PostgreSQL lock modes
Every statement takes a lock on the tables it touches, and some locks wait for others. Here is what each mode blocks, which statements take it, and how to see who holds it right now.
Which locks conflict
A cross means a session asking for the lock in the row waits while another session holds the lock in the column.
| Requested ↓ · Held → | ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | · | · | · | · | · | · | · | ✕ |
| ROW SHARE | · | · | · | · | · | · | ✕ | ✕ |
| ROW EXCLUSIVE | · | · | · | · | ✕ | ✕ | ✕ | ✕ |
| SHARE UPDATE EXCLUSIVE | · | · | · | ✕ | ✕ | ✕ | ✕ | ✕ |
| SHARE | · | · | ✕ | ✕ | · | ✕ | ✕ | ✕ |
| SHARE ROW EXCLUSIVE | · | · | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ |
| EXCLUSIVE | · | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ |
| ACCESS EXCLUSIVE | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ | ✕ |
The lock modes
- ACCESS SHARE lock in PostgreSQLThe weakest table lock, taken by every query that reads a table. It conflicts only with ACCESS EXCLUSIVE, but a transaction that read a table keeps it until it ends, which is enough to stall an ALTER TABLE.
- ROW SHARE lock in PostgreSQLSELECT … FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE take ROW SHARE on the table. At table level it only conflicts with EXCLUSIVE and ACCESS EXCLUSIVE; the waits you notice come from the row locks those statements take as well.
- ROW EXCLUSIVE lock in PostgreSQLThe lock every write takes on its table. Writers don’t block each other at table level; ROW EXCLUSIVE conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE, so an open write transaction holds up CREATE INDEX, CREATE TRIGGER, ADD FOREIGN KEY and most ALTER TABLE.
- SHARE UPDATE EXCLUSIVE lock in PostgreSQLThe lock for maintenance that must not run twice at once: VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY and a few ALTER TABLE forms. Reads and writes carry on; it conflicts with itself and with every stronger mode.
- SHARE lock in PostgreSQLCREATE INDEX (without CONCURRENTLY) takes SHARE on the table: reads continue, but every INSERT, UPDATE and DELETE waits until the index is built and the transaction ends. Several SHARE holders can coexist, so two index builds can run at once.
- SHARE ROW EXCLUSIVE lock in PostgreSQLCREATE TRIGGER, ENABLE/DISABLE TRIGGER and ALTER TABLE … ADD FOREIGN KEY take SHARE ROW EXCLUSIVE. Reads carry on; writes wait. A new foreign key takes it on both tables, so writes to the referenced table stop too.
- EXCLUSIVE lock in PostgreSQLEXCLUSIVE lets other sessions read the table and nothing else. REFRESH MATERIALIZED VIEW CONCURRENTLY takes it on the view; otherwise you only get it by asking with LOCK TABLE. The ExclusiveLock rows every transaction holds in pg_locks are a different thing.
- ACCESS EXCLUSIVE lock in PostgreSQLThe strongest table lock: while one session holds it, no other session can read or write the table, not even with a plain SELECT. Most forms of ALTER TABLE take it, as do DROP TABLE, TRUNCATE, VACUUM FULL and CLUSTER.