Locks in PostgreSQL and How to Avoid Contention

Locks are how PostgreSQL stops two transactions from stepping on each other. Most of the time they work invisibly, and you never think about them. Then one day a deploy runs a migration that takes an exclusive lock, and the whole application hangs for 30 seconds because every query is queued behind it. Understanding what locks what, and for how long, is how you avoid turning a routine change into an outage.

This is part 19 of the PostgreSQL Masterclass, following isolation levels.

Two kinds of locks

Postgres has table-level locks and row-level locks, and they serve different purposes.

Row-level locks protect individual rows during writes. When you UPDATE a row, Postgres locks that row so another transaction can't update it at the same time. Crucially, row write locks don't block reads: a plain SELECT never waits for a row's write lock, because Postgres serves the older row version from its MVCC snapshot. Readers and writers don't block each other, which is one of the best things about Postgres under load.

Table-level locks protect the table's structure and coordinate operations that affect the whole table. ALTER TABLE, DROP TABLE, CREATE INDEX, and VACUUM FULL take table locks of varying strength. These are the ones that cause visible stalls, because a strong table lock blocks everything else on that table.

The rule that matters most: readers and writers don't block

This is worth stating plainly because it shapes how you design for concurrency. In PostgreSQL:

• Reads never block reads.
• Reads never block writes, and writes never block reads.
• Writes to the same row block each other.

So the only everyday contention is two transactions trying to write the same row. If your workload spreads writes across many rows, you'll rarely see lock waits. Contention concentrates when many transactions fight over the same few rows, like a counter everyone increments, or a single "next number" row.

Explicit row locks

Sometimes you want to lock a row you're only reading, to protect a read-modify-write against the race from the isolation article. SELECT ... FOR UPDATE does that: it locks the selected rows as if you were about to update them, so no other transaction can change them until you commit.

BEGIN;
  SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- locks the row
  -- any other transaction's FOR UPDATE on this row now waits
  UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;  -- lock released

This is the standard fix for oversell and similar races at Read Committed. The next article is dedicated to these patterns, so here the point is just that explicit row locks exist and are the usual tool for serializing access to a specific row. There's also FOR SHARE, a weaker lock that lets others read-lock the same row but not write it.

Where table locks bite: migrations

The dangerous locks are the table-level ones taken by schema changes, and they're dangerous because a strong lock has to wait for existing transactions to finish, and then makes new ones wait behind it. A few specifics that save real outages:

Adding a column is cheap in modern Postgres. ADD COLUMN with no default, or with a constant default, is fast because it doesn't rewrite the table. But ADD COLUMN ... DEFAULT (a volatile expression) rewrites every row under a strong lock.

Adding an index with plain CREATE INDEX locks the table against writes for the whole build. Use CREATE INDEX CONCURRENTLY instead, which we covered in the indexing module, to build without blocking writes.

Even a fast ALTER TABLE can cause a pileup. It needs a brief strong lock, and to get it, it waits for current transactions on the table to finish. If a long-running transaction is holding the table, your ALTER waits, and every query that arrives after it queues behind the ALTER. A 1-millisecond schema change can freeze the app because it's stuck in line behind one slow transaction. Setting a short lock_timeout before a migration makes it fail fast instead of blocking everything:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN notes text;

Seeing what's locked

When something hangs, pg_locks joined with pg_stat_activity shows who holds what and who's waiting:

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

pg_blocking_pids() is the quick way to find which session is blocking another. In an incident, this query tells you exactly which transaction to investigate or cancel.

Reducing contention

To keep locks from becoming a problem:

• Keep transactions short, so locks are held briefly. This is the same advice as the transactions article, and it's the habit that pays off most.
• Spread writes across rows instead of funneling them through one hot row; if you need a global counter, consider sharding it or using a sequence.
• Run schema changes with CONCURRENTLY where available and a short lock_timeout, and avoid table-rewriting operations during peak traffic.
• Update rows in a consistent order across your code to reduce the chance of deadlocks, which is the very next topic.

Locks are not something to fear; they're the machinery that makes concurrent writes correct. The trouble comes from holding them too long or taking a strong one at the wrong time. Short transactions and lock-aware migrations prevent nearly all of it.

Next, we look at what happens when two transactions each hold a lock the other wants: deadlocks, how Postgres resolves them, and how to design so they rarely happen.