How does locking work in PostgreSQL?

Question

Accepted Answer

PostgreSQL uses **locks** to coordinate concurrent access and prevent conflicts. Thanks to **MVCC**, reads generally don't need locks (readers don't block writers), but writes acquire row-level locks, and various lock types coordinate operations. Understanding locking helps avoid contention and deadlocks.

## MVCC means reads usually don't lock

```text
Because of MVCC, plain SELECTs do NOT take row locks — they read a consistent snapshot.
→ Readers don't block writers, writers don't block readers (a key Postgres advantage).
Locking mainly comes into play for WRITES and explicit locking.
```

## Row-level locks (from writes and explicit locking)

```sql
-- UPDATE/DELETE automatically lock the affected ROWS (until the transaction ends)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- locks row 1

-- SELECT ... FOR UPDATE — explicitly lock rows you intend to update (prevent races)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
-- ... compute, then UPDATE — no other transaction can modify it meanwhile
UPDATE accounts SET balance = ... WHERE id = 1;
COMMIT;
```

`SELECT ... FOR UPDATE` locks rows so concurrent transactions can't modify them — preventing race conditions in read-modify-write operations (e.g. safely decrementing inventory).

## Lock types and table locks

```text
ROW-level → ACCESS via UPDATE/DELETE/SELECT FOR UPDATE — fine-grained
TABLE-level → some DDL (ALTER TABLE) takes heavier table locks → can block queries
→ Schema changes on busy tables can cause contention; plan migrations carefully
  (Postgres has gotten better — many ALTERs are now non-blocking, but not all).
```

## Avoiding lock problems and deadlocks

```text
✓ Keep transactions SHORT (hold locks briefly → less contention)
✓ Acquire locks in a CONSISTENT ORDER across transactions → avoid DEADLOCKS
✓ Use FOR UPDATE deliberately for read-modify-write; consider FOR UPDATE SKIP LOCKED
  for queue-like patterns (skip already-locked rows)
✓ Watch for long-held locks blocking others (pg_locks, pg_stat_activity to diagnose)
✓ Be cautious with DDL on busy tables (can block)
```

## Why it matters

Understanding PostgreSQL locking is important for building **correct, performant concurrent applications**, so it's valuable senior-level knowledge for systems with significant concurrent database access.

The foundational insight is that **MVCC means reads usually don't lock** (readers don't block writers — a key Postgres concurrency advantage), so locking primarily concerns **writes and explicit locking**.

Understanding **row-level locks** (acquired by UPDATE/DELETE) and especially **`SELECT ... FOR UPDATE`** (explicitly locking rows to prevent race conditions in read-modify-write operations — like safely decrementing inventory or processing a balance, where without locking concurrent transactions could cause incorrect results) is important for **correctness** in concurrent scenarios.

Knowing that **DDL/schema changes can take heavier table locks** (potentially blocking queries on busy tables — an important consideration when planning migrations, though Postgres has improved many ALTERs to be non-blocking) helps avoid contention during deployments.

Understanding how to **avoid lock problems and deadlocks** — keeping transactions short (holding locks briefly), acquiring locks in consistent order (preventing deadlocks), using `FOR UPDATE` deliberately (and `SKIP LOCKED` for queue patterns), and diagnosing lock contention (pg_locks, pg_stat_activity) — is valuable for performance and reliability under concurrency.

Since concurrency is inherent in real applications, and since proper use of locking (especially `FOR UPDATE` for correctness in read-modify-write operations, and avoiding contention/deadlocks) is what makes concurrent database code correct and performant, understanding PostgreSQL locking — the MVCC-no-read-locks model, explicit row locking for correctness, table locks from DDL, and avoiding deadlocks/contention — is valuable senior-level knowledge for building robust concurrent applications and operating Postgres under load, a frequently-relevant concern that demonstrates understanding of database concurrency control.