PostgreSQL provides a means for creating locks that have application-defined meanings. These are known as advisory locks. The system does not enforce advisory locks, it is up to the application to use them correctly.

There are two ways to acquire advisory locks in PostgreSQL, at the session level or at the transaction level. Session level advisory locks are held until they are explicitly released or until the session ends. Transaction-level advisory lock requests behave more like regular lock requests, they are automatically released at the end of the transaction. Unlike session-level advisory locks, there is no explicit unlock operation for transaction-level advisory locks.

Advisory locks can be either shared or exclusive. Shared advisory locks do not block other shared advisory locks but they will block exclusive advisory locks. Exclusive advisory locks will block shared advisory locks and exclusive advisory locks.

An advisory lock resource identifier is either by a single 64-bit key value or two 32-bit key values. If another session already holds a conflicting lock on the same resource identifier, a new lock request will either wait until the resource identifier becomes available, or return a false result if appropriate.

Advisory locks are scoped to the current database. Taking an advisory lock in one database does NOT block taking the same advisory lock in another database.

Session-level and transaction-level requests for the same resource identifier can block each other. If a session already holds an advisory lock, additional lock requests for the same resource identifier by it will always succeed, even if other sessions are waiting on the same resource identifier. This is true regardless of whether the locks are at the session level or the transaction level. Multiple session-level lock requests will stack. If the same resource identifier is locked three times then there must be three unlock requests if you want to release the resource identifier prior to the session ending.

A complete list of advisory locks currently held by any session can be found in the pg_locks system view.

SELECT * FROM pg_locks WHERE locktype = 'advisory'

PostgreSQL provides several system functions for managing advisory locks. The following is from the PostgreSQL online documentation:

FunctionReturnsDescription
pg_advisory_lock()voidObtains an exclusive session-level advisory lock, waiting if necessary.
pg_advisory_lock_shared()voidObtains a shared session-level advisory lock, waiting if necessary.
pg_advisory_unlock()booleanReleases a previously-acquired exclusive session-level advisory lock. Returns true if the lock is successfully released. If the lock was not held, false is returned.
pg_advisory_unlock_all()voidReleases all session-level advisory locks held by the current session.
pg_advisory_unlock_shared()booleanReleases a previously-acquired shared session-level advisory lock. Returns true if the lock is successfully released. If the lock was not held, false is returned.
pg_advisory_xact_lock()voidObtains an exclusive transaction-level advisory lock, waiting if necessary.
pg_advisory_xact_lock_shared()voidObtains a shared transaction-level advisory lock, waiting if necessary.
pg_try_advisory_lock()booleanObtains an exclusive session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
pg_try_advisory_lock_shared()booleanObtains a shared session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
pg_try_advisory_xact_lock()booleanObtains an exclusive transaction-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
pg_try_advisory_xact_lock_shared()booleanObtains a shared transaction-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
Advisory Lock Functions

Use Cases
Advisory locks can be useful for controlling processes to prevent them from running while conflicting processes are running. The following examples use advisory locks to prevent orders from being priced while prices are being updated.

-- Price Updates
BEGIN TRANSACTION;
SELECT pg_advisory_xact_lock(1242);

UPDATE prices
SET unit_price = unit_price * 1.10
WHERE category = 'hard goods';

UPDATE prices
SET unit_price = unit_price * 1.25
WHERE category = 'consumables';

UPDATE prices
SET unit_price = unit_price * .90
WHERE category = 'discontinued';

COMMIT TRANSACTION;

-- Order Pricing
BEGIN TRANSACTION;
SELECT pg_advisory_xact_lock_shared(1242);

UPDATE order_details od
SET line_total = line_quantity * pr.unit_price - discount
FROM prices pr
WHERE pr.item_code = od.item_code 
AND od.order_id = order_id_parameter;

UPDATE order_header oh
SET order_total = SUM(od.line_total)
FROM order_details od
WHERE od.order_id = oh.order_id
AND oh.order_id = order_id_parameter;

COMMIT TRANSACTION;

In this scenario the price updates script will cause any executions of the order pricing script to wait. Conversely, if there are any active order pricings running, the price update script will wait. Since the order pricing script uses shared advisory locks, multiple order pricings can run at the same time.

I’ll admit that this is not a very good example. Regular locking should prevent conflicting updates. Perhaps a better use case would be a function that is called from many places and uses a lot of resources. You could use advisory locks to prevent multiple executions of that function at the same time.

The only limitation to using advisory locks is your creativity.

One last note. I have found that that if my database session times out while I have an active advisory session lock, the lock will stay active. Since only the session that created the lock can release it, you will have to terminate the session to release the lock. The following SQL can help with that.

SELECT pg_terminate_backend(pid) -- Get pid from pg_locks