Introduction to Locking in PostgreSQL

Locking in PostgreSQL is essential for ensuring data consistency and isolation across concurrent transactions. PostgreSQL uses multi-version concurrency control (MVCC) to allow multiple transactions to access data simultaneously, but certain operations still require explicit locking to prevent conflicts.

Understanding the types of locks, their use cases, and potential performance implications is critical for optimizing database performance and avoiding deadlocks.

Types of Locks in PostgreSQL

PostgreSQL provides a variety of locks to handle different levels of data protection. These locks can be broadly categorized as:

  • Row-Level Locks

  • Table-Level Locks

  • Page-Level Locks

  • Advisory Locks

  • Deadlocks and Prevention

Let’s dive into each lock type with detailed explanations, real-world examples, and performance implications.

1. Row-Level Locks

Row-level locks allow fine-grained control over individual rows, ensuring minimal impact on other parts of the table.

Types of Row-Level Locks:

  • FOR UPDATE: Prevents other transactions from modifying or locking the same row until the current transaction completes.

  • FOR NO KEY UPDATE: Similar to FOR UPDATE, but weaker: it still blocks other transactions’ UPDATE, DELETE, FOR UPDATE and FOR SHARE on the row, while allowing FOR KEY SHARE. Ordinary UPDATEs that do not change a key column take this lock, so they do not block foreign-key checks from referencing tables.

  • FOR SHARE: Prevents modifications but allows other transactions to acquire a shared lock.

  • FOR KEY SHARE: The weakest row lock, taken by foreign-key checks. Other transactions can still update non-key columns, but cannot delete the row or change its key.

Real-World Application:

  • Order Management Systems: When updating the status of an order, acquiring a FOR UPDATE lock ensures no other transaction modifies or deletes the order concurrently.
BEGIN;
SELECT * FROM orders WHERE order_id = 101 FOR UPDATE;
-- Another transaction trying to update the same row will wait until the lock is released.

Edge Case:

  • Deadlocks: Occurs when two transactions hold locks that the other needs, leading to a stalemate.

  • Performance Implication: Row-level locks scale well, but frequent locking can lead to increased contention and deadlocks.

2. Table-Level Locks

Table-level locks apply to entire tables, preventing or allowing certain operations to be performed concurrently.

Types of Table-Level Locks:

  • ACCESS SHARE: Acquired by SELECT statements.

  • ROW SHARE: Acquired by SELECT ... FOR UPDATE or SELECT ... FOR SHARE.

  • ROW EXCLUSIVE: Acquired by INSERT, UPDATE, and DELETE.

  • SHARE UPDATE EXCLUSIVE: Used by VACUUM operations.

  • SHARE: Allows multiple transactions to read but not write.

  • EXCLUSIVE: Blocks writes and row locks; only plain SELECT (ACCESS SHARE) can run alongside it.

  • ACCESS EXCLUSIVE: Blocks all operations, including SELECT. Taken by most ALTER TABLE forms, DROP, TRUNCATE, VACUUM FULL and CLUSTER.

Real-World Application:

  • Schema Migrations: Most ALTER TABLE operations take an ACCESS EXCLUSIVE lock, which blocks reads and writes during the schema change. A waiting ACCESS EXCLUSIVE request also queues new readers behind it, so set lock_timeout on migrations.

  • Bulk Corrections: EXCLUSIVE mode blocks concurrent writes while still allowing plain reads.

BEGIN;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- Blocks writes and row locks until the transaction ends; plain SELECTs still run.

Edge Case:

  • Performance Implication: Table locks can lead to high contention in multi-user environments.

  • Deadlocks: High risk when combined with row locks.

3. Page-Level Locks

Page-level locks are used internally by PostgreSQL during index and table access operations.

  • Page Locks: Implicitly managed by PostgreSQL and not directly accessible to users.

  • Use Case: Prevents data corruption during index writes.

Real-World Application:

  • Index Maintenance: During large data insertions, page locks ensure index consistency.

4. Advisory Locks

Advisory locks provide application-level locking mechanisms that are independent of the standard SQL locks.

  • Session-level: Locks held until explicitly released with pg_advisory_unlock() or the session ends. They survive COMMIT and ROLLBACK, which matters with connection pools.

  • Transaction-level: Locks taken with pg_advisory_xact_lock() are held until the transaction commits or rolls back.

Real-World Application:

  • Distributed Systems Coordination: Advisory locks help coordinate processes accessing shared resources.
SELECT pg_advisory_xact_lock(12345);
-- Released when the transaction ends.

SELECT pg_advisory_lock(12345);
-- Session-level: held until pg_advisory_unlock(12345) or disconnect.
SELECT pg_advisory_unlock(12345);

Performance Implication:

  • Lightweight but requires careful management to avoid deadlocks.

Optimistic vs. Pessimistic Locking

Optimistic Locking:

  • Assumes minimal conflicts and only checks for conflicts at the time of commit.

  • Implementation: Use versioning or timestamps.

UPDATE products
SET price = 200, updated_at = now()
WHERE product_id = 1 AND updated_at = '2025-01-01 10:00:00';
-- 0 rows updated means another transaction changed the row first: reload and retry.

Real-World Application:

  • E-commerce: Prevents overwriting of product information by checking for updates before committing changes.

Pessimistic Locking:

  • Acquires locks at the beginning of a transaction to prevent other transactions from modifying the data.

  • Implementation:

BEGIN;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;

Real-World Application:

  • Banking Systems: Ensures account balances are not modified by multiple transactions simultaneously.

