Concurrent PostgreSQL applications fail in subtle ways when two sessions believe they are the only writer. This lesson shows how PostgreSQL locks rows and tables, how deadlocks are detected, and how to write updates that stay correct when many workers operate at the same time. The outcome is practical: you should be able to reserve inventory, transfer value between rows, and diagnose lock waits without guessing.
Purpose in the Concurrency Section
Earlier transaction lessons explain atomicity and isolation. Locks are the machinery that makes those promises work when statements touch the same data. PostgreSQL combines multi-version concurrency control, called MVCC, with explicit lock modes. Readers normally see a snapshot and do not block ordinary writers. Writers still need locks because only one transaction can create the next version of a particular row at a time. Safe concurrent update design is mostly about choosing where to let PostgreSQL serialize access and where to let independent work proceed.
How PostgreSQL Locks Work
MVCC stores row versions as tuples. An update does not edit a tuple in place; it creates a new version and marks the old version as no longer current for future snapshots. To prevent two transactions from producing conflicting next versions, an UPDATE, DELETE, or SELECT ... FOR UPDATE takes a row-level lock on each selected row. If another transaction already holds an incompatible row lock, the later statement waits until the first transaction commits or rolls back.
PostgreSQL also takes table-level locks. The phrase is easy to misread: most table locks do not mean the whole table is frozen. A normal UPDATE takes a RowExclusiveLock on the table so it can coexist with reads and many writes, while conflicting with operations such as some schema changes. ACCESS EXCLUSIVE is the strong table lock that blocks ordinary reads and writes, and it is used by operations that must see the relation in a single shape.
Lock strength matters. FOR UPDATE protects a row before you change key values or dependent state. FOR NO KEY UPDATE is slightly weaker and is what ordinary updates usually acquire when key columns are not changed. FOR SHARE and FOR KEY SHARE allow more concurrent readers while still protecting referenced keys. Modifiers change waiting behavior: NOWAIT raises an error immediately if a row is locked, while SKIP LOCKED skips locked rows and is useful for job queues where each worker can take any available job.
Syntax Anatomy
The core pieces are the transaction boundary, the predicate that identifies the rows, the lock clause, and the update condition. The predicate must be backed by an index when the table is large; otherwise a statement may hold locks longer because it scans too much data. The update condition should encode the invariant, such as on_hand > 0, so a stale application-side check cannot oversell inventory.
CREATE TABLE inventory (
sku text PRIMARY KEY,
on_hand integer NOT NULL CHECK (on_hand >= 0)
);
INSERT INTO inventory (sku, on_hand) VALUES ('book-1', 2);This setup gives the examples one row with two units available. The check constraint is not a substitute for correct locking, but it is a final database-enforced guard: a negative count is invalid no matter which application path attempts it.
Example 1: Lock, Inspect, Then Update
The most explicit pattern is to lock the row, inspect it, then update it inside the same transaction. Session B running the same FOR UPDATE statement waits while Session A owns the row lock. After Session A commits, Session B rechecks the current row version and proceeds with current data.
BEGIN;
SELECT sku, on_hand
FROM inventory
WHERE sku = 'book-1'
FOR UPDATE;
UPDATE inventory
SET on_hand = on_hand - 1
WHERE sku = 'book-1';
COMMIT;With the initial data, the select returns book-1 | 2, and the committed row becomes book-1 | 1. This pattern is readable when business logic needs the locked row before deciding how to update it. Its cost is a longer critical section: application work between the select and update extends the time other sessions must wait.
Example 2: Atomic Conditional Update
When the decision is simple, a single conditional UPDATE is safer and shorter. PostgreSQL locks the matching row, verifies the predicate against the current row version, applies the change, and returns the new value. If two sessions run this statement while one unit remains, one succeeds and the other updates zero rows.
UPDATE inventory
SET on_hand = on_hand - 1
WHERE sku = 'book-1'
AND on_hand > 0
RETURNING sku, on_hand;The deterministic interpretation is simple: one returned row means the reservation succeeded, and no returned row means the item was unavailable at the moment PostgreSQL serialized that update. The application should not treat zero rows as an exception; it is a normal business result. This is usually the preferred inventory pattern because it minimizes lock duration and keeps the invariant in the statement.
Example 3: Transfer Rows in a Stable Order
Deadlocks happen when transactions wait on each other in a cycle. A transfer touches two account rows, so two sessions that lock the same pair in opposite order can deadlock. The robust design is to lock every row needed for the operation in a stable order before applying changes.
CREATE TABLE accounts (
id integer PRIMARY KEY,
balance integer NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 50);BEGIN;
SELECT id
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;The expected final balances are account 1 with 75 and account 2 with 75. The important part is not the arithmetic; it is ORDER BY id FOR UPDATE. Every transfer that touches two accounts must lock lower id first and higher id second. If all code paths follow that rule, PostgreSQL may make sessions wait, but it does not create a circular wait for the same pair.
Design Choices and Trade-offs
Use a single conditional update when the invariant can fit in one statement. It is compact, keeps locks short, and works well for counters, status transitions, and claiming ownership. Use SELECT ... FOR UPDATE when the decision requires multiple columns, related rows, or procedural work that must be based on a stable current row.
Use NOWAIT when waiting is worse than refusing, such as an administrative screen that can display “currently being edited.” Use a normal wait when eventual completion is expected and the user can tolerate latency. Use SKIP LOCKED for job workers, not for financial correctness, because skipped rows disappear from that statement’s view rather than proving they are irrelevant.
Isolation level is another choice. READ COMMITTED is PostgreSQL’s common default and each statement sees a fresh committed view. Correct conditional updates work well there because PostgreSQL rechecks the row after waiting. REPEATABLE READ and SERIALIZABLE provide stronger guarantees for multi-statement reasoning, but they can produce serialization failures that applications must retry.
Failure Modes and Troubleshooting
Symptom: requests hang and database CPU is low. Cause: sessions are waiting on row or table locks, often because a transaction stayed open after taking a lock. Diagnose: inspect pg_stat_activity for sessions with wait events such as Lock, then join to pg_blocking_pids(pid) to find blockers. Correct: commit or roll back the blocking transaction, then shorten future transactions so user input, HTTP calls, and long reports do not run while locks are held.
Symptom: PostgreSQL aborts one transaction with deadlock detected. Cause: two or more transactions acquired locks in different orders and formed a wait cycle. PostgreSQL’s deadlock detector breaks the cycle by canceling one statement. Diagnose: read the server log deadlock detail and compare the involved statements. Correct: impose a global lock order, reduce the rows locked per transaction, or replace multi-step logic with a single conditional statement.
Symptom: inventory or quota occasionally goes negative. Cause: the application selected a value, made a decision outside the database, and later updated without encoding the condition. Diagnose: search for update statements that subtract without AND on_hand > 0 or equivalent constraints. Correct: move the invariant into a conditional update and keep the check constraint as defense in depth.
Reliability and Performance Implications
Locks are reliable because they serialize conflicting changes, but serialization is a scarce resource. Long transactions increase latency for other writers, create larger MVCC cleanup debt, and make vacuum less effective because old row versions must remain visible to old snapshots. Index the predicates used to find locked rows, commit quickly, and set application-level timeouts so a blocked request does not consume a connection forever.
From a security perspective, avoid using powerful maintenance roles in application workers that handle locks. A compromised worker should not be able to terminate arbitrary sessions or change schema to escape lock conflicts. Operational tools that inspect blockers can have broader read access, but correction commands should be restricted and audited.
Hands-on Lab
Prerequisites: a local PostgreSQL database, two terminal sessions running psql against the same database, and permission to create temporary practice tables. Run the setup once:
DROP TABLE IF EXISTS lock_lab_inventory;
CREATE TABLE lock_lab_inventory (
sku text PRIMARY KEY,
on_hand integer NOT NULL CHECK (on_hand >= 0)
);
INSERT INTO lock_lab_inventory (sku, on_hand) VALUES ('pen', 1);Step 1 in Session A: take the row lock and leave the transaction open.
BEGIN;
SELECT sku, on_hand
FROM lock_lab_inventory
WHERE sku = 'pen'
FOR UPDATE;Step 2 in Session B: try the same lock.
BEGIN;
SELECT sku, on_hand
FROM lock_lab_inventory
WHERE sku = 'pen'
FOR UPDATE;Session B should wait. Verification from a third session:
SELECT blocked.pid AS blocked_pid,
blocker.pid AS blocker_pid,
blocked.wait_event_type,
blocked.wait_event
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker
ON blocker.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';The query should show Session B as blocked by Session A. Now commit Session A. Session B should finish and display the row. Roll back Session B so the lab returns to a clean transaction state.
COMMIT;
-- Run in Session B after it returns:
ROLLBACK;Cleanup removes the practice table:
DROP TABLE IF EXISTS lock_lab_inventory;Assessment
- You need to decrement a quota only when it is positive. Write the single SQL statement and explain why it is safe when two sessions run it at the same time.
- A worker queue uses
FOR UPDATE SKIP LOCKED. What result should a worker expect when all eligible jobs are already locked, and why is that not proof the queue is empty? - Two transfer functions deadlock once per day. Describe the lock-order rule you would introduce and how you would verify that every code path follows it.
- A migration is waiting behind an application transaction. Which PostgreSQL views or functions would you query to identify the blocker, and what application behavior would you change afterward?
- Compare
NOWAITwith waiting normally for a row lock. Give one user-facing workflow where each choice is appropriate.
Summary
PostgreSQL concurrency is not magic hidden behind transactions. MVCC lets readers and writers coexist, while row and table locks serialize operations that would otherwise corrupt shared state. Safe updates keep invariants inside SQL, lock rows in predictable orders, minimize transaction duration, and treat deadlock or serialization failures as retryable outcomes when appropriate. That is the practical foundation for the rest of the PostgreSQL in Depth work on transactions, schema changes, queues, and operational diagnosis.
