Locking and Concurrency Control: Shared, Exclusive, Deadlocks, and More

Databases let many users access the same data simultaneously. Locks are the mechanism that makes this safe. Understanding locks helps you write code that is both correct and fast under concurrency.

Introduction

Databases let many users access the same data simultaneously. Without locks, concurrent transactions would overwrite each other’s changes, read each other’s uncommitted work, or read data that never actually persisted. Locks are the mechanism that makes concurrent access safe — and understanding them is the difference between code that works in development and code that fails silently in production under load.

Shared vs Exclusive Locks

PostgreSQL has two main lock modes: shared and exclusive.

Shared Locks

Shared locks allow concurrent reads. Multiple transactions can hold shared locks on the same row at the same time.

-- Transaction 1
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- Transaction 2 can also read
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- Success: both can read simultaneously

Exclusive Locks

Exclusive locks block both shared and exclusive locks. Only one transaction can hold an exclusive lock at a time.

-- Transaction 1
UPDATE accounts SET balance = 900 WHERE id = 1;
-- Acquires exclusive lock

-- Transaction 2 tries to read
SELECT balance FROM accounts WHERE id = 1;
-- Waits...

-- Transaction 1 commits, then Transaction 2 proceeds

FOR SHARE vs FOR UPDATE

SELECT ... FOR SHARE acquires a shared lock. SELECT ... FOR UPDATE acquires an exclusive lock.

-- Shared lock: others can read but not update
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- Exclusive lock: blocks everyone else
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Use FOR SHARE when you need to read data and ensure it doesn’t change while you’re reading. Use FOR UPDATE when you need to modify data.

Lock Escalation

Some databases have a concept called lock escalation, where the system converts many row-level locks into a table-level lock when too many rows are locked.

PostgreSQL does NOT have lock escalation. You can hold locks on millions of rows if needed, though this consumes memory and impacts performance.

Other databases like SQL Server do have lock escalation, which can cause blocking problems when many rows are locked simultaneously.

-- PostgreSQL: no lock escalation
-- This is fine even with many rows
UPDATE orders SET processed = true WHERE status = 'pending';
-- Holds row-level locks, not escalated to table lock

Deadlocks

flowchart LR
    subgraph T1["Transaction A"]
    A1["Locks Row 1<br/>(UPDATE orders)"]
    A2["Waits for Row 2<br/>(UPDATE accounts)"]
    end
    subgraph T2["Transaction B"]
    B1["Locks Row 2<br/>(UPDATE accounts)"]
    B2["Waits for Row 1<br/>(UPDATE orders)"]
    end
    A1 --> A2
    B1 --> B2
    A2 -.->|"Row 2 held by B"| B1
    B2 -.->|"Row 1 held by A"| A1

A deadlock happens when two or more transactions are waiting for each other to release locks. Neither can proceed. PostgreSQL detects this automatically and rolls back one transaction.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- Transaction 2 (concurrent)
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Locks row 2

-- Transaction 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Waits for row 2

-- Transaction 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Deadlock! Transaction 1 holds row 1

PostgreSQL detects deadlocks automatically and resolves them by rolling back one transaction.

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ShareLock on transaction 67890; blocked by process 12345.
HINT:  See the server log for query details.

Preventing Deadlocks

The best deadlock is the one you prevent. Three rules help:

1. Always lock rows in the same order. If Transaction A always locks row 1 before row 2, and Transaction B does the same, deadlocks cannot occur.

-- Always lock accounts in id order
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- ...

2. Keep transactions short. Long transactions hold locks longer and increase deadlock probability.

3. Access shared objects last. In transaction-intensive workloads, access shared lookup tables after modifying user data.

Handling Deadlocks in Application Code

Your application should be prepared to retry on deadlock errors:

def transfer_funds(from_account, to_account, amount):
    max_retries = 3
    for attempt in range(max_retries):
        try:
            with connection.cursor() as cursor:
                cursor.execute("BEGIN")
                cursor.execute(
                    "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                    [from_account]
                )
                cursor.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    [amount, from_account]
                )
                cursor.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    [amount, to_account]
                )
                cursor.execute("COMMIT")
                return True
        except DeadlockError:
            cursor.execute("ROLLBACK")
            continue
    return False

Optimistic vs Pessimistic Concurrency Control

There are two philosophical approaches to concurrency control.

Pessimistic Concurrency Control

Assume conflicts will happen and prevent them with locks.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions must wait

