Table Lock Modes

Topic: Databases

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\text{ 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

A runs CREATE INDEX (no CONCURRENTLY) on t. It takes SHARE. For each statement from B, say “wait” or “ok”.

1. Plain SELECT on t.
2. UPDATE on t.
3. VACUUM on t (SHARE UPDATE EXCLUSIVE).
4. A second CREATE INDEX on t (SHARE).
5. SELECT ... FOR UPDATE on t.

Answers: 1 ok, 2 wait, 3 wait, 4 ok, 5 ok.

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.

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 (your answer, correct)
• C raises an error
• C runs, and Postgres cancels B

Why: 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 (your answer, correct)

Why: 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 (your answer, correct)
• 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

Why: 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.