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.

Pool integration: Session-level lock acquires through this typed API bump a per-connection counter, and the pool releases or discards the connection on return so that locks never leak to subsequent borrowers. Raw-SQL acquires (e.g. conn.exec("SELECT pg_advisory_lock(1)")) bypass this tracking — callers must release them explicitly or invoke advisoryUnlockAll before returning the connection to the pool.

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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, ValueError, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, ValueError, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, ValueError, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, ValueError, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, CatchableError,
                                PgNoRowsError, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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, PgNullError, PgTypeError],
    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-06-26 11:39:22 UTC