Advisory Locks: Application-Level Mutexes

Turn leader election into three lines of SQL, with no external coordination service at all

An advisory lock is a mutex PostgreSQL manages for you, identified by nothing more than a number you pick — it has no relationship to any table, row, or object. That is exactly what makes it useful for application-level coordination that has nothing to do with data: two instances of a job, a leader election, a "only one migration script at a time" guard. Whoever acquires the number first wins; everyone else can either wait or, more usefully, ask non-blockingly and immediately learn they lost.\n\nThis lab makes the mutex genuinely observable: one session holds an advisory lock and is kept alive with a wrapping sleep the exact way earlier labs kept a row lock or a table lock alive, a second session races for the same number and loses immediately rather than waiting, and pg_locks shows the held lock directly while all of this is happening.

Non-Blocking Acquisition

SELECT pg_try_advisory_lock(424242);  -- true if acquired, false if already held elsewhere — never waits

Compare with the blocking form:

SELECT pg_advisory_lock(424242);  -- waits until it can acquire the lock, like any other PostgreSQL lock wait

Two Scopes, Two Different Release Rules

| Function | Released by | Use it when | |----------|-------------|-------------| | pg_try_advisory_lock() / pg_advisory_lock() | An explicit pg_advisory_unlock() call, or the session disconnecting | The coordination needs to outlive a single transaction (a long-running job, a leader election) | | pg_advisory_xact_lock() | Automatically, the instant the current transaction commits or rolls back | The coordination should never accidentally outlive one transaction — no unlock call to forget |

Seeing the Lock, Live

SELECT locktype, mode, granted, objid FROM pg_locks WHERE locktype = 'advisory';

This is the exact same pg_locks view used for every other lock type in this course — an advisory lock is a first-class PostgreSQL lock, just one your own application chose to take rather than one the database took on your behalf.

A Cron-Safe Wrapper

CREATE OR REPLACE FUNCTION run_nightly_etl() RETURNS text LANGUAGE plpgsql AS $
BEGIN
  IF NOT pg_try_advisory_lock(555) THEN
    RETURN 'already running, skipped';
  END IF;
  -- ... the real job body goes here ...
  PERFORM pg_advisory_unlock(555);
  RETURN 'completed';
END;
$;

Two overlapping cron triggers of this same job — one running long, one starting on schedule regardless — resolve themselves with zero coordination infrastructure: the second one simply sees the lock already held and exits immediately.

Advisory Lock

A mutex identified purely by an application-chosen number (or two 32-bit numbers), with no connection to any table, row, or database object whatsoever. PostgreSQL enforces mutual exclusion on that number exactly like any other lock, but the meaning of the number — what it is coordinating — is entirely up to the application, not the database.

Connection-Scoped vs Transaction-Scoped

pg_advisory_lock() / pg_try_advisory_lock() locks live for the connection's lifetime and need an explicit pg_advisory_unlock() (or disconnection) to release — appropriate for coordination spanning multiple transactions. pg_advisory_xact_lock() locks are tied to the current transaction and release automatically and unconditionally at COMMIT or ROLLBACK, which is safer whenever the coordination should never be allowed to outlive one transaction by a forgotten unlock call.

🔐 Win the Race

Acquire an advisory lock and hold it for a few seconds by wrapping it in a sleep, the same technique used to hold a real row or table lock in earlier labs.

psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);" -c "SELECT pg_sleep(6);" > /tmp/adv1.log 2>&1 &

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);" -c "SELECT pg_sleep(6);" > /tmp/adv1.log 2>&1 & [1] 143

❌ Lose the Race, Instantly

From a second session, try the exact same lock number while the first session is still holding it.

psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);" SET pg_try_advisory_lock ----------------------- f (1 row)

🔍 See It in pg_locks

While the lock is still held, confirm it is visible in pg_locks like any other PostgreSQL lock.

psql -U postgres -d beer_db -c "SELECT locktype, mode, granted, objid FROM pg_locks WHERE locktype = 'advisory';"

🔓 Confirm It Releases and Can Be Re-Won

Wait for the holding session to finish (and disconnect, releasing its connection-scoped lock), then confirm the lock is free again.

sleep 6

psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);"

student@lab:~$ sleep 6 student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_try_advisory_lock(424242);" SET pg_try_advisory_lock ----------------------- t (1 row)

⏳ Compare: Transaction-Scoped Locks Release Automatically

Take a transaction-scoped lock, confirm it is held mid-transaction, and watch it disappear automatically at COMMIT — no unlock call at all.

psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT pg_advisory_xact_lock(2);" -c "SELECT locktype, mode FROM pg_locks WHERE locktype='advisory';" -c "COMMIT;" -c "SELECT locktype FROM pg_locks WHERE locktype='advisory';"

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT pg_advisory_xact_lock(2);" -c "SELECT locktype, mode FROM pg_locks WHERE locktype='advisory';" -c "COMMIT;" -c "SELECT locktype FROM pg_locks WHERE locktype='advisory';" SET BEGIN pg_advisory_xact_lock ------------------------ (1 row) locktype | mode ----------+--------------- advisory | ExclusiveLock (1 row) COMMIT locktype ---------- (0 rows)

🛡️ Write the Cron-Safe Wrapper

Write a function that uses a try-lock to guarantee only one copy of a job ever runs at a time, and prove it against itself.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION run_nightly_etl() RETURNS text LANGUAGE plpgsql AS \$\$ BEGIN IF NOT pg_try_advisory_lock(555) THEN RETURN 'already running, skipped'; END IF; PERFORM pg_sleep(1); PERFORM pg_advisory_unlock(555); RETURN 'completed'; END; \$\$;"

psql -U postgres -d beer_db -c "SELECT run_nightly_etl();"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION run_nightly_etl() RETURNS text LANGUAGE plpgsql AS \$\$ BEGIN IF NOT pg_try_advisory_lock(555) THEN RETURN 'already running, skipped'; END IF; PERFORM pg_sleep(1); PERFORM pg_advisory_unlock(555); RETURN 'completed'; END; \$\$;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "SELECT run_nightly_etl();" SET run_nightly_etl ------------------ completed (1 row)

Lab 2.7.2 complete. Leader election and mutual exclusion, without any external coordination service:\n\n\n pg_try_advisory_lock : ✅ one winner, one instant loser, no waiting\n Visible in pg_locks : ✅ same view as every other lock type\n Connection-scoped release : ✅ automatic on disconnect, explicit with unlock\n Transaction-scoped release : ✅ automatic and unconditional at COMMIT\n Cron-safe wrapper function : ✅ working leader election in four lines\n

Enable JavaScript to run the live terminal and track your progress.