Transactions & Isolation
Transaction and concurrency depth beyond ACID — isolation levels and the anomalies each permits, optimistic vs pessimistic locking, deadlocks, MVCC, 2PC vs saga, and data-layer idempotency.
8 questions
SeniorDesignVery commonTwo microservices must both apply a change or both undo it: a money transfer debits an account service and credits a ledger service, and a half-completed transfer is unacceptable. Compare the distributed-commit protocol 2PC (two-phase commit) with the compensation-based Saga pattern for this case — how each achieves all-or-nothing, and what each costs in availability and coupling — then pick one for the money transfer and justify the choice.
Two microservices must both apply a change or both undo it: a money transfer debits an account service and credits a ledger service, and a half-completed transfer is unacceptable. Compare the distributed-commit protocol 2PC (two-phase commit) with the compensation-based Saga pattern for this case — how each achieves all-or-nothing, and what each costs in availability and coupling — then pick one for the money transfer and justify the choice.
2PC gives strong atomicity: a coordinator commits only if both services agree — but it holds locks across the network and blocks if the coordinator dies. Saga uses local transactions with compensations; it stays available but is only eventually consistent. Prefer Saga, with idempotent steps and reconciliation.
Common mistakes
- ✗Calling
2PCnon-blocking or highly available - ✗Thinking
Sagagives immediate strong consistency without compensation - ✗Ignoring idempotency and reconciliation when choosing
Saga
Follow-up questions
- →Why does the two-phase commit protocol
2PCblock when the coordinator fails? - →How does a compensating transaction differ from a plain rollback?
SeniorDesignVery commonA payment flow debits the sender in service A, then calls service B to credit the receiver. The debit in A succeeds, but the credit in B fails or times out, so the money is gone from A yet never arrives in B. There is no shared database and no distributed transaction spanning the two services. Which mechanisms guarantee the money is never lost and the two sides eventually agree — without double-crediting if the failed call is later retried? Describe the approach you would specify.
A payment flow debits the sender in service A, then calls service B to credit the receiver. The debit in A succeeds, but the credit in B fails or times out, so the money is gone from A yet never arrives in B. There is no shared database and no distributed transaction spanning the two services. Which mechanisms guarantee the money is never lost and the two sides eventually agree — without double-crediting if the failed call is later retried? Describe the approach you would specify.
Don't rely on one cross-service commit. Persist the debit and a credit intent atomically in A (transactional outbox), then deliver the message to B with at-least-once retries. Make B's credit idempotent via a unique op id so retries don't double-credit. On failure, a compensating refund in A; reconciliation catches stragglers.
Common mistakes
- ✗Assuming a distributed transaction across services is available or reliable
- ✗Retrying a non-idempotent credit and double-crediting the receiver
- ✗Skipping compensation, leaving money debited but never credited
Follow-up questions
- →How does a transactional outbox avoid the dual-write problem?
- →When would you choose reconciliation over synchronous compensation?
JuniorTheoryCommonWhich isolation levels does the SQL standard define, and which is PostgreSQL's default?
Which isolation levels does the SQL standard define, and which is PostgreSQL's default?
The SQL standard defines four isolation levels, weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each higher level forbids more concurrency anomalies but costs throughput. PostgreSQL defaults to READ COMMITTED and, unusually, never permits dirty reads even at its lowest level.
Common mistakes
- ✗Naming only two levels or inventing READ/WRITE modes
- ✗Thinking higher isolation raises throughput rather than costing it
- ✗Assuming PostgreSQL's default is SERIALIZABLE
Follow-up questions
- →What anomaly does READ COMMITTED still allow?
- →Why does PostgreSQL never permit dirty reads?
JuniorTheoryCommonWhat is a lock, how do shared and exclusive locks differ, and what is a deadlock?
What is a lock, how do shared and exclusive locks differ, and what is a deadlock?
A lock is a marker a transaction places on a row or table to control concurrent access. A shared (read) lock lets others read but not write; an exclusive (write) lock blocks other reads and writes. A deadlock is when two transactions each hold a lock the other needs and both wait forever — the DBMS detects the cycle and aborts one of them.
Common mistakes
- ✗Swapping shared (read) and exclusive (write) semantics
- ✗Thinking the client, not the DBMS, resolves a deadlock
- ✗Believing a deadlock is just a slow query
Follow-up questions
- →How does the DBMS choose which transaction to abort in a deadlock?
- →How does consistent lock ordering prevent deadlocks?
MiddleTheoryCommonWhich anomaly does the isolation level READ COMMITTED still permit, and which one does REPEATABLE READ?
Which anomaly does the isolation level READ COMMITTED still permit, and which one does REPEATABLE READ?
READ COMMITTED prevents dirty reads but still permits non-repeatable reads and phantoms — a re-read can return new committed values or rows. REPEATABLE READ also stops non-repeatable reads; by the SQL standard it still permits phantoms, though PostgreSQL blocks those too. Only SERIALIZABLE forbids every anomaly.
Common mistakes
- ✗Thinking
READ COMMITTEDalready blocks non-repeatable reads - ✗Believing higher isolation permits more anomalies, not fewer
- ✗Forgetting the standard still allows phantoms at
REPEATABLE READ
Follow-up questions
- →How does PostgreSQL's
REPEATABLE READdiffer from the SQL standard? - →What extra cost does
SERIALIZABLEimpose to remove phantoms?
MiddleTheoryCommonHow do you make a data-layer write idempotent, and what does a unique business key buy you?
How do you make a data-layer write idempotent, and what does a unique business key buy you?
An idempotent write leaves the same final state however many retries repeat it — never a duplicate. Define a unique business key (an order number or client request id) and back it with a unique constraint. Then INSERT ... ON CONFLICT DO NOTHING or an upsert makes the retry a no-op, so the database rejects duplicates under concurrency.
Common mistakes
- ✗Counting retries in the app instead of enforcing a DB constraint
- ✗Using a timestamp or random id that makes every attempt unique
- ✗Thinking a unique key fails to stop concurrent duplicate inserts
Follow-up questions
- →How does an idempotency key at the API layer relate to this?
- →What HTTP verb is naturally idempotent for the same reason?
MiddleTheoryCommonWhat is MVCC, and how does it let readers avoid blocking writers?
What is MVCC, and how does it let readers avoid blocking writers?
MVCC (multiversion concurrency control) keeps several versions of each row instead of overwriting in place. A reader sees a snapshot as of its transaction's start, reading an old version while a writer creates a new one. So readers never block writers and vice versa; only two writers on one row contend.
Common mistakes
- ✗Thinking MVCC keeps only one version and overwrites in place
- ✗Believing readers still block writers under MVCC
- ✗Confusing MVCC with a disk backup or logging format
Follow-up questions
- →Which anomaly can a snapshot still permit at
READ COMMITTED? - →How are obsolete row versions cleaned up afterwards (vacuum)?
MiddleTheoryCommonHow do optimistic and pessimistic locking differ, and how does a version column implement the optimistic kind?
How do optimistic and pessimistic locking differ, and how does a version column implement the optimistic kind?
Pessimistic locking takes a lock up front, so conflicts block; it fits high-contention writes. Optimistic locking takes no lock: you read a version column, then update with WHERE id = ? AND version = ? and increment it. If zero rows match, someone changed the row first, so you retry. It suits low-contention cases.
Common mistakes
- ✗Swapping which strategy holds a lock up front
- ✗Thinking the version column sorts rows rather than detecting conflicts
- ✗Using optimistic locking for the highest-contention writes
Follow-up questions
- →Why does optimistic locking scale better under low contention?
- →How does a lost update occur without any version check?