Pessimistic locking guarantees no conflicts. It’s simple to reason about and works well under high contention. The downside is that locks block other transactions, which can cause waiting and deadlocks, and reduces throughput under low contention.

Optimistic Concurrency Control

Assume conflicts are rare, detect them when they occur, and retry.

-- Read current version
SELECT balance, version FROM accounts WHERE id = 1;
-- balance=1000, version=5

-- Update with version check
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;
-- If version changed, update affects 0 rows -> retry

Optimistic locking has no locks and therefore higher throughput under low contention, and avoids deadlocks. The downside is conflicts cause retries and wasted work if conflicts are frequent, and the application logic is more complex.

When to Use Each

Pessimistic is better when contention is high, conflicts are expensive (long transactions), or you need guaranteed ordering. Optimistic is better when contention is low, conflicts are cheap (short transactions), or you want maximum throughput.

Optimistic vs Pessimistic: Trade-off Summary

Row-Level vs Page-Level vs Table-Level Locks

PostgreSQL locks at the row level by default, but can escalate to page-level and table-level locks internally.

Lock Hierarchy

From least to most restrictive:

1. Row share — acquired by SELECT FOR SHARE
2. Row exclusive — acquired by UPDATE, DELETE, INSERT
3. Share update exclusive — acquired by VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
4. Share — acquired by CREATE INDEX (non-concurrent)
5. Share row exclusive — acquired by CREATE TRIGGER
6. Exclusive — acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY
7. Access exclusive — acquired by ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL

-- This acquires row-exclusive lock (fine)
UPDATE accounts SET balance = 900 WHERE id = 1;

-- This acquires access exclusive lock (blocks everything)
ALTER TABLE accounts ADD COLUMN new_col TEXT;

Lock Mode Compatibility

Advisory Locks

PostgreSQL provides advisory locks for application-level locking needs.

When to Use Advisory Locks

Use advisory locks when you need to coordinate work that doesn’t map naturally to table locks. Common uses are preventing duplicate batch processing, rate limiting, and resource reservation.

-- Acquire advisory lock
SELECT pg_advisory_lock(12345);

-- Do work
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Release
SELECT pg_advisory_unlock(12345);

Advisory Lock Functions

• pg_advisory_lock(key) — exclusive advisory lock
• pg_advisory_lock_shared(key) — shared advisory lock
• pg_try_advisory_lock(key) — non-blocking exclusive lock
• pg_try_advisory_lock_shared(key) — non-blocking shared lock
• pg_advisory_unlock(key) — release exclusive lock
• pg_advisory_unlock_all() — release all locks held by session

-- Non-blocking: returns true if acquired, false if not
SELECT pg_try_advisory_lock(12345);
-- Returns: true (or false if someone else holds it)

Monitoring Locks

Viewing Active Locks

SELECT
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    l.virtualxid,
    l.transactionid,
    p.pid,
    p.usename,
    p.query
FROM pg_locks l
JOIN pg_stat_activity p ON l.pid = p.pid
WHERE NOT l.pid = pg_backend_pid();

Viewing Lock Waits

SELECT
    p.pid,
    p.usename,
    p.query,
    l.locktype,
    l.mode
FROM pg_locks l
JOIN pg_stat_activity p ON l.pid = p.pid
WHERE NOT l.granted
AND NOT l.pid = pg_backend_pid();

Killing a Blocking Process

-- Get the pid from the queries above
SELECT pg_terminate_backend(12345);
-- This rolls back the transaction and releases locks

Real-World Case Study: Amazon DynamoDB’s Lock-Free Design

Amazon DynamoDB chose lock-free concurrency for its distributed key-value store. The reasoning: at massive scale, managing locks across distributed nodes is expensive and creates single points of failure. Traditional database locks require a coordinator to manage shared state — that coordinator becomes a bottleneck and a fragility point.

DynamoDB uses optimistic concurrency control instead. Each item has a Version attribute (or you can use conditional writes with EXPECT conditions). When you update an item, you specify the expected current state. If the condition fails (another write committed first), DynamoDB returns a ConditionalCheckFailedException. Your application decides whether to retry.

This design choice shows up in their API directly:

response = table.update_item(
    Key={'pk': 'order123'},
    UpdateExpression='SET total = :new_total',
    ConditionExpression='total = :expected_total',  # optimistic lock
    ExpressionAttributeValues={
        ':new_total': 150.00,
        ':expected_total': 100.00  # if this changed, fail
    }
)

