In PostgreSQL, which table-level lock do SELECT, SELECT ... FOR UPDATE and UPDATE each take, and which mode alone blocks a plain SELECT?
answer
- the names are historical
- all eight are table-level
- ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE
- only the strongest mode stops readers
basics
~20 sA plain SELECT takes ACCESS SHARE, SELECT ... FOR UPDATE takes ROW SHARE and UPDATE takes ROW EXCLUSIVE, and none of the three conflict. Only ACCESS EXCLUSIVE, taken by DROP TABLE, TRUNCATE and many ALTER TABLE forms, blocks a plain SELECT.
solid answer
~50 sPostgreSQL has eight table-level lock modes, and despite their names `ROW SHARE` and `ROW EXCLUSIVE` lock the whole table; the only difference between modes is which other modes they conflict with. A plain `SELECT` takes `ACCESS SHARE`, a `SELECT` with `FOR UPDATE` (or any other `FOR` locking clause) takes `ROW SHARE`, and `INSERT`, `UPDATE`, `DELETE` and `MERGE` take `ROW EXCLUSIVE`. Those three modes do not conflict with one another, so readers, row-lockers and writers proceed together, and conflicts on individual rows are settled by row locks. `ACCESS SHARE` conflicts only with `ACCESS EXCLUSIVE`, which `DROP TABLE`, `TRUNCATE`, `VACUUM FULL` and many `ALTER TABLE` forms take, so that is the one mode that stops a plain read. Any mode can be taken explicitly with `LOCK TABLE`, which defaults to `ACCESS EXCLUSIVE` and holds the lock until the transaction ends.
code
sql · 7 linesBEGIN;
-- plain SELECTs still run; locking SELECTs and all writes wait
LOCK TABLE email_outbox IN EXCLUSIVE MODE;
INSERT INTO email_outbox_archive
SELECT * FROM email_outbox WHERE status = 'sent';
DELETE FROM email_outbox WHERE status = 'sent';
COMMIT; -- the table lock is released herego deeper
Recall the three everyday mappings: SELECT takes ACCESS SHARE, a FOR UPDATE read takes ROW SHARE, writes take ROW EXCLUSIVE, and only ACCESS EXCLUSIVE stops a plain read.
Explain that all eight modes are table locks whose behaviour comes only from the conflict list, and that LOCK TABLE defaults to ACCESS EXCLUSIVE and needs a transaction block.
Show you know which maintenance commands take ACCESS EXCLUSIVE and can read pg_locks mode names when a dashboard query suddenly stalls behind one.
Discuss when an explicit LOCK TABLE is the right tool versus row locks or advisory locks, weighing its blast radius on every reader and writer of the table.
## Table locks in PostgreSQL: eight modes, one rule Before a command touches a table, PostgreSQL takes a **table-level lock** on it. These are heavyweight locks kept in a shared-memory lock table, normally held until the transaction ends, and listed in the `pg_locks` view. There are **eight** modes, and the manual is blunt about their names: they are historical. `ROW SHARE` and `ROW EXCLUSIVE` lock the **whole table**, not rows. The only real difference between one mode and another is the set of modes it **conflicts** with: - Two transactions can never hold conflicting modes on the same table at the same time; the later request waits. - Non-conflicting modes can be held concurrently by any number of transactions. - A transaction never conflicts with itself, so it may take `ACCESS EXCLUSIVE` and later `ACCESS SHARE` on the same table. - Some modes are **self-conflicting** (one holder at a time), others are not. Conflicts on individual rows, such as two sessions updating the same email, are settled separately by **row locks** recorded in the row itself. ## What everyday statements take | Statement | Table lock mode | Name in `pg_locks.mode` | |---|---|---| | `SELECT` | `ACCESS SHARE` | `AccessShareLock` | | `SELECT ... FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, `FOR KEY SHARE` | `ROW SHARE` | `RowShareLock` | | `INSERT`, `UPDATE`, `DELETE`, `MERGE` | `ROW EXCLUSIVE` | `RowExclusiveLock` | None of these three modes conflicts with the others or with itself. That is why, on a busy `email_outbox` table, a dashboard reading the backlog, workers claiming rows with a locking clause and producers inserting new emails all run side by side at table level. A locking `SELECT` also takes plain `ACCESS SHARE` on any other table it reads without a `FOR` clause. ## The full conflict list | Mode | Typically taken by | Conflicts with | |---|---|---| | `ACCESS SHARE` | `SELECT` | `ACCESS EXCLUSIVE` only | | `ROW SHARE` | `SELECT` with a `FOR` locking clause | `EXCLUSIVE`, `ACCESS EXCLUSIVE` | | `ROW EXCLUSIVE` | `INSERT`, `UPDATE`, `DELETE`, `MERGE` | `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE` | | `SHARE UPDATE EXCLUSIVE` | `VACUUM` (not `FULL`), `ANALYZE`, `CREATE INDEX CONCURRENTLY` | itself, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE` | | `SHARE` | `CREATE INDEX` without `CONCURRENTLY` | `ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE` | | `SHARE ROW EXCLUSIVE` | `CREATE TRIGGER`, some `ALTER TABLE` forms | every mode except `ACCESS SHARE` and `ROW SHARE` | | `EXCLUSIVE` | `REFRESH MATERIALIZED VIEW CONCURRENTLY` | every mode except `ACCESS SHARE` | | `ACCESS EXCLUSIVE` | `DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, many `ALTER TABLE` forms, a bare `LOCK TABLE` | every mode, itself included | ## Why only ACCESS EXCLUSIVE stops a plain SELECT `ACCESS SHARE` conflicts with exactly one mode, so a plain `SELECT` waits only behind a transaction that holds, or is queued ahead of it for, `ACCESS EXCLUSIVE`. Writers never block plain readers at table level, and at row level MVCC lets the reader see the last committed version instead of waiting. The commands that take `ACCESS EXCLUSIVE` are the ones that remove or rewrite the table, or change it in ways a reader cannot tolerate: `DROP TABLE`, `TRUNCATE`, `REINDEX`, `CLUSTER`, `VACUUM FULL`, `REFRESH MATERIALIZED VIEW` without `CONCURRENTLY`, and many `ALTER TABLE` forms. Exactly which `ALTER TABLE` variant takes which mode, and how one queued `ACCESS EXCLUSIVE` request stalls every query behind it, belong to the schema-changes leaf. ## Taking a mode yourself with LOCK TABLE 1. The syntax is `LOCK TABLE name IN mode MODE [NOWAIT]`. 2. Without `IN ... MODE` it takes `ACCESS EXCLUSIVE`, the strongest mode. 3. It only works inside a transaction block; outside one PostgreSQL raises an error, because the lock would end with the statement. 4. The lock lasts until `COMMIT` or `ROLLBACK`, or a `ROLLBACK TO` a savepoint set before it. There is no `UNLOCK` command. 5. `NOWAIT` makes it fail at once instead of queueing for the lock. An archiving job that must stop new claims and writes on `email_outbox` while dashboards keep reading can take `LOCK TABLE email_outbox IN EXCLUSIVE MODE`: `EXCLUSIVE` conflicts with `ROW SHARE` and `ROW EXCLUSIVE` but not with `ACCESS SHARE`. ## Seeing table locks `pg_locks` shows one row per lock held or awaited, with `locktype = 'relation'`, the table's OID in `relation`, the mode name such as `RowExclusiveLock`, and `granted`. A row with `granted = false` is a session still waiting. Working out which session blocks it is easier with `pg_blocking_pids()`, which the monitoring leaf covers.
- Which PostgreSQL table lock modes are self-conflicting, and why does that matter?`SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE` and `ACCESS EXCLUSIVE` conflict with themselves, so only one transaction can hold each on a table at a time; `ACCESS SHARE`, `ROW SHARE`, `ROW EXCLUSIVE` and `SHARE` do not. That is why two `VACUUM` runs cannot work on the same table at once, while any number of writers holding `ROW EXCLUSIVE` can.
- Can a PostgreSQL transaction release a table lock before it commits?Not explicitly: there is no `UNLOCK` statement, and table locks are held until `COMMIT` or `ROLLBACK`. The one exception is a savepoint: a lock taken after `SAVEPOINT` is released by `ROLLBACK TO` that savepoint, along with everything else done since. An error escaping a PL/pgSQL exception block releases locks taken inside the block the same way.
- ROW EXCLUSIVE is a row lock covering only the rows being updated
- A plain SELECT waits for any transaction currently writing to the table
- LOCK TABLE without a mode takes a SHARE lock
- Run UNLOCK TABLES to release a LOCK TABLE lock before committing
- SELECT ... FOR UPDATE takes ROW EXCLUSIVE on the table, just like UPDATE