Table Lock Modes

Concrete problem

A team runs this migration during the day:

ALTER TABLE orders ADD COLUMN note text;

One analyst runs a slow SELECT on orders. It takes 5 minutes. The migration waits for it. During those 5 minutes, every new query on orders also stops. The web site hangs. Nobody runs a conflicting query. Why does the whole table stop?

Background terms

Table lock: A table lock is a lock on a whole table. Every statement takes one. A plain SELECT takes a weak table lock. ALTER TABLE takes a strong one. Table locks are separate from row locks.
DDL: DDL means data definition language. These are the commands that change the shape of the database: CREATE INDEX, ALTER TABLE, DROP TABLE, TRUNCATE. A command that only reads or writes rows is not DDL.
Lock queue: Each lock has a queue of waiting requests. A new request must not conflict with the locks that are held. A new request must also not conflict with the requests that wait ahead of it. If it conflicts with a waiting request, it joins the queue.
Migration: A migration is a script that changes the database schema. A deploy usually runs it.

Intuition

Row locks answer “who may change this row”. Some commands change the whole table. They must stop other commands from using the table in the wrong way.

CREATE INDEX reads every row. It needs no one to write. Readers can continue.
VACUUM cleans old row versions. It needs no other VACUUM on the same table. Readers and writers can continue.
ALTER TABLE ... ADD COLUMN changes the shape of every row. No one can use the table. Not even readers.

Postgres has eight modes. They fall in three tiers. Each mode has a rule: what it blocks.

