Postgres Locks Explorer

• Postgres table-level locking docs
• Postgres row-level locking docs
• django-pg-zero-downtime-migrations
• A more comprehensive list of queries with locks - at some point I may add these into this site

All of the data in this page is derived from these tests, so is true by some definition of true. Notable is this test - given an arbitrary SQL statement, it determines via runtime inspection which locks are taken.

Thanks to kataklinger for this fix!

• Each type of lock conflicts with a subset of lock types.
• Locks acquired within a transaction will only be released at the end of the transaction.
• Locks do not conflict with any locks of any type taken within the same transaction.
• A conflicting lock will either be acquired or blocked and queueing behind another lock that it conflicts with. A somewhat common failure mode might be:

a = ACCESS EXCLUSIVE
b = ACCESS SHARE

Many SELECTs are queued up and
running concurrently:

queue(t=1) = | b       |
             | b[slow] |

A migration enters the queue:

queue(t=2) = | a |         |
             |   | b[slow] |

It blocks new SELECTs from happening
until b[slow] is completed:

queue(t=3) = | b | a |         |
             | b |   | b[slow] |