ruk·si
/notes

🐘 PostgreSQL
Locks

Updated at 2015-11-08 22:02

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 LOCK command that fully locks the table, e.g. ALTER TABLE, DRO TABLE and VACUUM FULL.
  • 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: e.g. CREATE INDEX.
  • SHARE UPDATE EXCLUSIVE: e.g. VACUUM, ANALYZE and CREATE INDEX CONCURRENTLY.
  • ROW EXCLUSIVE: e.g. UPDATE and DELETE.
  • ROW SHARE: e.g. SELECT FOR UPDATE.
  • ACCESS SHARE: e.g. SELECT.

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 DELETE and any unique index related UPDATEs.
  • FOR NO KEY UPDATE: prevents most locking by other transactions, but not FOR KEY SHARE. Acquired by the most UPDATE commands.
  • FOR SHARE: prevents locking and modification, but allows others to acquire the same lock. Blocks DELETEs and UPDATEs.
  • FOR KEY SHARE: prevents locking and modification, but allows others to acquire the same lock. Blocks DELETEs and key value changing UPDATEs.

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;

Sources