Locks, deadlock, and what a long transaction leaves behind
Writers block writers. Two of them waiting for each other is a deadlock, and the fix is lock ordering. And any open transaction stops the whole table from being cleaned up.
14 min · runs against the shop dataset
Two transactions cannot update the same row at once. The second one waits for the first to commit or roll back — and if the first is waiting for a row the second is holding, neither will ever proceed. That is a deadlock, and the database finds it by looking for a cycle in the wait-for graph and aborting one of the participants.
The fix is almost always lock ordering: if every transaction touches rows in the same order — by primary key, say — two of them can never be waiting on each other, because one of them must have got to the lower id first. That is not a clever trick, it is the entire prevention strategy.
labBuild a deadlock, watch the wait-for graph form, then prevent it with lock ordering.→The bloat a long transaction leaves
VACUUM may only reclaim row versions that no snapshot anybody holds could still want. One transaction sitting open — a BEGIN somebody left over lunch, a connection pool that never committed — pins the oldest snapshot, and nothing behind it can be cleaned up however many dead rows accumulate.
This is the single most common cause of a table that will not stop growing. The dead rows are not leaking; they are being retained, on purpose, because something might still need them. The fix is never a bigger disk.
labWatch one idle transaction stop a whole table from being cleaned up.→