Mutual exclusion coordinates concurrent database access through locks. PostgreSQL obtains locks at table or row level; conflicting lock types either permit or prevent another transaction from using the same object. section 6.5.1
Client A | Client B
------------------------+-----------------------
BEGIN; |
SELECT * FROM mytable; |
(...) |
| ALTER TABLE mytable
| ADD description text;
| *hangs*
A realistic PostgreSQL failure: Client A leaves a transaction open after reading, while Client B’s ALTER TABLE waits for the required lock. section 6.5.1
The tempting mistake is to assume that a completed SELECT leaves no operational consequence.
- An open transaction retains an ACCESS SHARE table lock, even though that lock does not block ordinary reads or writes. section 6.5.1
- ALTER TABLE requires ACCESS EXCLUSIVE, which conflicts with the lingering read lock and can wait indefinitely. section 6.5.1
