Advisory locks in PostgreSQL - Step-by-Step Execution

Concept Flow - Advisory locks

Start Transaction

↓

Request Advisory Lock

↓

Check Lock Availability

↓

Lock Acquired

↓

Perform Work

↓

Release Lock

↓

End Transaction

The flow shows starting a transaction, requesting an advisory lock, waiting if needed, doing work, then releasing the lock and ending the transaction.

Execution Sample

PostgreSQL

BEGIN;
SELECT pg_advisory_lock(12345);
-- critical section
SELECT pg_advisory_unlock(12345);
COMMIT;

This code acquires an advisory lock with key 12345, does some work, then releases the lock.

Execution Table

Step	Action	Function Call	Result	Lock State
1	Start transaction	BEGIN;	Transaction started	No lock held
2	Request lock	SELECT pg_advisory_lock(12345);	Lock acquired	Lock 12345 held by session
3	Perform work	-- critical section	Work done	Lock 12345 held by session
4	Release lock	SELECT pg_advisory_unlock(12345);	Lock released	No lock held
5	End transaction	COMMIT;	Transaction committed	No lock held

💡 Transaction ends and lock is released if not already unlocked.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Lock 12345	Not held	Held	Held	Released	Released
Transaction	Not started	Started	Started	Started	Committed

Key Moments - 3 Insights

Why does the session wait at pg_advisory_lock if another session holds the lock?

Because pg_advisory_lock blocks until the lock is free, as shown in step 2 where the lock is acquired only when available.

What happens if you forget to call pg_advisory_unlock?

The lock stays held until the transaction ends, so it is automatically released at COMMIT or ROLLBACK, as shown in step 5.

Can advisory locks be used outside transactions?

Yes, advisory locks are session-level and can be used outside explicit transactions; however, locks acquired outside transactions are held until the session ends or the lock is explicitly released.

Visual Quiz - 3 Questions

Look at the execution table, what is the lock state after step 3?

A No lock held

B Lock 12345 released

C Lock 12345 held by session

D Lock 12345 waiting

💡 Hint

Check the 'Lock State' column in row for step 3 in the execution table.

At which step is the lock released according to the execution table?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Action' and 'Result' columns for when the lock is released.

If pg_advisory_lock is called while the lock is held by another session, what happens?

A The call blocks until the lock is free

B The call returns immediately with success

C The call fails with an error

D The lock is forcibly taken

💡 Hint

Refer to the concept flow where 'Check Lock Availability' leads to waiting if lock is not free.

Concept Snapshot

Advisory locks in PostgreSQL allow sessions to lock resources by key.
Use pg_advisory_lock(key) to acquire a lock; it blocks if taken.
Use pg_advisory_unlock(key) to release the lock.
Locks are session-based and released automatically at transaction end.
Useful for application-level synchronization without locking tables.

Full Transcript

This visual execution shows how advisory locks work in PostgreSQL. First, a transaction starts. Then the session requests an advisory lock with a key. If the lock is free, it is acquired; otherwise, the session waits. While holding the lock, the session performs work. After finishing, it releases the lock explicitly or it is released automatically when the transaction commits or rolls back. The lock state changes from not held to held and back to released. Advisory locks help coordinate access to resources without blocking database rows or tables directly.