Locks

First, read official docs rigorously: Explicit Locking.

And The Locking Clause for SELECT.

Lock is a way to ensure an operation happen without interference. Lock lifecycle is a Transaction. Lock releases when transaction finished.

Lock will release when savepoint rolled back if the lock was acquired after that savepoint.

There are many levels of lock: table-level, row-level, page-level, advisory locks. Page-level locks are taking cared by Postgres. Advisory locks for applications.

The fast-path exists to accelerate acquisition and release of locks that rarely conflict.
Fast-path locks. This lock can avoid lock conflict checks, thus faster.
We can use Fastpath lock if:

  • ACCESS SHARE
  • ROW SHARE
  • ROW EXCLUSIVE
  • SHARE UPDATE EXCLUSIVE
  • SHARE
  • SHARE ROW EXCLUSIVE
  • EXCLUSIVE
  • ACCESS EXCLUSIVE (blocks everything)

A SELECT can block a DDL change, so it is good to set lock_timeout or statement_timeout. This number should be less than 15s (or even better 10s, 5s):

SET statement_timeout = '5000ms';
SET lock_timeout = '5000ms';

  • FOR UPDATE — Also acquire by DELETE
  • FOR NO KEY UPDATE — Also acquire by UPDATE. This does not block SELECT FOR KEY SHARE
  • FOR SHARE — Blocks UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE . Does not block SELECT FOR SHARE and SELECT FOR KEY SHARE
  • FOR KEY SHARE

Locking a row may cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

ACCESS SHARE

Read from a table. SELECT (without FOR UPDATE / FOR SHARE)
Any query only reads a table and does not modify table acquires this lock.

ROW SHARE

SELECT FOR UPDATE and SELECT FOR SHARE acquire this lock on target table and acquire ACCESS SHARE lock on any other referenced tables.

ROW EXCLUSIVE
Modify a table. Acquired by INSERT, UPDATE, DELETE on target table and acquire ACCESS SHARE lock on any other referenced tables.

SHARE UPDATE EXCLUSIVE
This lock mode protects table against concurrent schema changes and VACUUM runs.
Acquired by VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY. Some ALTER INDEX and ALTER TABLE commands.

SHARE
This lock mode protects a table against concurrent data changes.
Acquired by CREATE INDEX (without CONCURRENTLY).

SHARE ROW EXCLUSIVE
This lock mode protects a table against concurrent data changes.
This is self-exclusive, so only one session can hold it at a time.
Acquired by CREATE TRIGGER and some forms of ALTER TABLE.

EXCLUSIVE
This lock mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

ACCESS EXCLUSIVE
This lock mode guarantees the holder is the only transaction accessing the table in any way.

Acquired by DROP TABLE, ALTER TABLE, VACUUM FULL, TRUNCATE, REINDEX, CLUSTER, REFRESH MATERIALIZED VIEW (without CONCURRENTLY). Many forms of ALTER INDEX and ALTER TABLE also acquire this lock. This is the default lock mode for LOCK TABLE statements that do not specify a lock mode explicitly.

Requested Lock Mode Existing Lock Mode
ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE
ACCESS SHARE               ✘
ROW SHARE             ✘ ✘
ROW EXCL.         ✘ ✘ ✘ ✘
SHARE UPDATE EXCLUSIVE       ✘ ✘ ✘ ✘ ✘
SHARE     ✘ ✘   ✘ ✘ ✘
SHARE ROW EXCLUSIVE     ✘ ✘ ✘ ✘ ✘ ✘
EXCLUSIVE   ✘ ✘ ✘ ✘ ✘ ✘ ✘
ACCESS EXCLUSIVE ✘ ✘ ✘ ✘ ✘ ✘ ✘ ✘

From the table we can tell few insights: