Row locks at the page level

In PostgreSQL, every tuple starts with 23-byte header, and the first eight bytes are two transaction IDs. t_xmin for the transaction that created the row and t_xmax for the one that deleted or updated it. That is the visibility story covered in PostgreSQL MVCC, Byte by Byte. For now we have discussed t_xmax acting as the delete marker. t_xmax has a second job. When you run SELECT ... FOR UPDATE or an insert checks a foreign key, PostgreSQL has nowhere else to record the row lock. The shared memory lock table is limited by max_locks_per_transaction. Locking a million rows would exceed its capacity. PostgreSQL works around this by storing the locking transaction ID in t_xmax and marking the row as locked with flags in t_infomask, while readers can still see it, so every row lock in PostgreSQL ends up as a write to the page. Setup The setup is one parent table in the usual shape, plus a child table with a foreign key, since foreign key checks lock parent rows. Everything below was…

Commenting on Bubbles has been removed. Why? If you want to talk about this post, write to the author, use the comments on their blog if it has them, or reply with a "RE:" post on your own blog. Suggestions? feedback@bubbles.town