For OLTP workloads (such as web and mobile applications), it is important to understand object-level and row-level locks in PostgreSQL. There are several good materials that I can recommend reading:
- the official documentation is a must-read, as usual: "13.3. Explicit Locking" (do not be confused by the title – this article discusses not only explicit locks that we can add using queries like
LOCK TABLE ...or
SELECT ... FOR UPDATEor functions like
pg_advisory_lock(..)but also the locks introduced by regular SQL commands such as
- "PostgreSQL rocks, except when it blocks: Understanding locks" (2018) by Marco Slot – a great extension to the docs, explaining some aspects and conveniently "translating" the table provided in the docs from "lock language" to "SQL command language"
- As usual, a thorough and deep explanation of how PostgreSQL works by Egor Rogov: