🐘 PostgreSQL - Locks
Locking mechanisms vary a bit between PostgreSQL version but here is the general picture.
Two transactions cannot have conflicting locks for the same table/row. Any number of parallel transactions can access tables/rows with non-conflicting locks. Conflicting locks can be seen at PostgreSQL documentation.
A single transaction can never conflict with itself.
There are 4 different lock types. But developers usually only need to focus on table, row and advisory locks.
- table-level locks
- row-level locks
- page-level locks
- advisory locks
Table-level lock modes: This list of lock modes is from the most restrictive to the least restrictive. Note that these all are table-level locks even if they have row in their name.
ACCESS EXCLUSIVE: default mode for
LOCKcommand that fully locks the table, e.g.
EXCLUSIVE: only allow reads parallel to the running transaction, not implicitly used by any command.
SHARE ROW EXCLUSIVE: not implicitly used by any command.
SHARE UPDATE EXCLUSIVE: e.g.
CREATE INDEX CONCURRENTLY.
ROW EXCLUSIVE: e.g.
ROW SHARE: e.g.
SELECT FOR UPDATE.
ACCESS SHARE: e.g.
Row-level lock modes: This list of lock modes is from the most restrictive to the least restrictive. Note that none of the row-level locks block querying the row data.
FOR UPDATE: prevents all row-level locking by other transactions. Acquired by
DELETEand any unique index related
FOR NO KEY UPDATE: prevents most locking by other transactions, but not
FOR KEY SHARE. Acquired by the most
FOR SHARE: prevents locking and modification, but allows others to acquire the same lock. Blocks
FOR KEY SHARE: prevents locking and modification, but allows others to acquire the same lock. Blocks
DELETEs and key value changing
Advisory locks: Application-defined locks that are rarely needed. Session-level advisory locks are acquired as long as explicitly released or the session ends, but kept even after a single transaction. Multiple session-level locks stack so you must free them as many times you locked them. Transaction-level advisory locks behave like table- and row-level locks.
How to inspect locks.
SELECT * FROM pg_locks;
-- View with readable locks info and filtered out locks on system tables CREATE VIEW active_locks AS SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype, pg_locks.database, pg_locks.relation, pg_locks.page, pg_locks.tuple, pg_locks.virtualtransaction, pg_locks.pid, pg_locks.mode, pg_locks.granted FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE relname !~ '^pg_' and relname <> 'active_locks'; -- Now when we want to see locks just type SELECT * FROM active_locks;