async_postgres/pg_advisory_lock

Search:
Group by:

PostgreSQL Advisory Lock API

Provides an async interface to PostgreSQL's advisory locking facility. Advisory locks are application-enforced locks that do not lock any actual table rows — they simply act on application-defined lock identifiers.

Two flavours exist:

  • Session-level locks (default) — held until explicitly released or the session ends.
  • Transaction-level locks — released automatically at the end of the current transaction; no explicit unlock is needed.

Locks can be exclusive (default) or shared (multiple sessions may hold the same shared lock concurrently).

Stacking: Session-level advisory locks are stackable — if the same session acquires the same lock multiple times, it must be released the same number of times before it is truly released. The withAdvisoryLock templates handle acquire/release as a pair, but be careful not to nest them with the same key unintentionally. Transaction-level locks are not stackable and are always released at transaction end.

Key space: PostgreSQL uses a single advisory-lock name space per database that both session-level and transaction-level locks share — a session-level advisoryLock(42) blocks a concurrent advisoryLockXact(42) on another connection and vice versa. The single-int64 and (int32, int32) overloads live in separate sub-spaces (PostgreSQL treats the two signatures as distinct lock types), so advisoryLock(1'i64) never blocks advisoryLock(0'i32, 1'i32) even though the numeric identifiers overlap. Shared and exclusive locks on the same key follow standard reader/writer semantics — a shared lock blocks concurrent exclusive acquires but not other shared acquires.

Pool integration: Typed acquires set a sticky sessionLockDirty flag; the pool runs pg_advisory_unlock_all on return based on the flag (not the counter), so a typed advisoryUnlock of a raw-acquired key cannot forge a lock-free state. Raw-SQL acquires still bypass tracking — callers must release them explicitly or invoke advisoryUnlockAll before returning the connection.

Mixed raw + typed usage: heldSessionLocks counts only typed acquires, but advisoryUnlock* decrements it whenever the server reports the lock was released — including when the released lock was acquired via raw SQL. Under mixed usage the counter therefore under-reports still-held tracked locks. The onLeakedSessionLocks hook fires on sessionLockDirty rather than the counter, so leak detection stays accurate even when the counter has been driven to zero; the delivered count field is an unreliable lower bound in that regime. Prefer sticking to a single API (typed or raw) per connection checkout.

Example

# Session-level exclusive lock (blocking)
await conn.advisoryLock(42'i64)
defer: await conn.advisoryUnlock(42'i64)

# Non-blocking try
if await conn.advisoryTryLock(42'i64):
  defer: await conn.advisoryUnlock(42'i64)
  echo "acquired"

# Transaction-level lock (auto-released on COMMIT/ROLLBACK)
conn.withTransaction:
  await conn.advisoryLockXact(42'i64)
  echo "locked for this transaction"

# Two-key variants
await conn.advisoryLock(1'i32, 2'i32)
await conn.advisoryUnlock(1'i32, 2'i32)

# RAII-style convenience
conn.withAdvisoryLock(42'i64):
  echo "lock held here"

Procs

proc advisoryLock(conn: PgConnection; key1, key2: int32;
                  timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a session-level exclusive advisory lock using two int32 keys. 
proc advisoryLock(conn: PgConnection; key: int64;
                  timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a session-level exclusive advisory lock, blocking until available. 
proc advisoryLockShared(conn: PgConnection; key1, key2: int32;
                        timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a session-level shared advisory lock using two int32 keys. 
proc advisoryLockShared(conn: PgConnection; key: int64;
                        timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a session-level shared advisory lock, blocking until available. 
proc advisoryLockXact(conn: PgConnection; key1, key2: int32;
                      timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, PgStateError, ValueError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a transaction-level exclusive advisory lock (two int32 keys). 
proc advisoryLockXact(conn: PgConnection; key: int64;
                      timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, PgStateError, ValueError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a transaction-level exclusive advisory lock, blocking until available. Automatically released at end of the current transaction. 
proc advisoryLockXactShared(conn: PgConnection; key1, key2: int32;
                            timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, PgStateError, ValueError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a transaction-level shared advisory lock (two int32 keys). 
proc advisoryLockXactShared(conn: PgConnection; key: int64;
                            timeout: Duration = ZeroDuration): Future[void] {.
    ...stackTrace: false, raises: [Exception, PgStateError, ValueError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Acquire a transaction-level shared advisory lock, blocking until available. Automatically released at end of the current transaction. 
proc advisoryTryLock(conn: PgConnection; key1, key2: int32;
                     timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a session-level exclusive advisory lock (two int32 keys). 
proc advisoryTryLock(conn: PgConnection; key: int64;
                     timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a session-level exclusive advisory lock without blocking. Returns true if the lock was acquired. 
proc advisoryTryLockShared(conn: PgConnection; key1, key2: int32;
                           timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a session-level shared advisory lock (two int32 keys). 
proc advisoryTryLockShared(conn: PgConnection; key: int64;
                           timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a session-level shared advisory lock without blocking. Returns true if the lock was acquired. 
proc advisoryTryLockXact(conn: PgConnection; key1, key2: int32;
                         timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, PgStateError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a transaction-level exclusive advisory lock (two int32 keys). 
proc advisoryTryLockXact(conn: PgConnection; key: int64;
                         timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, PgStateError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a transaction-level exclusive advisory lock without blocking. Returns true if the lock was acquired. 
proc advisoryTryLockXactShared(conn: PgConnection; key1, key2: int32;
                               timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, PgStateError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a transaction-level shared advisory lock (two int32 keys). 
proc advisoryTryLockXactShared(conn: PgConnection; key: int64;
                               timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, PgStateError,
                                CatchableError, PgNoRowsError, PgTypeError,
                                PgNullError, PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Try to acquire a transaction-level shared advisory lock without blocking. Returns true if the lock was acquired. 
proc advisoryUnlock(conn: PgConnection; key1, key2: int32;
                    timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Release a session-level exclusive advisory lock (two int32 keys). 
proc advisoryUnlock(conn: PgConnection; key: int64;
                    timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Release a session-level exclusive advisory lock. Returns true if the lock was held and successfully released. 
proc advisoryUnlockAll(conn: PgConnection; timeout: Duration = ZeroDuration): Future[
    void] {....stackTrace: false, raises: [Exception, ValueError, CatchableError],
            tags: [RootEffect, TimeEffect], forbids: [].}
Release all session-level advisory locks held by the current session. 
proc advisoryUnlockShared(conn: PgConnection; key1, key2: int32;
                          timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Release a session-level shared advisory lock (two int32 keys). 
proc advisoryUnlockShared(conn: PgConnection; key: int64;
                          timeout: Duration = ZeroDuration): Future[bool] {.
    ...stackTrace: false, raises: [Exception, ValueError, CatchableError,
                                PgNoRowsError, PgTypeError, PgNullError,
                                PgProtocolError],
    tags: [RootEffect, TimeEffect], forbids: [].}
Release a session-level shared advisory lock. Returns true if the lock was held and successfully released.

Macros

macro withAdvisoryLock(conn: PgConnection; key1, key2: int32; body: untyped): untyped

Acquire a session-level exclusive advisory lock (two int32 keys), execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLock(conn: PgConnection; key1, key2: int32; timeout: Duration;
                       body: untyped): untyped

Acquire a session-level exclusive advisory lock (two int32 keys) with a timeout, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLock(conn: PgConnection; key: int64; body: untyped): untyped

Acquire a session-level exclusive advisory lock, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLock(conn: PgConnection; key: int64; timeout: Duration;
                       body: untyped): untyped

Acquire a session-level exclusive advisory lock with a timeout, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLockShared(conn: PgConnection; key1, key2: int32;
                             body: untyped): untyped

Acquire a session-level shared advisory lock (two int32 keys), execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLockShared(conn: PgConnection; key1, key2: int32;
                             timeout: Duration; body: untyped): untyped

Acquire a session-level shared advisory lock (two int32 keys) with a timeout, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLockShared(conn: PgConnection; key: int64; body: untyped): untyped

Acquire a session-level shared advisory lock, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

macro withAdvisoryLockShared(conn: PgConnection; key: int64; timeout: Duration;
                             body: untyped): untyped

Acquire a session-level shared advisory lock with a timeout, execute body, then release the lock (even on exception).

If unlocking fails (for example because the connection was lost), the failure is reported through the connection's tracer (onAdvisoryUnlockFailed) so the original exception from body is not masked.

Templates

template withAdvisoryLockXact(conn: PgConnection; key1, key2: int32;
                              body: untyped)
Acquire a transaction-level exclusive advisory lock (two int32 keys) inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXact(conn: PgConnection; key1, key2: int32;
                              timeout: Duration; body: untyped)
Acquire a transaction-level exclusive advisory lock (two int32 keys) with a timeout inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXact(conn: PgConnection; key: int64; body: untyped)
Acquire a transaction-level exclusive advisory lock inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXact(conn: PgConnection; key: int64; timeout: Duration;
                              body: untyped)
Acquire a transaction-level exclusive advisory lock with a timeout inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXactShared(conn: PgConnection; key1, key2: int32;
                                    timeout: Duration; body: untyped)
Acquire a transaction-level shared advisory lock (two int32 keys) with a timeout inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXactShared(conn: PgConnection; key1, key2: int32;
                                    body: untyped)
Acquire a transaction-level shared advisory lock (two int32 keys) inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXactShared(conn: PgConnection; key: int64;
                                    body: untyped)
Acquire a transaction-level shared advisory lock inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction. 
template withAdvisoryLockXactShared(conn: PgConnection; key: int64;
                                    timeout: Duration; body: untyped)
Acquire a transaction-level shared advisory lock with a timeout inside a transaction, execute body. The lock is automatically released at transaction end. Must be called within withTransaction.

Made with Nim. Generated: 2026-08-22 10:47:28 UTC