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
(blocksSELECT
)
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
FOR UPDATE
FOR NO KEY UPDATE
FOR SHARE
FOR KEY SHARE
Explicit Locking
https://www.postgresql.org/docs/current/explicit-locking.html