Skip to main content

One post tagged with "Troubleshooting"

View All Tags

Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)

· 4 min read
Nikolay Samokhvalov

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:

Links

  1. [1] "13.3. Explicit Locking" https://www.postgresql.org/docs/current/explicit-locking.html
  2. [2] LOCK TABLE ... https://www.postgresql.org/docs/current/sql-lock.html
  3. [3] SELECT ... FOR UPDATE https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
  4. [4] pg_advisory_lock(..) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
  5. [5] "PostgreSQL rocks, except when it blocks: Understanding locks" https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
  6. [6] Marco Slot https://twitter.com/marcoslot
  7. [7] Egor Rogov https://twitter.com/e_g_Orius
  8. [8] "Locks in PostgreSQL: 1. Relation-level locks" https://postgrespro.com/blog/pgsql/5967999
  9. [9] "Locks in PostgreSQL: 2. Row-level locks" https://postgrespro.com/blog/pgsql/5968005