PostgreSQL advisory locks can keep cooperating workers that use the same database from entering the same job’s critical section at the same time. Define a stable lock key, have each worker try to acquire it, and run the work only if acquisition succeeds. This is useful for singleton or resource-scoped jobs; it is not a durable job queue, a cross-cluster lock, or a guarantee of exactly-once side effects.
What an advisory lock does—and what it does not
An advisory lock is an application-defined coordination signal. PostgreSQL grants or denies it, but it does not automatically make unrelated application code obey it. Every worker or code path that must coordinate needs to use the same key and locking convention. The PostgreSQL documentation describes advisory lock functions and their behavior at Advisory Locks.
Advisory locks are local to a database. They coordinate sessions connected to that database; they should not be treated as a lock shared across separate databases or PostgreSQL clusters. PostgreSQL represents advisory lock identifiers as either one 64-bit value or a pair of 32-bit values. Those key spaces do not overlap, so choose one scheme and document its namespace and meaning.
A lock records temporary ownership, not a job’s durable state. By itself it does not store pending jobs, track attempts, schedule retries, or make effects in another system happen exactly once. If a worker loses its connection after performing an external action but before recording success, a later attempt may repeat that action. Design the work to be idempotent, or give external operations their own deduplication or recovery mechanism.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →#1 Best Overall
Choose the lock lifetime that matches the work
| Lock function | Lifetime | Typical fit |
|---|---|---|
pg_try_advisory_lock |
Session-level; remains held until explicitly unlocked or the session ends. Rollback does not release it. | A job run that spans multiple transactions, provided the worker keeps the owning database session open and pinned. |
pg_try_advisory_xact_lock |
Transaction-level; released automatically when the transaction ends, including on abort. It cannot be manually unlocked. | A critical section that fits entirely within one transaction. |
Both functions make a nonblocking attempt at an exclusive lock: they return true when the lock is acquired immediately and false when it is unavailable. A false result means another session currently holds the conflicting lock; the worker can skip this run or choose another task, according to the application’s policy. Function signatures and details are in PostgreSQL’s Advisory Lock Functions reference.
Prevent two workers from running the same singleton job
For a recurring singleton task—such as a periodic cleanup or one global refresh—give the task a stable key shared by every worker. The key must not change from run to run unless the intent is to create a different lock. Avoid lossy hashing unless the consequences of two unrelated tasks colliding are acceptable; PostgreSQL accepts the documented integer key shapes, while uniqueness and namespace design belong to the application.
- Define the lock identity. Choose a documented 64-bit key or pair of 32-bit keys for the task. Ensure every worker uses the identical mapping.
- Attempt acquisition before starting protected work. Use
pg_try_advisory_lockif the job spans transactions, orpg_try_advisory_xact_lockif all protected work is contained in one transaction. - Run only after a successful attempt. If the result is false, do not enter the protected section. Skip, log, or defer the run according to a policy you define.
- Release or let the lock expire through session or transaction end. For a session lock, explicitly unlock on successful completion and on handled errors, and make sure the connection is closed if the worker cannot continue safely.
For session-level locks, keep the same PostgreSQL session associated with the worker for the whole ownership period. Do not acquire the lock on one pooled connection and assume a later query or unlock on an unrelated connection uses that same server session. Pooler behavior varies; check the selected pooler’s current documentation before relying on session affinity.
Handle errors, crashes, and retries separately
A session-level lock survives transaction rollback. If code obtains one and then a transaction aborts, the lock remains until it is explicitly unlocked or the session ends. Also, repeated acquisitions of the same session-level lock stack: each successful acquisition requires a corresponding unlock for early release. Keep acquisition and release paths easy to audit.
Free tools Windows power users keep installed
One-click scans. No signup required.
PostgreSQL automatically releases session locks when their owning session ends. That provides a recovery boundary when a process or connection disappears, but it does not establish whether the job’s work completed before that happened. Treat a subsequent run as potentially repeating unfinished or externally visible work. Record durable status separately when operators need to know which jobs ran, failed, or need retrying.
Transaction-level locks are often simpler when the entire critical section is one transaction: transaction completion releases them even when the transaction aborts. They are not suitable for protecting work that must continue after that transaction ends, such as a multi-step job with external calls between database transactions.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When a queue table with SKIP LOCKED is a better fit
Use an advisory lock when workers need to exclude one another from a single application-defined resource—for example, one singleton task or one job per logical account. Use a persisted queue table when jobs need durable rows, status transitions, per-job history, retries, or multiple workers claiming different jobs concurrently.
In a queue-table design, concurrent consumers can select eligible rows with SELECT ... FOR UPDATE SKIP LOCKED; a worker skips rows already locked by another transaction instead of waiting on them. PostgreSQL cautions that SKIP LOCKED produces an inconsistent view and is intended for queue-like consumers, not general-purpose reads. It solves row claiming, which is different from taking one advisory lock for a logical resource. See the PostgreSQL documentation on SELECT locking clauses.
Best Value
| Question | Advisory lock | Queue rows with SKIP LOCKED |
|---|---|---|
| What is being coordinated? | One application-defined resource or singleton task. | Persisted job rows, often with different workers claiming different jobs. |
| How long does ownership last? | One transaction or the owning session, depending on the lock function. | Typically the transaction holding the row lock; durable job status must be represented in the table. |
| Does the mechanism store retries or job history? | No; the lock itself stores no durable job record. | The table can store status and history if the application schema and logic provide them. |
| How does a worker respond to contention? | A try-lock returns false immediately, so the worker can skip or defer. | SKIP LOCKED lets a consumer move past rows another transaction has locked. |
Inspect locks and avoid operational surprises
Outstanding advisory locks are visible in PostgreSQL’s pg_locks view. Its database column matters because advisory locks are database-local. Consult the official pg_locks view documentation when diagnosing ownership or contention.
Advisory and regular locks draw on a finite shared lock-memory pool governed by settings including max_locks_per_transaction and max_connections. The PostgreSQL documentation describes typical capacity as tens to hundreds of thousands depending on configuration, not as a universal fixed limit. High-cardinality lock use should therefore be considered against the actual server configuration.
Be careful when invoking lock functions in a query with LIMIT. SQL expression evaluation can lead to locks being acquired for more rows than expected. PostgreSQL documents using a subquery to limit the rows passed to the lock call; review the advisory-lock section of the explicit locking documentation before using that pattern.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
