Postgres locks

Concurrent operations are a must-have for databases used to power rich, multi-user applications. But in order to support concurrency, a database has to handle the possibility of two users accessing or editing the same thing at the same time.

In Postgres, simultaneous reads and writes are made possible by a rich set of locks, which can get quite complicated. Your average SaaS app won't have to worry about locks, but they can quickly become a pain point when dealing with complicated transactions.

Postgres locks control access at two levels: tables and rows. The majority of Postgres commands automatically acquire a lock that corresponds with their semantics. It's also possible to enable/disable locks manually via the LOCK command.

For table locks, the terminology is a bit confusing because some of them include the name "row". (Postgres's lock nomenclature is descended from Oracle's.) As can be seen in the following table, a lock is defined by how it conflicts with other locks.

Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCL.	SHARE UPDATE EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACCESS EXCL.
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

A useful way to understand Postgres's system of table locks is to walk backwards from the most restrictive type and view them in pairs.

• An ACCESS EXCLUSIVE lock conflicts with every other type. It's only acquired on heavy operations such as DROP TABLE, REINDEX, and VACUUM FULL. If you use a LOCK TABLE statement, it's the default.
• An EXCLUSIVE lock is a slightly less prohibitive version that allows concurrent reads via the ACCESS SHARE lock, which is used for read-only commands like SELECT. This lock is used by REFRESH MATERIALIZED VIEW CONCURRENTLY.
• A SHARE ROW EXCLUSIVE lock enables another corresponding operation: ROW SHARE, which is acquired for SELECT FOR UPDATE and FOR SHARE. It conflicts with itself, so you can only have one of these pending on a table at a time. It's acquired by CREATE TRIGGER and certain ALTER TABLE expressions. So you can update a table while creating a trigger on it, but you can't alter a table's triggers via multiple operations at the same time.
• A SHARE lock is the same thing as a SHARE ROW EXCLUSIVE one except that it doesn't conflict with itself. This is used for CREATE INDEX statements, since you should be able to create multiple indexes at the same time.
• A SHARE UPDATE EXCLUSIVE relaxes the constraint against concurrent ROW EXCLUSIVE locks, which activate with your standard CRUD operations: UPDATE, DELETE, INSERT, and MERGE. SHARE UPDATE EXCLUSIVE is acquired on ANALYZE, CREATE INDEX CONCURRENTLY, and COMMENT ON.

What about row-level locks?

Requested Lock Mode	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

Row locks are much simpler and have a couple of other nice guarantees. Unlike table locks, row locks only concern writes, so they do not block concurrent queries. And a single transaction can obtain multiple locks on the same row if needed.

If we go backwards again, we have:

• FOR UPDATE, which is used by DELETE and a few special cases of UPDATE.
• FOR NO KEY UPDATE is acquired by the rest of UPDATE operations and lets you run FOR KEY SHARE at the same time. FOR KEY SHARE is only acquired explicitly. The "FOR NO KEY" part can be understood as meaning that unlike FOR UPDATE, the row's key is not modified.
• FOR SHARE is another explicit-only lock.

So really the takeaway for rows is that you can't update the same row via multiple pending transactions. Trying to do so can trigger an infamous deadlock where two transactions try to update the same row, but neither can complete because the other blocks it from doing so.