Deadlocks and Prevention

Detecting Deadlocks:

PostgreSQL detects deadlocks itself: after deadlock_timeout (1 second by default) it cancels one of the transactions with ERROR: deadlock detected, so the application must retry it. Sessions that are blocked but not deadlocked show up as lock waits, together with the sessions blocking them:

SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS xact_age, left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Log Monitoring:

  • Deadlocks are logged in the PostgreSQL log.

  • Location: pg_log or log_directory.

  • Command:

cat /var/log/postgresql/postgresql.log | grep 'deadlock'

Real-World Application:

  • High-Transaction Systems: Continuously monitor and resolve deadlocks to prevent transaction failures.

Preventing Deadlocks:

  • Order Transactions Consistently: Always access tables and rows in the same order.

  • Keep Transactions Short: Minimize the duration of locks.

  • Use NOWAIT/ SKIP LOCKED:

SELECT * FROM orders FOR UPDATE NOWAIT;

Comparison of Lock Types

Lock Type Scope Blocks Read Blocks Write Use Case
Row-Level (FOR UPDATE) Row No Yes Row updates and deletions
Table-Level (EXCLUSIVE) Table No Yes Blocking writes while allowing reads
Advisory Locks Application No No (blocks only the same advisory key) Application-level coordination
Access Share Table No No SELECT statements
Row Exclusive Table No Yes INSERT, UPDATE, DELETE
Access Exclusive Table Yes Yes Schema changes, DROP, TRUNCATE, VACUUM FULL, CLUSTER

Conclusion

Locking in PostgreSQL is a powerful mechanism that, when used correctly, can ensure data integrity and consistency. By understanding the various types of locks, their appropriate use cases, and how to manage deadlocks, developers can design efficient and resilient database applications. Optimistic and pessimistic locking strategies provide additional tools to handle concurrency effectively.

Production notes (October 2026)

These notes add the diagnostic queries, decision criteria and validation steps the topic needs on a production system.

Start with the waiting session, then find the blocker

PostgreSQL's MVCC lets ordinary reads and writes overlap in many cases, but transactions still acquire row and table locks. When an application endpoint slows down, a waiting query can be the victim rather than the cause. Tuning its SQL before finding the blocker may leave the incident untouched.

Take a snapshot while the wait is happening. Capture the time window, affected endpoint, session state, wait event, transaction age and the sessions returned by pg_blocking_pids(). A long idle in transaction session can hold locks after its last statement has finished.

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS transaction_age,
       pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

This view is an investigation starting point, not a full incident history. Permissions affect which session details you can see, and a queue may disappear before the next sample.

Turn waiting PIDs into a blocking chain

Run this read-only query while the slowdown is active. It pairs each waiting session with the PID that PostgreSQL says is ahead of it. An idle blocker can have no current query, so its transaction age and application owner matter more than its last SQL text.

SELECT w.pid AS waiting_pid, w.wait_event_type, w.wait_event,
       b.pid AS blocking_pid, b.state AS blocking_state,
       now() - b.xact_start AS blocking_transaction_age
FROM pg_stat_activity AS w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS blocked_by(pid)
JOIN pg_stat_activity AS b ON b.pid = blocked_by.pid
WHERE w.datname = current_database()
ORDER BY blocking_transaction_age DESC NULLS LAST;

pg_blocking_pids() accounts for both held locks and processes ahead in a lock queue. This is more reliable than trying to infer blockers from a simple self-join on pg_locks. Record repeated snapshots if the event lasts long enough; one snapshot only describes one moment. PostgreSQL's pg_locks reference explains the limitation.

Distinguish three common patterns

  • Row contention: multiple transactions update or lock the same rows. Check transaction boundaries and whether the application holds a lock during network calls or other slow work.
  • DDL queue: a schema change waits for a conflicting lock, and later application statements queue behind it. The DDL statement may be quick once granted; waiting time is the incident.
  • Deadlock: transactions wait on each other in a cycle. PostgreSQL detects the cycle and aborts one transaction. A long lock wait without a cycle is a different problem.

For a realistic sequence of the second pattern, see the synthetic DDL lock-queue walkthrough.

Why a quick migration can stop ordinary reads

An ALTER TABLE may only change a catalog entry, yet still request an ACCESS EXCLUSIVE table lock. If an older transaction already holds an ACCESS SHARE lock, the DDL waits. Later SELECT statements can queue behind that waiting DDL even though they would have been compatible with the older reader. In this pattern the migration's lock wait, rather than its execution time, determines the outage window.

Compare the migration start time with application latency, and look for a waiting DDL PID between the old transaction and new readers. The lock-contention triage page lists the first production signals to preserve.

Respond to the actual cause

During an incident, identify the oldest blocker and the owner of its transaction before terminating anything. For migrations, test the lock mode the command requests, keep transactions short, and consider a short lock_timeout in the migration session so a change fails rather than waiting indefinitely in a busy queue. A timeout does not make an unsafe migration safe by itself.

A migration-session lock_timeout bounds time spent waiting to acquire each lock; it does not limit how long the command runs after it gets the lock. Build a retry path and test the migration on a representative copy. Use statement_timeout separately when total statement duration also needs a bound.

After a change, compare wait time, queue length and application latency over a similar workload. If a query rewrite did not change the blocking graph, it did not resolve the lock issue. PostgreSQL's lock-mode reference and activity views are the primary references for these checks. For a bounded review of your own workload, see the PostgreSQL performance investigation.