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.

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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                        PgConnectionError, SslError, KeyError,
                                        ProtocolError, PgTimeoutError,
                                        AsyncTimeoutError],
            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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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, PgQueryError,
                                PgConnectionError, SslError, KeyError,
                                ProtocolError, PgTimeoutError,
                                AsyncTimeoutError, PgError, 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). 
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). 
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). 
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). 
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). 
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). 
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). 
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).

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-04-03 06:45:30 UTC