The consequence: DynamoDB can scale horizontally without lock coordination overhead, and it never blocks a writer waiting for another writer. The tradeoff is that high-contention items (multiple writes to the same key simultaneously) generate many failed conditional writes and require application-level retries. For most real-world workloads this works well — contention is rare. For write-heavy hot keys, you need a separate strategy (write sharding, or accepting last-writer-wins semantics).

The lesson applies to PostgreSQL: optimistic locking via version columns gives you similar benefits in a single-node or primary-replica setup — no lock manager overhead, no blocking, and horizontal read scalability via MVCC. The cost is retry logic in your application and wasted work when conflicts are frequent.

Common Production Failures

Deadlock victim retry not implemented: Your code does a fund transfer with FOR UPDATE but does not retry on deadlock errors. When PostgreSQL rolls back a deadlock victim, the application throws an exception and the transfer is lost. Every write transaction using FOR UPDATE in a concurrent workload needs a retry loop — deadlocks are not a sign of bad code, they are an inevitable consequence of concurrent locking.

Long-running transactions holding locks: A report query runs inside a transaction, holds locks for 10 minutes while generating a PDF, then commits. During that time, every concurrent update to the rows it read is blocked. The fix is to use READ COMMITTED and read rows without locks, or to ensure transactions are as short as possible.

Optimistic locking without retry logic: You implement version-based optimistic concurrency but only check if the update succeeded once — you don’t retry. Concurrent updates fail silently (0 rows affected) and the user sees no feedback. You need a retry loop that re-reads the row, increments the version, and tries again.

FOR UPDATE on a scan that returns many rows: You write SELECT * FROM orders WHERE status = 'pending' FOR UPDATE expecting to lock just a few rows. If the table has millions of pending orders, this escalates to a table-level lock and blocks all concurrent access to the orders table. Lock only the specific rows you need.

SELECT FOR SHARE blocking writes: On PostgreSQL, SELECT ... FOR SHARE holds a row-level share lock. Other transactions can read but cannot update. If your “read-only” report runs with FOR SHARE and the table has frequent updates, your report blocks writers. Use plain SELECT in READ COMMITTED mode unless you genuinely need to prevent concurrent updates.

Advisory lock not released on error: You acquire pg_advisory_lock but an exception occurs before pg_advisory_unlock. The lock is held until the session ends. If your connection pool recycles connections without resetting state, you can leak advisory locks across requests. Always use pg_try_advisory_lock in a BEGIN/ROLLBACK block so the lock is released on error.

Quick Recap Checklist

• Shared locks allow concurrent reads; exclusive locks block both reads and writes
• PostgreSQL has no lock escalation — row locks remain row-level
• Deadlocks are resolved by PostgreSQL rolling back one transaction automatically
• Prevent deadlocks by locking rows in consistent order across transactions
• Pessimistic locking blocks conflicts; optimistic locking detects them at commit
• FOR SHARE acquires shared lock; FOR UPDATE acquires exclusive lock
• Advisory locks are session-scoped, not transaction-scoped
• Always use retry logic for serialization failures in application code
• Long-running transactions hold snapshot and prevent vacuum cleanup
• Connection pools must satisfy: pool_size × instances <= max_connections

Common Problems and Fixes

Interview Questions

SELECT
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    p.pid,
    p.usename,
    p.query,
    l.transactionid
FROM pg_locks l
JOIN pg_stat_activity p ON l.pid = p.pid
WHERE NOT l.pid = pg_backend_pid()
ORDER BY l.granted;

Further Reading

• PostgreSQL Explicit Locking documentation — complete reference for all lock modes and compatibility matrix
• PostgreSQL Advisory Lock functions — advisory lock API reference
• MVCC: How PostgreSQL Implements Concurrent Access — internal MVCC mechanics and visibility rules
• pg_locks view documentation — monitoring locks via system catalog
• PostgreSQL transaction isolation levels — how isolation relates to locking behavior

Conclusion

Locks are how databases make concurrent access safe. Shared locks allow readers to coexist; exclusive locks serialize writers. Deadlocks happen when transactions hold locks in inconsistent orders, but you can prevent them with consistent locking order and short transactions. Choose pessimistic locking when contention is high, optimistic when it’s low. PostgreSQL’s row-level locking avoids the escalation problems of other databases, and advisory locks give you flexibility for application-level coordination.

For more on keeping transactions safe, see Transaction Isolation and Relational Databases.