Locks

Lock is a way to ensure an operation happen without interference. Lock lifecycle is a Transaction.

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.

Table Level

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

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';

Row Level

Explicit Locking
https://www.postgresql.org/docs/current/explicit-locking.html