Handling PostgreSQL Lock Conflicts in Application Code

When an API request reads or changes PostgreSQL data, another request may hold a lock that the first request needs. PostgreSQL then makes the request wait, or returns an error when a timeout or concurrency failure ends the operation. Your code must turn that result into a sensible outcome: wait within the request’s latency budget, retry a safe transaction, report that a resource is busy, or explain a business-rule failure.

PostgreSQL uses locks to coordinate access to data while transactions run. A transaction groups database commands that PostgreSQL commits or rolls back as one unit. A SQLSTATE is a standardized code that identifies a database error. Use the SQLSTATE and the operation’s meaning together to decide what the API should do.

How PostgreSQL lock conflicts affect requests

PostgreSQL acquires locks as commands read or change data. Updates to the same row can make one transaction wait for the other to commit or roll back. Ordinary row locks do not block ordinary reads, but they can block other writers or commands that request conflicting row locks. Some table operations, such as TRUNCATE, take more restrictive table locks. PostgreSQL describes these behaviors in its documentation on explicit locking.

A wait does not necessarily mean the request will fail. Under PostgreSQL’s default READ COMMITTED isolation level, a waiting update can continue after the other transaction commits. PostgreSQL then rechecks whether the updated row still matches the waiting command’s WHERE condition. If it no longer matches, the command may update zero rows. Your code should interpret that result according to the request’s purpose: for example, a conditional update that changes no rows could mean that the resource is no longer available.

PostgreSQL holds most locks until the transaction ends. Keep transactions focused on the database work needed for correctness. If a request holds a transaction open while it waits for user input or an unrelated service, other requests that need the same data wait longer too.

Recognize the conflict from its outcome

A normal lock wait has no error code while it continues. PostgreSQL returns an error if a configured timeout ends the wait, if it detects a deadlock, or if a transaction fails an isolation check.

A deadlock occurs when transactions hold locks that each other needs. For example, two account transfers can deadlock if one locks account A and then B while the other locks B and then A. PostgreSQL detects the cycle and aborts one transaction. The error has SQLSTATE 40P01, deadlock_detected. Acquiring locks in a consistent order helps prevent this pattern; PostgreSQL also describes retrying an aborted transaction when prevention is insufficient in its locking documentation.

A serialization failure means PostgreSQL aborted a transaction to preserve the requested isolation guarantee. The failure has SQLSTATE 40001, serialization_failure and can occur at REPEATABLE READ or SERIALIZABLE isolation. PostgreSQL recommends retrying serialization failures, while noting that serializable transactions can fail even without a conventional lock wait. See serialization failure handling.

A request can also ask PostgreSQL to fail fast. SELECT ... FOR UPDATE NOWAIT returns an error rather than waiting when a conflicting row lock is unavailable. PostgreSQL identifies this condition as 55P03, lock_not_available. The lock_timeout setting instead aborts a statement when a lock-acquisition attempt waits longer than the configured limit. A statement_timeout limits the statement’s total duration. PostgreSQL documents that both timeout settings default to zero, which disables them, and that a shorter statement timeout fires before a longer lock timeout. See the documentation for SELECT locking clauses and client connection settings.

A unique constraint prevents duplicate values, and an exclusion constraint prevents conflicting values according to a specified rule. SQLSTATE 23505 means unique_violation; 23P01 means exclusion_violation. A concurrent race can cause either error, but so can an invalid or duplicate user request. The code alone does not tell you whether the condition is temporary.

Choose whether to wait, skip, or fail fast

Let PostgreSQL wait when completing the operation is worth the latency and the request has enough time to finish. Set timeouts to match the service’s request budget rather than leaving lock waits unbounded. The PostgreSQL defaults do not provide a request-level limit: lock_timeout and statement_timeout are disabled unless configured.

Use NOWAIT when the API should return promptly if a particular row is busy. Treat the result as contention only when that matches the operation’s meaning. A failure to acquire a lock does not by itself prove that the user violated a business rule.

Use SKIP LOCKED only when omitting locked rows is acceptable. PostgreSQL notes that this option gives an inconsistent view of the data, but it can suit queue workers that compete to claim available jobs. An API that promises a complete answer should not silently skip locked rows. The behavior is described in the SELECT documentation.

Retry the transaction only when the failure is transient

Retry SQLSTATE 40001 by rerunning the whole transaction, including the application logic that decides which statements and values to use. Retrying only the last failed statement does not repeat the full set of decisions that led to the transaction. PostgreSQL also says retrying 40P01 deadlocks may be advisable. Set a finite retry policy so repeated contention does not keep a request running indefinitely.

For 55P03 or a lock timeout, retry only when your API treats the contention as temporary and repeating the request is safe. If the request cannot wait or retry within its latency budget, return a clear temporary outcome.

For 23505 and 23P01, first determine what caused the violation. Retrying after a collision in an application-generated key can make sense if the code chooses a new key on each attempt. Retrying a duplicate user-supplied identifier generally repeats the same invalid request. PostgreSQL cautions that unique and exclusion errors need more care than serialization failures because the underlying condition may persist; see its retry guidance.

When a transaction retry repeats application logic, it must not duplicate external effects such as sending a notification or charging another service. Design those effects so a transaction that later rolls back and retries does not perform them twice. PostgreSQL requires the transaction logic to be rerun for a retry, but the application must choose how to handle effects outside the database.

In driver code, branch on the structured SQLSTATE field when the driver exposes one. PostgreSQL advises using error codes rather than matching message text because codes are less likely to change across releases and do not depend on message localization. Then consider the operation’s context: 55P03 from an intentional NOWAIT request has a clear “busy” meaning, while the same code in a path expected to wait deserves investigation. See PostgreSQL error codes.

Map database results to API behavior

PostgreSQL defines database errors, not HTTP responses or user-facing messages. Your API should make that mapping based on whether the request failed because of a business rule, temporary contention, or an unexpected database condition.

For example, report a constraint violation as a conflict or validation failure when it means the request cannot succeed as submitted. Retry a classified transient transaction failure within a bounded policy, then return a temporary-failure outcome if attempts are exhausted. For an intentional fail-fast lock request, tell the caller the resource is busy when that reflects the product behavior. Keep raw SQL and database error text out of user-facing responses.

Prevent and investigate recurring conflicts

Have transactions acquire multiple locks in the same order. That reduces deadlocks caused by requests touching the same rows in opposite sequences. Keep transactions short so they release locks promptly.

When requests wait unexpectedly, inspect pg_stat_activity for active sessions and wait events; PostgreSQL labels heavyweight-lock waits with the Lock wait-event type. The pg_locks view shows outstanding locks; access to details about other sessions depends on database privileges. See the activity monitoring documentation.

Do not treat deadlock_timeout as a request timeout. PostgreSQL 18 documents a one-second default for this setting, which controls when the server checks whether a lock wait is a deadlock. It does not end an ordinary lock wait after one second. The distinction is described in lock management settings.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com