Mode	Taken by	Blocks plain `SELECT`	Blocks `SELECT ... FOR UPDATE`	Blocks `INSERT`, `UPDATE`, `DELETE`	Blocks itself
`ACCESS SHARE`	`SELECT`	no	no	no	no
`ROW SHARE`	`SELECT ... FOR UPDATE` and the other locking clauses	no	no	no	no
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`	no	no	no	no
`SHARE UPDATE EXCLUSIVE`	`VACUUM`, `ANALYZE`, `CREATE INDEX CONCURRENTLY`	no	no	no	yes
`SHARE`	`CREATE INDEX`	no	no	yes	no
`SHARE ROW EXCLUSIVE`	`CREATE TRIGGER`, some `ALTER TABLE`	no	no	yes	yes
`EXCLUSIVE`	`REFRESH MATERIALIZED VIEW CONCURRENTLY`	no	yes	yes	yes
`ACCESS EXCLUSIVE`	`DROP`, `TRUNCATE`, `VACUUM FULL`, most `ALTER TABLE`	yes	yes	yes	yes

Read the table as follows. Row “SHARE” says: while a transaction holds SHARE, an INSERT waits, a plain SELECT runs, and a second SHARE request (another CREATE INDEX) also runs.

This table is the conflict table in compact form. Two modes conflict when one blocks what the other one does. The full table is symmetric. Every mode also blocks ACCESS EXCLUSIVE. The columns of the table do not show this case.

Numeric example

The migration problem, step by step. The table is orders.

Time	Event	Lock state of `orders`
0:00	Analyst starts a 5-minute `SELECT` (`ACCESS SHARE`).	Held: `ACCESS SHARE`.
0:01	Migration asks for `ACCESS EXCLUSIVE`. It conflicts with the held lock.	Held: `ACCESS SHARE`. Queue: `ACCESS EXCLUSIVE`.
0:02	A web request runs `SELECT` (`ACCESS SHARE`). It does not conflict with the held lock. It does conflict with the queued `ACCESS EXCLUSIVE`.	The web request joins the queue behind the migration.
0:03	More web requests arrive. Each joins the queue.	Queue grows.
5:00	The analyst’s `SELECT` ends.	The migration gets the lock.
5:00 + a few ms	The migration ends.	The queue drains.

Without the queue rule, the web requests at 0:02 would run, because ACCESS SHARE does not conflict with ACCESS SHARE. The migration would wait until no reader remains. On a busy table, this never happens. The queue rule stops starvation of the migration. The price: the migration blocks everybody while it waits.

The wait at 0:01 to 5:00 is 5 minutes. The migration itself needs only a few milliseconds.

Visual

The three tiers. An arrow means “blocks more”.

graph LR
  subgraph Everyday
    AS["ACCESS SHARE"]
    RS["ROW SHARE"]
    RX["ROW EXCLUSIVE"]
  end
  subgraph Maintenance
    SUX["SHARE UPDATE EXCLUSIVE"]
    S["SHARE"]
    SRX["SHARE ROW EXCLUSIVE"]
    X["EXCLUSIVE"]
  end
  AX["ACCESS EXCLUSIVE"]
  AS --> RS --> RX --> SUX --> S --> SRX --> X --> AX

The migration queue:

sequenceDiagram
  participant A as Analyst SELECT
  participant M as Migration ALTER TABLE
  participant W as Web SELECT
  A->>A: holds ACCESS SHARE (5 min)
  M->>A: asks ACCESS EXCLUSIVE, waits
  W->>M: asks ACCESS SHARE, queues behind M
  A-->>M: ends, lock granted
  M-->>W: ends, queue drains

Notation

Take a table lock yourself:

LOCK TABLE orders IN SHARE MODE;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;

LOCK TABLE t with no mode uses ACCESS EXCLUSIVE. The NOWAIT clause from Wait Policies works here. SKIP LOCKED does not, because it works on rows.

Implicit table locks:

SELECT takes ACCESS SHARE.
SELECT ... FOR UPDATE (and the other locking clauses) takes ROW SHARE.
INSERT, UPDATE, DELETE take ROW EXCLUSIVE.

An UPDATE takes two locks: ROW EXCLUSIVE on the table, and a row lock on each row it changes. The table locks of two UPDATEs never conflict. The row locks decide who waits.

A safe migration:

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

If the lock is not free in 2 seconds, the statement raises an error. The queue drains. The team retries later.

Plain-English translation

“ACCESS SHARE” means “I read this table.”
“ROW EXCLUSIVE” means “I write rows in this table.” The word “exclusive” refers to the rows. It does not exclude other writers.
“SHARE” means “No one may write this table while I read all of it.”
“ACCESS EXCLUSIVE” means “No one may touch this table.”

The names are confusing. Use the table in the Intuition section instead of the names.

Equation

The full conflict table has 64 cells (8 modes by 8 modes). Count the modes that conflict with each mode:

Mode	Conflicts with
`ACCESS SHARE`	1
`ROW SHARE`	2
`ROW EXCLUSIVE`	4
`SHARE UPDATE EXCLUSIVE`	5
`SHARE`	5
`SHARE ROW EXCLUSIVE`	6
`EXCLUSIVE`	7
`ACCESS EXCLUSIVE`	8

1 + 2 + 4 + 5 + 5 + 6 + 7 + 8 = 38 of 64

Most pairs (38 of 64) conflict. The weak modes conflict with almost nothing. This is why everyday traffic runs in parallel.

What changes when parameters change

CREATE INDEX CONCURRENTLY. It takes SHARE UPDATE EXCLUSIVE, not SHARE. Writers continue. The command is slower.
A short ALTER TABLE with lock_timeout. The queue forms for at most the timeout.
No long query is running. ALTER TABLE gets its lock at once. The stall does not happen. The risk is the long query.
A long transaction that is idle. An idle transaction can still hold ACCESS SHARE, because locks last until the transaction ends. It blocks the migration as well.
A transaction that holds row locks. It also holds a table lock: ROW SHARE or ROW EXCLUSIVE. ALTER TABLE conflicts with both. So ALTER TABLE also waits for a transaction that only holds row locks. Row locks do not replace table locks. The two systems work together.

Practice problem

Try these on a later review. Do not open the answers first.

A runs CREATE INDEX CONCURRENTLY on t. It takes SHARE UPDATE EXCLUSIVE. For each statement from B, say “wait” or “ok”: (a) INSERT into t, (b) VACUUM on t, (c) CREATE INDEX without CONCURRENTLY on t, (d) TRUNCATE t.
A migration runs BEGIN; ALTER TABLE t ADD COLUMN c int;. Then the deploy script does 10 minutes of other work before COMMIT. What happens to other sessions that use t? No other session held a lock on t before the migration.
Two sessions each run an UPDATE on different rows of t. Does one wait for the other at table level?

Answers

(a) ok. ROW EXCLUSIVE does not conflict with SHARE UPDATE EXCLUSIVE. (b) wait. SHARE UPDATE EXCLUSIVE blocks itself. (c) wait. SHARE conflicts with SHARE UPDATE EXCLUSIVE. (d) wait. ACCESS EXCLUSIVE conflicts with every mode.

Every statement on t waits for 10 minutes, even a plain SELECT. The ACCESS EXCLUSIVE lock lasts until the end of the transaction, not until the end of the statement. Keep a migration transaction short.

No. Both take ROW EXCLUSIVE, and this mode does not conflict with itself. Only a conflict on the same row makes one wait.

Demo

Each scenario opens real concurrent sessions against a local Postgres. The log shows every statement with a time stamp. The folder is 04-table-lock-modes.

Scenario	What it shows
01-table-lock-matrix	The conflict table, tested on all 64 pairs. 38 pairs conflict.
02-ddl-queue-stall	The migration problem. A waiting `ALTER TABLE` stalls later readers.
03-migration-lock-timeout	The fix. `lock_timeout` and a retry loop limit the stall.
04-create-index-blocks-writes	`CREATE INDEX` blocks `INSERT`. `CONCURRENTLY` does not.

To run one scenario, start Postgres with ./up.sh. Then run go run ./04-table-lock-modes/01-table-lock-matrix in the repository. To run all scenarios of this concept, use ./run.sh 04-table-lock-modes.

Quiz

Asked on 2026-10-05 after the lesson. All three answers were correct.

Quiz (2026-10-05)

Q1. A runs a long plain SELECT on table t. B runs ALTER TABLE t ADD COLUMN, which needs ACCESS EXCLUSIVE, and waits. C then starts a plain SELECT on t. What happens to C?

C runs at once

C waits behind B

C raises an error

C runs, and Postgres cancels B

Answer and reasoning

Your answer: C waits behind B (correct). Correct answer: C waits behind B.

A new request must not conflict with the waiting requests ahead of it. C’s ACCESS SHARE conflicts with B’s queued ACCESS EXCLUSIVE. So C joins the queue. The first option ignores the queue rule.

Q2. A runs CREATE INDEX (no CONCURRENTLY) on table t. Which statement from B waits for A?

Plain SELECT on t

SELECT ... FOR UPDATE on t

A second CREATE INDEX (no CONCURRENTLY) on t

INSERT into t

Answer and reasoning

Your answer: INSERT into t (correct). Correct answer: INSERT into t.

CREATE INDEX takes SHARE. SHARE blocks ROW EXCLUSIVE, which INSERT takes. SHARE does not block ACCESS SHARE (plain SELECT), ROW SHARE (SELECT ... FOR UPDATE), or another SHARE.

Q3. Which step makes a migration with ALTER TABLE safe for live traffic?

Set lock_timeout before the ALTER TABLE, and retry after an error

Add SKIP LOCKED to the ALTER TABLE

Run SELECT ... FOR UPDATE on every row of the table first

Run the ALTER TABLE at the READ COMMITTED isolation level

Answer and reasoning

Your answer: Set lock_timeout before the ALTER TABLE, and retry after an error (correct). Correct answer: Set lock_timeout before the ALTER TABLE, and retry after an error.

With lock_timeout, the migration gives up if it waits too long. The queue then drains. The second option is wrong because SKIP LOCKED works on rows only. The third option is wrong because row locks do not stop other sessions from queuing behind the migration. The fourth option is wrong because the isolation level does not change the table lock that ALTER TABLE needs.

My reasoning

Quiz on 2026-10-05: all three correct. The user applied the lock queue rule and the SHARE conflicts without error. The earlier belief that locks always block readers did not return.

Session history

2026-10-05 — First taught. Quiz all correct. See 2026-10-05 Databases - Postgres Locking.
2026-10-05 — Demo section added. See 04-table-lock-modes.

Egghead

Explorer