Row locks at the page level

boringSQL | Supercharge your SQL & PostgreSQL powers · 5 days ago · discuss

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...