Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)
· 4 min read
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"[1] (do not be confused by the title – this article discusses not only explicit locks that we can add using queries like
LOCK TABLE ...[2] orSELECT ... FOR UPDATE[3] or functions likepg_advisory_lock(..)[4] but also the locks introduced by regular SQL commands such asALTERorUPDATE) - "PostgreSQL rocks, except when it blocks: Understanding locks"[5] (2018) by Marco Slot[6] – 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[7]:
Links
- [1] "13.3. Explicit Locking" https://www.postgresql.org/docs/current/explicit-locking.html
- [2] LOCK TABLE ... https://www.postgresql.org/docs/current/sql-lock.html
- [3] SELECT ... FOR UPDATE https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
- [4] pg_advisory_lock(..) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
- [5] "PostgreSQL rocks, except when it blocks: Understanding locks" https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
- [6] Marco Slot https://twitter.com/marcoslot
- [7] Egor Rogov https://twitter.com/e_g_Orius
- [8] "Locks in PostgreSQL: 1. Relation-level locks" https://postgrespro.com/blog/pgsql/5967999
- [9] "Locks in PostgreSQL: 2. Row-level locks" https://postgrespro.com/blog/pgsql/5968005