Advisory locks: the basic pattern

PostgreSQL has an advanced lock system for coordinating between DB operations — row locks (SELECT FOR UPDATE), table locks (LOCK TABLE), schema locks. But sometimes you need a lock that represents nothing in the DB — it just "marks" a conceptual resource as busy. For those cases, PostgreSQL offers advisory locks.

An advisory lock is a lock with an arbitrary key (an integer or a pair of integers) that you decide. PostgreSQL only tracks who holds the lock — it doesn't apply to any specific table. You decide what the key represents. "Re-indexing job", "cleanup cron", "operation X on resource Y" — anything.

In this lesson you'll learn the two basic commands (pg_advisory_lock and pg_try_advisory_lock), the two key forms, and a first real case: protecting a cron against concurrent runs.

The two basic commands

pg_advisory_lock(key) — blocks until acquired

SELECT pg_advisory_lock(12345);
-- If no one else holds lock 12345: it acquires it immediately.
-- If someone else holds it: it BLOCKS, waiting until they release it.

pg_advisory_lock waits. If the lock is taken, your connection waits until the holder releases it. Useful when you always want to run the operation — you just need to wait your turn.

pg_try_advisory_lock(key) — tries without blocking

SELECT pg_try_advisory_lock(12345);
-- Returns TRUE if it acquired the lock.
-- Returns FALSE if someone else holds it (doesn't wait).

pg_try_advisory_lock doesn't wait. If the lock is taken, it returns false immediately. Useful when you only want to run if no one else is doing it — if someone else is, you back out without doing anything.

Releasing: pg_advisory_unlock(key)

SELECT pg_advisory_unlock(12345);
-- Returns TRUE if it released something, FALSE if it didn't hold the lock.

When to use each

pg_advisory_lock (waits) — typical cases:

• Migrations that run in order — one at a time, wait your turn.
• Operations that are NOT safe to skip — a critical cache refresh that must ALWAYS run.
• Sequential processes where the order matters.

pg_try_advisory_lock (doesn't wait) — typical cases:

• Periodic crons: if the previous run takes longer than expected, the next run backs out without doing anything instead of duplicating work.
• Distributed locks for "single instance": only one does the operation, the rest are discarded.
• Best-effort processing: if it's busy, try again later.

In Python backends, pg_try_advisory_lock is the more common one. The "skip if already running" logic is typical for crons.

The two key forms

Form 1: a single integer (BIGINT)

SELECT pg_try_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

Range: BIGINT (8 bytes), -9223372036854775808 to 9223372036854775807.

Useful when your key fits in a single integer.

Form 2: two integers (INT, INT)

SELECT pg_try_advisory_lock(12345, 67890);
SELECT pg_advisory_unlock(12345, 67890);

Each one is an INT (4 bytes). Useful for namespacing: the first int can be "lock category" and the second "specific ID".

-- Convention: type 1 lock (job runner) on resource 67890
SELECT pg_try_advisory_lock(1, 67890);

-- Convention: type 2 lock (cron refresh) on resource 100
SELECT pg_try_advisory_lock(2, 100);

The two forms are independent in terms of namespace. pg_advisory_lock(12345) (one int) and pg_advisory_lock(0, 12345) (two ints) are different locks.

Team key conventions

Since the key is an arbitrary integer, the team needs to agree on what each key means. Without a convention, two teams could use the same key for different things and block each other.

Approach 1: hash of a string

import zlib

def lock_key(name: str) -> int:
    """Convert a string into a stable BIGINT key."""
    return zlib.crc32(name.encode()) & 0x7FFFFFFFFFFFFFFF


# Usage
key = lock_key("cron:reindex_posts")
# Always the same number for the same string

Advantage: a human-readable name in code, an automatic key. Caution: collisions are theoretically possible (rare with descriptive strings).

Approach 2: namespacing with two ints

class LockType:
    CRON = 1
    JOB_QUEUE = 2
    REFRESH_MV = 3
    BATCH_IMPORT = 4

# Usage
SELECT pg_try_advisory_lock(LockType.CRON, hash("reindex_posts") & 0x7FFFFFFF)

Advantage: explicit namespacing, easy to debug (see locks by category). Caution: a bit more verbose.

Approach 3: enum + sequential ID

LOCK_REGISTRY = {
    "cron:reindex_posts": 1001,
    "cron:cleanup_old_logs": 1002,
    "job:daily_report": 1003,
    "mv:refresh_top_posts": 1004,
}

key = LOCK_REGISTRY["cron:reindex_posts"]

Advantage: a centralized registry, no collisions. Caution: requires keeping the registry up to date.

Recommendation: approach 2 (namespacing with two ints) for new code. Approach 1 (hash) if you want simplicity. Approach 3 if you have dozens of locks and want strict control.

Real-world case: a cron protected against concurrent runs

Your cron runs every 5 minutes. Sometimes it takes more than 5 minutes. Without protection, two concurrent crons do the same work.

Without an advisory lock (the problem)

# cron_reindex.py
async def reindex_posts():
    """Reindex tsvector on modified posts."""
    posts = await fetch_posts_to_reindex()
    for post in posts:
        await recalculate_tsvector(post)


# Crontab: */5 * * * *
asyncio.run(reindex_posts())

If two crons run simultaneously, both do the same work, row locks, intermittent errors.

With pg_try_advisory_lock (the solution)

# cron_reindex.py
import asyncpg


async def reindex_posts():
    """Reindex tsvector on modified posts.

    If another reindex is running, exit immediately without doing anything.
    """
    conn = await asyncpg.connect("postgresql://...")

    try:
        # Try to acquire the lock — an arbitrary key that represents "this cron"
        REINDEX_LOCK_KEY = 1001
        got_lock = await conn.fetchval(
            "SELECT pg_try_advisory_lock($1)",
            REINDEX_LOCK_KEY
        )

        if not got_lock:
            print("Another reindex is running — exiting.")
            return

        # We have the lock — do the work
        try:
            posts = await fetch_posts_to_reindex(conn)
            for post in posts:
                await recalculate_tsvector(conn, post)
        finally:
            # Release the lock
            await conn.fetchval(
                "SELECT pg_advisory_unlock($1)",
                REINDEX_LOCK_KEY
            )
    finally:
        await conn.close()


asyncio.run(reindex_posts())

Now if two crons run simultaneously:

• First: pg_try_advisory_lock → TRUE, does the work.
• Second: pg_try_advisory_lock → FALSE, exits immediately.

No Redis, no extra service, just PostgreSQL.

Inspecting locks in use

pg_locks is the view that shows all active locks:

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

Typical output:

 pid  | locktype  | key  | granted | mode
------+-----------+------+---------+----------
 1234 | advisory  | 1001 | t       | ExclusiveLock

Useful for debugging:

• Are there held locks? If an operation seems hung, check.
• Which process holds it? pid leads you to the backend.
• Is the lock granted or waiting? granted true/false.

To join with process info:

SELECT
    pl.pid,
    pl.objid AS key,
    psa.application_name,
    psa.query,
    psa.state,
    psa.query_start
FROM pg_locks pl
JOIN pg_stat_activity psa ON pl.pid = psa.pid
WHERE pl.locktype = 'advisory';

Traps and common mistakes

1. Forgetting the unlock — a lock leak.

# ❌ If the code between lock and unlock crashes, the lock stays held
got_lock = await conn.fetchval("SELECT pg_try_advisory_lock(1)")
if got_lock:
    do_work()  # If this crashes, the unlock doesn't run
    await conn.fetchval("SELECT pg_advisory_unlock(1)")

# ✅ Use try/finally
got_lock = await conn.fetchval("SELECT pg_try_advisory_lock(1)")
if got_lock:
    try:
        do_work()
    finally:
        await conn.fetchval("SELECT pg_advisory_unlock(1)")

Lesson 04 covers the pattern with a Pythonic context manager.

2. A lock that survives the connection close.

A session-level advisory lock is released automatically when the connection closes. But if your app has a connection pool, the connection gets reused — the lock isn't released when you "return" the connection to the pool. This matters with session-level vs transaction-level (lesson 03).

3. Confusing an advisory lock with a table lock.

An advisory lock does NOT affect queries on tables. It's a conceptual marker. Another connection can read/write freely — the lock doesn't protect rows, it only coordinates.

4. A race condition between the check and the operation.

# ❌ Race: someone else can start between the check and our work
exists = await check_if_running()
if not exists:
    await do_work()  # Another process could have started at the same time

An advisory lock is atomic: the check AND the acquisition happen in a single operation.

5. Using an advisory lock for something that should be a row.

If the lock represents "this row is being processed", use SELECT ... FOR UPDATE (a real row-level lock). An advisory lock is for abstract concepts (jobs, crons), not for specific rows.

6. Keys hardcoded across multiple files.

# ❌ Scattered, prone to collision
# in cron_a.py:
got_lock = await conn.fetchval("SELECT pg_try_advisory_lock(1001)")

# in cron_b.py:
got_lock = await conn.fetchval("SELECT pg_try_advisory_lock(1001)")  # Collision!

Use a centralized registry or the namespace + string-hash approach.

7. An advisory lock in a function called as a subquery.

Some ORM features can wrap the query in subqueries that affect when it's evaluated. Keep SELECT pg_try_advisory_lock(...) as a simple, non-embedded query.

Exercise: implement a lock for a cron

Setup: two terminals, the same PostgreSQL.

Step 1: terminal 1 — acquire the lock.

-- Terminal 1
SELECT pg_advisory_lock(99999);
-- Lock acquired immediately

Step 2: terminal 2 — try without waiting.

-- Terminal 2
SELECT pg_try_advisory_lock(99999);
-- Returns FALSE (another one holds it)

Step 3: terminal 2 — try with waiting.

-- Terminal 2
SELECT pg_advisory_lock(99999);
-- BLOCKS, waiting — the query hangs

Step 4: terminal 1 — release.

-- Terminal 1
SELECT pg_advisory_unlock(99999);
-- Returns TRUE

Immediately terminal 2 acquires the lock and the query returns.

Step 5: terminal 2 — release too.

-- Terminal 2
SELECT pg_advisory_unlock(99999);

Step 6: verify by inspecting pg_locks while someone holds the lock.

-- In any terminal
SELECT pid, objid, granted, mode FROM pg_locks WHERE locktype = 'advisory';

Step 7: implement a Python wrapper with try/finally.

import asyncpg
import asyncio


async def with_lock(key: int, work_func):
    """Acquire lock, run work_func, release."""
    conn = await asyncpg.connect("postgresql://...")
    try:
        got = await conn.fetchval("SELECT pg_try_advisory_lock($1)", key)
        if not got:
            print(f"Lock {key} not available, skipping")
            return False
        try:
            await work_func(conn)
            return True
        finally:
            await conn.fetchval("SELECT pg_advisory_unlock($1)", key)
    finally:
        await conn.close()


async def my_work(conn):
    print("Doing work...")
    await asyncio.sleep(2)
    print("Work done")


# Test concurrency
async def main():
    results = await asyncio.gather(
        with_lock(1001, my_work),
        with_lock(1001, my_work),
        with_lock(1001, my_work),
    )
    print(f"Results: {results}")
    # Expected: only the first does the work, the rest return False


asyncio.run(main())

 pid  | objid | granted | mode
 1234 | 99999 | t       | ExclusiveLock

Doing work...
Lock 1001 not available, skipping
Lock 1001 not available, skipping
Work done
Results: [True, False, False]

Summary and next step

What you learned:

• pg_advisory_lock(key): blocks, waiting until acquired.
• pg_try_advisory_lock(key): tries without waiting. Returns TRUE/FALSE.
• pg_advisory_unlock(key): releases the lock.
• Two key forms: a single BIGINT, or (int, int) with namespacing.
• Team conventions: string hash, namespacing with two ints, a centralized registry.
• Real-world case: a cron protected against concurrent runs with pg_try_advisory_lock.
• pg_locks: inspect active locks for debugging.
• Traps: forgetting the unlock, race conditions, scattered hardcoded keys.

Before moving on, you should be able to:

• Explain the difference between pg_advisory_lock and pg_try_advisory_lock.
• Implement a cron protected with an advisory lock.
• Inspect active locks with pg_locks.
• Decide on a key convention for your team.

In the next lesson we get to the central advisory-lock decision: session-level vs transaction-level. It's the most important decision and the one that gets messed up the most. You'll learn when to use each, how each level is released automatically, and the gotchas with connection pools.

Resources

1. PostgreSQL Docs — Advisory Locks — official reference.
2. PostgreSQL Docs — pg_locks — the locks view.
3. Citus Data — Advisory Locks — deep dive.
4. Crunchy Data — Advisory Locks tutorial — real-world case.
5. Sidekiq Job Uniqueness — Ruby pattern.
6. zlib.crc32 — Python docs — for hashing strings.

Lesson 02 of 08 — Module 6 — Advanced PostgreSQL for Backend Guide