PostgreSQL - Locks

PostgreSQL - Concurrency and Locks

Understanding Concurrency with MVCC

PostgreSQL manages concurrency using a sophisticated model called Multi-Version Concurrency Control (MVCC). Unlike systems that use read locks, MVCC allows readers and writers to operate simultaneously without blocking each other. When a row is updated, PostgreSQL creates a new version of the row instead of overwriting the old one. Transactions see a consistent “snapshot” of the database, ensuring that reads are non-blocking and highly performant.

Because of MVCC, you often don’t need to manually lock tables for reading. PostgreSQL handles most locking automatically and at the row level. For example, an UPDATE or DELETE on a row will automatically place an exclusive lock on that specific row for the duration of the transaction, preventing other transactions from modifying it.

Explicit Locking: When and Why

While automatic locking is sufficient for most operations, there are scenarios where you need to manually control locking at a higher level (e.g., the entire table). This is done with the LOCK command, which must be executed inside a transaction block (BEGIN...COMMIT).

Common use cases for explicit locking include:

• Performing complex, multi-stage data updates or maintenance where you must prevent any concurrent modifications to ensure consistency.
• Preventing other sessions from acquiring stronger locks that could conflict with your transaction’s long-running operations.

Syntax

BEGIN;
LOCK TABLE table_name IN lock_mode;
-- ... your operations here ...
COMMIT;

PostgreSQL offers various lock modes, from least to most restrictive. A key few are:

• ACCESS SHARE (Taken by SELECT): Conflicts only with ACCESS EXCLUSIVE.
• ROW EXCLUSIVE (Taken by UPDATE, INSERT, DELETE): Conflicts with SHARE and EXCLUSIVE modes.
• SHARE: Allows other sessions to read (with SELECT), but not modify, the table.
• ACCESS EXCLUSIVE: The most restrictive mode. Conflicts with all other locks. Prevents any other transaction from reading or writing to the table. This is what DROP TABLE uses.

There is no UNLOCK TABLE command. All locks acquired during a transaction are automatically released when the transaction ends with a COMMIT or ROLLBACK.

Deadlocks

A deadlock occurs when two (or more) transactions are waiting for each other to release locks. PostgreSQL’s lock manager can automatically detect deadlocks. When it does, it will terminate one of the transactions by raising an error, allowing the other to proceed.

Example of a deadlock:

-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Session 2:
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;

-- Session 1 (now waits for Session 2 to release lock on id=2):
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Session 2 (now waits for Session 1 to release lock on id=1 -> DEADLOCK):
UPDATE accounts SET balance = balance + 200 WHERE id = 1;
-- ERROR: deadlock detected

Best Practice: To avoid deadlocks, ensure all applications that access multiple resources lock them in the same, consistent order.

Advisory Locks

Advisory locks are a cooperative locking mechanism. PostgreSQL does not enforce them automatically; it’s up to the application logic to acquire and release them correctly. They are useful for managing application-level concurrency, such as ensuring only one instance of a background job runs at a time.

Example: Ensuring a single background worker.

-- A background worker process would run this:
-- The number 12345 is an arbitrary key for our lock.

SELECT pg_try_advisory_xact_lock(12345);

-- If the result is 'true', the lock was acquired.
-- If 'false', another worker already holds the lock.

-- The lock is automatically released at the end of the transaction.