Transactions & Concurrency Control

Choose a concurrency scheme

Concurrent transactions write to the same data: locking, optimistic, commutative operations, partitioning or deterministic execution? Measure the conflict rate, shorten transactions, then follow five questions to one scheme.

Design a commit

Building or choosing the commit path of a store: name the one atomic write it rests on, derive the commit-rate ceiling, and decide what an abort and a retry will cost.

Choose a distributed commit

One operation writes to several systems: make it all-or-nothing, or let each system commit on its own and handle failures another way? Six questions lead to one of six designs, each with its costs and checks.

Conflict grows with the square of concurrency (uniform versus skewed keys)

L concurrent transactions touching k random keys out of N: conflicting pairs against L²k²/2N, uniform and Zipf-skewed.

Phantoms and gap locks depend on the index

MySQL/InnoDB: SELECT ... FOR UPDATE on a price range, with and without an index on price; which inserts wait, and how many locks are held.

Reproduce a deadlock, then remove it by ordering locks

Two PostgreSQL sessions move money between the same two wallets in opposite orders, 20 times; then with a fixed lock order; then with deadlock_timeout = 100ms.

One old snapshot pins the version space

Two minutes of random updates on a million-row PostgreSQL table, without and with an old Repeatable Read snapshot open: size and dead tuples.

Pessimistic versus optimistic crossover

16 workers doing read-modify-write on a PostgreSQL counter table of K rows, with SELECT FOR UPDATE and with a version column: throughput and goodput from K = 1,000,000 to 1.

Write skew under snapshot isolation, and its fix

Two on-call agents each check that two are on shift and each go off shift: REPEATABLE READ against SERIALIZABLE; then 50 clients and the retry rate.

The idempotency key: the unknown outcome made harmless

A top-up whose acknowledgment is lost, retried naively and with an idempotency key in SQLite; then a replay after the key has expired.

Savepoint cliff in Postgres (more than 64 subtransactions)

A PostgreSQL transaction holding 0 to 200 subtransactions open while pgbench reads the same rows: read throughput across the 64-subtransaction cache.

Contended compare-and-swap: n²T/2 and the value of jitter

n writers each need one successful conditional write on a shared pointer: total time and wasted attempts with immediate retry and with jittered backoff.

Availability of unanimity: 0.999ⁿ versus a majority

Atomic commit needs every participant; consensus needs a majority: a^n against the binomial majority, then 10 million simulated rounds with and without rack failures.

Interview questions

Short questions on transactions and concurrency control, such as conflict arithmetic, locking, MVCC, optimistic schemes, commit mechanics and distributed transactions, each with an answer you could give aloud and a link to the full explanation.

Scenarios

Longer problems on transactions and concurrency control, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.