Advisory Lock

Introduction

Unlike Level Locks created by the database at the physical layer, developers can freely create Advisory Locks based on custom logic at the application level
The database does not know the meaning of this lock and it only acts as an intermediary to hold the lock (usually a bigint number)

The process that arrives first is granted the lock for processing
Meanwhile, subsequent processes must line up and wait to acquire the lock
When a process finishes processing, it returns the lock to the next process

Advantages

When using other types of locks (such as Table level lock, Row level lock or Page level lock), you must rely on an actual existing data row in the table to lock, whereas Advisory Lock does not require any available data row, you can use any random number to lock
It is extremely lightweight because it only exists on the Database RAM, does not write to the hard drive and does not generate redundant data (dead tuples)

Functions

To create an advisory lock, Postgres provides the following supporting functions

pg_advisory_lock(bigint): lock by session
pg_advisory_unlock(bigint): used to unlock, otherwise other processes will be blocked forever and never execute
pg_advisory_xact_lock(bigint): used within a Function/Procedure, it automatically creates a lock and unlocks upon completion
pg_advisory_xact_lock(int, int): uses 2 parameters where parameter 1 is the namespace (a unique number representing each table) and parameter 2 is based on the row id to distinguish which row is being processed
pg_try_advisory_xact_lock(): non-blocking function, returns false if a lock exists, allowing an error to be thrown immediately without waiting

Detail

First, create tables with data as follows

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    stock INT NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, stock) VALUES ('Product 1', 1);

We will look at a very common example when placing an order, if a product in stock only has 1 left but two people place an order at the same time, what will the result be
In terms of processing logic, we must implement it such that when that case occurs, only one person successfully places the order, the order quantity must decrease to 0 and one person must receive a failure notification due to out of stock

I will create a Function combined with pg_advisory_xact_lock to perform the ordering process as follows

CREATE OR REPLACE FUNCTION place_order(p_product_id INT, p_quantity INT)
RETURNS TEXT AS $$
DECLARE
    v_current_stock INT;
BEGIN
    PERFORM pg_advisory_xact_lock(9999, p_product_id);
    
    SELECT stock INTO v_current_stock FROM products WHERE id = p_product_id;
    
    IF v_current_stock < p_quantity THEN
        RAISE EXCEPTION 'Out of stock! Current stock: %, Requested quantity: %', v_current_stock, p_quantity;
    END IF;
    
    UPDATE products 
    SET stock = stock - p_quantity 
    WHERE id = p_product_id;
    
    INSERT INTO orders (product_id, quantity) 
    VALUES (p_product_id, p_quantity);
    
    RETURN 'Order placed successfully!';
EXCEPTION
    WHEN OTHERS THEN
        RETURN SQLERRM;
END;
$$ LANGUAGE plpgsql;

You can see that I use the pg_advisory_xact_lock function to create a lock
- No matter how many people place an order, whenever they encounter this lock, only one person is allowed to place the order
- During the processing for that person, others will be placed in a queue to wait for the lock
- After the Function finishes, it releases the lock for the next person to execute the order
- Therefore, even if there is only 1 product and 2 people order at the same time, overselling will not occur
Next is getting the stock to check, if it is out of stock, report an error
If stock is still available, subtract it by the ordered quantity and create the corresponding order
Note
- We should limit the use of pg_advisory_lock and pg_advisory_unlock because they require manual lock creation and release, if the logic you implement hits an error mid-way and cannot run the release lock function, that session will be locked and no one can use this feature anymore
- You should also be careful when using pg_advisory_xact_lock(bigint) passing only 1 input parameter, because this is a unique number applied to the whole session, if you use id, different tables will have overlapping id values leading to locking the wrong feature
- Instead, I use pg_advisory_xact_lock(int, int) with the first value as a namespace like a unique number identifying this feature and the next value as the id of the row you need to lock, which will reduce the risk of locking the wrong feature

Next, check the results

-- Transaction 1 and 2
BEGIN;
SELECT place_order(1, 1);

COMMIT;
ROLLBACK;

Please create 2 Transactions and execute the Function call together, if you have not committed or rolled back in Transaction 1, Transaction 2 will be locked to wait
If you commit Transaction 1, meaning all stock has been ordered, Transaction 2 will report an out of stock error (Out of stock! Current stock: %, Requested quantity: %)
If you rollback Transaction 1, meaning the order was not successful and stock remains, Transaction 2 will successfully place the order (Order placed successfully!)