DBMS Concurrency Scenarios

These scenarios connect transaction theory to the operational behavior an interviewer expects you to diagnose.

Lost update

Two transactions read the same value and both write a derived value. The later write overwrites the earlier update. Prevent it with a row lock, optimistic version column, serializable retry, or an atomic update:

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity >= 1;

Check the affected-row count; do not read, subtract, and write without a concurrency policy.

Deadlock

Transaction A locks row 1 then requests row 2. Transaction B locks row 2 then requests row 1. The database should detect the wait cycle and abort a victim. Applications must retry safe transactions with bounded backoff.

Prevent avoidable deadlocks by acquiring locks in a consistent order, keeping transactions short, indexing predicates, and avoiding user/network calls while holding locks.

Isolation anomalies

Isolation names are not identical across databases; explain the actual implementation and guarantee.

Optimistic concurrency

Use a version column or compare-and-swap update:

UPDATE documents
SET body = :new_body, version = version + 1
WHERE document_id = :id AND version = :expected_version;

If zero rows are affected, another writer won. Return a conflict or retry after merging according to the product policy.

Interview questions

• How would you diagnose a deadlock from database logs and lock tables?
• When is serializable isolation worth its retry cost?
• Why does SELECT ... FOR UPDATE not solve a missing predicate index?
• How do MVCC readers avoid blocking writers, and where does version cleanup happen?
• How would you make a payment decrement safe under retries and duplicate requests?

Cross-references

• Transactions
• Concurrency control
• Deadlocks in Operating Systems
• Idempotency
• ABA and memory reclamation

References

• PostgreSQL transaction isolation
• PostgreSQL explicit locking
• PostgreSQL deadlock detection