Top 4 Mvcc Interview Questions
Browse 4 real-world technical and behavioral interview questions about Mvcc. Review scenarios, edge cases, and architectural best practices.
Two transactions each check a rule, then both commit changes that break it, and neither overwrote the other's row. What happened?
This is write skew. Both transactions read the same snapshot, decide independently, and write disjoint rows, so there is no write-write conflict for the engine to catch. The invariant spanned rows it never saw as related, so either make the conflict physical or use true serialisability.
Walk me through the transaction isolation levels and which anomalies each one permits.
Each level is defined by the anomalies it allows: READ COMMITTED permits non-repeatable reads and phantoms, PostgreSQL's REPEATABLE READ is snapshot isolation so it prevents both yet still permits write skew, and only SERIALIZABLE rules out all four.
Monitoring says the age of the oldest transaction ID on this PostgreSQL database keeps climbing. What is it measuring, and what happens if it is ignored?
PostgreSQL compares 32-bit transaction IDs, so old rows must be frozen before the counter laps them. A rising age means vacuum is not freezing fast enough, and if it keeps rising the server eventually refuses to assign new transaction IDs and stops accepting writes.
In PostgreSQL, what does an UPDATE do to a row under MVCC, and why does a long-running transaction make that expensive?
An UPDATE writes a new row version and leaves the old one visible to older snapshots; vacuum reclaims it only once no snapshot can see it. A long transaction pins that horizon, so dead tuples accumulate as bloat and freezing stalls, which is what leads to transaction-id wraparound.