Session-level vs transaction-level: the central decision

Advisory locks come in two flavors: session-level and transaction-level. The difference seems small but matters a lot — choosing wrong causes locks held indefinitely or operations that release locks too early.

Session-level: the lock lasts until you release it explicitly or until the connection closes. It survives across transactions.

Transaction-level: the lock is released automatically on COMMIT/ROLLBACK of the transaction. It only lasts one transaction.

In this lesson you'll learn when to use each, the gotchas with connection pools (critical for any modern app), and the specific functions for each case.

The functions for each level

Session-level

-- Acquire
SELECT pg_advisory_lock(key);          -- Waits
SELECT pg_try_advisory_lock(key);      -- Doesn't wait

-- Release
SELECT pg_advisory_unlock(key);
SELECT pg_advisory_unlock_all();       -- Releases all locks in this session

Transaction-level

-- Acquire (released automatically on COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(key);          -- Waits
SELECT pg_try_advisory_xact_lock(key);      -- Doesn't wait

-- There's NO manual unlock function — always automatic on commit/rollback

Notice that transaction-level has no manual unlock function. That's a feature, not a bug: it guarantees the lock is released even if your code has bugs.

When session-level

Typical cases:

Case 1: a long-running job runner

A worker process is active for minutes or hours, processing jobs. The lock represents "I am the active worker for this job type".

async def worker():
    conn = await asyncpg.connect(...)

    # Acquire the lock at the start of the worker
    got_lock = await conn.fetchval(
        "SELECT pg_try_advisory_lock(:k)", {"k": JOB_QUEUE_LOCK}
    )
    if not got_lock:
        return  # Another worker is already active

    try:
        # Process jobs for minutes
        while True:
            job = await fetch_next_job(conn)
            if not job:
                break
            await process_job(conn, job)  # Each job is an independent transaction
    finally:
        await conn.fetchval(
            "SELECT pg_advisory_unlock(:k)", {"k": JOB_QUEUE_LOCK}
        )

The lock lasts the whole time the worker processes jobs. Each process_job can be its own transaction (commit/rollback) without affecting the global lock. Session-level because the lock's scope is much longer than any individual transaction.

Case 2: a periodic cron

A cron starts, runs for minutes, finishes. The lock prevents another cron from starting while this one runs.

async def cron_reindex():
    conn = await asyncpg.connect(...)

    got_lock = await conn.fetchval(
        "SELECT pg_try_advisory_lock(:k)", {"k": REINDEX_LOCK}
    )
    if not got_lock:
        print("Already running, exit")
        return

    try:
        # Work for minutes, possibly across multiple transactions
        await reindex_batch_1(conn)  # Tx 1
        await reindex_batch_2(conn)  # Tx 2
        await reindex_batch_3(conn)  # Tx 3
    finally:
        await conn.fetchval("SELECT pg_advisory_unlock(:k)", {"k": REINDEX_LOCK})

Same pattern — the lock lasts the whole cron, transactions come and go inside.

Case 3: a distributed singleton

Only one instance should run logic X. It acquires the lock on startup and holds it for its entire lifetime.

async def singleton_app():
    conn = await asyncpg.connect(...)

    got_lock = await conn.fetchval("SELECT pg_try_advisory_lock(SINGLETON_KEY)")
    if not got_lock:
        sys.exit("Another instance is running")

    # App runs indefinitely with the lock
    await run_app_forever()
    # (lock is released when the app closes)

When transaction-level

Typical cases:

Case 1: a protected atomic operation

Your operation is ONE transaction. The lock represents "only one does this operation at a time".

async def atomic_operation(conn):
    async with conn.transaction():
        # Acquire the lock — released at the end of the transaction
        got = await conn.fetchval(
            "SELECT pg_try_advisory_xact_lock(:k)", {"k": OPERATION_KEY}
        )
        if not got:
            return  # Someone else is doing the operation

        # Do the operation
        await do_critical_work(conn)
        # The automatic COMMIT here releases the lock

No need for a manual unlock. The transaction ends, the lock releases.

Case 2: a materialized view refresh

async def refresh_mv(conn):
    async with conn.transaction():
        got = await conn.fetchval("SELECT pg_try_advisory_xact_lock(:k)", {"k": MV_KEY})
        if not got:
            return

        await conn.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY top_posts")
        # The commit releases the lock automatically

Case 3: idempotency in an endpoint

@router.post("/sensitive-action")
async def sensitive_action(idempotency_key: str, db: AsyncSession = ...):
    async with db.begin():
        # Lock by idempotency key
        key_int = hash(idempotency_key) & 0x7FFFFFFF
        got = await db.scalar(
            text("SELECT pg_try_advisory_xact_lock(:k)"),
            {"k": key_int}
        )
        if not got:
            raise HTTPException(429, "Already processing")

        # Check if it already ran
        if await already_executed(db, idempotency_key):
            return cached_response

        # Execute
        result = await execute_action(db)
        await save_idempotency_record(db, idempotency_key, result)
        return result
        # The commit releases the lock

While a request with that idempotency_key is being processed, other requests with the same key get an immediate 429.

How to decide

Guiding question:

Does the lock's scope correspond exactly to one transaction?

• Yes → transaction-level. Simpler, automatic, robust.
• No → session-level. You need to handle the release explicitly.

Another framing:

Do you need the lock to survive across transactions?

• Yes → session-level. A worker that processes N jobs is N transactions, one global lock.
• No → transaction-level.

Default recommendation: transaction-level when you can, session-level when you need it. Transaction-level is more robust (you can't forget the unlock), simpler, less prone to leaks.

The gotcha with connection pools

This is critical for any modern app using SQLAlchemy/asyncpg with a pool:

The problem

Connection pools reuse connections. A connection your request used goes back to the pool and the next request receives it.

Session-level advisory locks are NOT released when you "return" the connection to the pool — only when the connection actually closes.

# Request 1
async with SessionLocal() as session:  # takes a conn from the pool
    await session.execute(text("SELECT pg_advisory_lock(:k)"), {"k": 1})
    # ... work ...
    # We forgot to unlock
# session.close() — conn returns to the pool, does NOT close
# The lock is held by this conn

# Request 2
async with SessionLocal() as session:  # takes the same conn from the pool
    # This session has the same underlying conn
    # The previous lock is still there, held by "this" connection
    got = await session.scalar(text("SELECT pg_try_advisory_lock(:k)"), {"k": 1})
    # got == TRUE because "this same connection" already holds the lock
    # Confusion...

PostgreSQL says "this connection already holds the lock" because at the PG level, it's still the same connection (even though at your app level, they're different requests).

Solutions

1. Use transaction-level when you can.

pg_advisory_xact_lock is released on commit/rollback. It doesn't have this problem.

2. If you need session-level, always unlock explicitly.

async with SessionLocal() as session:
    try:
        await session.execute(text("SELECT pg_advisory_lock(:k)"), {"k": 1})
        # ... work ...
    finally:
        await session.execute(text("SELECT pg_advisory_unlock(:k)"), {"k": 1})

try/finally is MANDATORY. Forgetting the unlock = a lock leak. Lesson 04 shows the context manager.

3. Use a dedicated connection (not from the pool).

For cases where the lock lasts a long time (worker, cron), open a fresh conn:

# Not from the pool — direct with asyncpg
conn = await asyncpg.connect("postgresql://...")
try:
    await conn.fetchval("SELECT pg_advisory_lock(:k)", {"k": 1})
    # ... lots of work ...
finally:
    await conn.fetchval("SELECT pg_advisory_unlock(:k)", {"k": 1})
    await conn.close()  # Closes the conn — all locks released

Closing the conn releases ALL pending session-level locks. The last line of defense.

4. pg_advisory_unlock_all() to clean everything up.

async with SessionLocal() as session:
    try:
        # ... work with several advisory locks ...
    finally:
        # Releases ALL advisory locks in this session
        await session.execute(text("SELECT pg_advisory_unlock_all()"))

Useful as a defensive cleanup.

Behavior with PgBouncer

PgBouncer in transaction mode reuses connections between transactions. This makes the problem worse:

Request 1 — Transaction 1:
  - PgBouncer assigns conn #5
  - SET LOCAL ...
  - Tx 1 commit
  - PgBouncer releases conn #5 to the pool

Request 2 — Transaction 2:
  - PgBouncer may assign the SAME conn #5
  - If Request 1 took a session-level lock, it's still there

Conclusion: with PgBouncer transaction mode, session-level advisory locks are unusable without care. Necessarily:

• Use transaction-level (xact_lock).
• If you need session-level, connect bypassing PgBouncer (port 5432 directly).

Checking locks by session

SELECT
    pid,
    objid,
    objsubid,
    granted,
    mode,
    -- If it's session-level, classid is 0; if it's transaction, also
    -- Telling them apart requires another check
    classid
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY pid;

To identify whether it's session vs transaction-level, there's no direct flag in pg_locks. You have to:

1. Know which function you used (a record in the code).
2. If nobody knows, assume the worst (a session-level one that got leaked).

That's why proactive monitoring helps. If you see locks that persist longer than expected, it's a sign of a leak.

Traps and common mistakes

1. Mixing session-level with PgBouncer transaction mode.

As we saw, this causes locks that unexpectedly persist between requests. Rule: with PgBouncer transaction mode, use ONLY transaction-level (xact_lock).

2. Forgetting pg_advisory_unlock on session-level.

A leaked lock. The conn holds it until it closes. In a conn pool, it could be hours/days.

3. Using transaction-level on an operation that lasts longer than the transaction.

# ❌
async with session.begin():
    got = await session.scalar(text("SELECT pg_try_advisory_xact_lock(:k)"), {"k": 1})
    # tx commit here
# Then trying the protected operation... but the lock was already released

If your operation lasts longer than one transaction, you need session-level.

4. Releasing a lock you didn't take.

-- Returns FALSE silently
SELECT pg_advisory_unlock(99999);

If you never took lock 99999, unlock returns false. No error, but confusing if you expected it to do something.

5. pg_advisory_unlock_all() in application code.

It releases ALL advisory locks in the session. If you had legitimate locks, you release those too. Only use it as a cleanup in a finally when closing.

6. Running pg_advisory_lock inside a transaction without realizing it.

If you run SELECT pg_advisory_lock(...) inside a transaction, the lock is session-level (it survives the commit). This can surprise you. If you want it to last only the transaction, use xact_lock.

7. pg_try_advisory_lock with two calls on the same key from the same session.

-- First time: TRUE (acquired)
SELECT pg_try_advisory_lock(1);

-- Second time from the SAME session: what happens?
SELECT pg_try_advisory_lock(1);
-- Returns TRUE — advisory locks are re-entrant!

Advisory locks are re-entrant: the same session can acquire the same lock multiple times. Each lock needs a corresponding unlock. This can be confusing if you don't know.

Decision matrix: when to use each level

Heuristic rule: if your lock accompanies ONE transaction, use transaction-level. If it accompanies multiple transactions, use session-level (with care about the connection pool).

Exercise: compare the behavior

Setup: local PostgreSQL, two terminals.

Step 1: terminal 1 — session-level inside a transaction.

-- Terminal 1
BEGIN;
SELECT pg_advisory_lock(1);
COMMIT;
-- Is the lock still held?

Step 2: terminal 2 — verify.

-- Terminal 2
SELECT pg_try_advisory_lock(1);
-- FALSE or TRUE?

Step 3: terminal 1 — disconnect and verify again.

-- Terminal 1
\q   -- disconnect

-- Terminal 2 (reconnect if needed)
SELECT pg_try_advisory_lock(1);
-- Now, FALSE or TRUE?

Step 4: Repeat with transaction-level.

-- Terminal 1 (reconnect)
BEGIN;
SELECT pg_advisory_xact_lock(1);
COMMIT;
-- Is the lock still held?

-- Terminal 2
SELECT pg_try_advisory_lock(1);
-- FALSE or TRUE?

Step 5: simulate the connection-pool problem.

Implement in Python:

import asyncio
import asyncpg


async def take_lock_session_level(pool):
    """Acquire a session-level lock (bad pattern)."""
    async with pool.acquire() as conn:
        await conn.fetchval("SELECT pg_advisory_lock(99999)")
        # No unlock — we simulate forgetting
    # conn returns to the pool


async def try_lock(pool):
    """Check whether the lock is available."""
    async with pool.acquire() as conn:
        got = await conn.fetchval("SELECT pg_try_advisory_lock(99999)")
        if got:
            await conn.fetchval("SELECT pg_advisory_unlock(99999)")
        return got


async def main():
    pool = await asyncpg.create_pool("postgresql://...", min_size=1, max_size=1)
    # max_size=1 guarantees the same conn

    # Step 1: take the lock and "forget" to release it
    await take_lock_session_level(pool)

    # Step 2: try_lock with the same conn — what happens?
    result = await try_lock(pool)
    print(f"Result: {result}")
    # If max_size=1, same conn — what does it return?

    await pool.close()


asyncio.run(main())

Step 6: repeat with transaction-level — observe the difference.

Terminal 2: pg_try_advisory_lock(1) → FALSE

Terminal 2: pg_try_advisory_lock(1) → TRUE

Terminal 2: pg_try_advisory_lock(1) → TRUE

Result: True

Summary and next step

What you learned:

• Session-level: pg_advisory_lock / pg_try_advisory_lock / pg_advisory_unlock. Survives across transactions, released when the conn closes.
• Transaction-level: pg_advisory_xact_lock / pg_try_advisory_xact_lock. Released automatically on commit/rollback. No manual unlock function.
• When session: long-running workers, crons, distributed singletons.
• When transaction: atomic operations, MV refresh, idempotency, the scope of one tx.
• The gotcha with pools: session-level stays held between requests if they reuse a conn.
• With PgBouncer transaction mode: only transaction-level is safe.
• Default: transaction-level when you can. More robust.

Before moving on, you should be able to:

• Decide between session vs transaction based on the case.
• Recognize when a connection pool causes leaks.
• Apply try/finally correctly.
• Use pg_advisory_unlock_all() as a defensive cleanup.

In the next lesson we consolidate the Pythonic pattern: a context manager that wraps advisory locks with automatic try/finally. You'll see implementations for sync (psycopg2) and async (asyncpg/SQLAlchemy 2.0), with correct handling of errors and connection pools.

Resources

1. PostgreSQL — Session vs Transaction Advisory Locks — official reference.
2. PgBouncer — Transaction mode caveats — pool mode considerations.
3. Vlad Mihalcea — Advisory locks — deep dive.
4. Heroku — Connection pooling and advisory locks — operational patterns.

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