📘 Transactions and Concurrency

A transaction groups several operations into one all-or-nothing unit, and concurrency control decides what happens when many transactions run at once. This is the most interview-heavy database topic, and the source of the subtlest production bugs: oversold stock, double bookings, lost updates, deadlocks. Runnable examples use SQLite for portability. Behavior that differs by engine is labelled.

Table of Contents

1. ACID
2. Transaction Control
3. Anomalies and Isolation Levels
4. MVCC
5. Locking
6. Deadlocks
7. Optimistic and Pessimistic Concurrency
8. Patterns That Work
9. Write Skew and Serializable
10. Durability and the WAL
11. Choosing an Isolation Level
12. Questions and Answers

1. ACID

Two clarifications interviewers like:

• The "C" in ACID is about application invariants (balance never negative), which the database can only enforce if you declare them as constraints. It is not the "C" in CAP, which means linearizability across replicas.
• Isolation is a spectrum. The default level in most databases is not fully serializable, which is why anomalies exist.

2. Transaction Control

BEGIN;                    start (or START TRANSACTION)
  ... statements ...
SAVEPOINT s1;             a named point inside the transaction
  ... statements ...
ROLLBACK TO s1;           undo back to the savepoint, keep the transaction open
COMMIT;                   make everything durable and visible
ROLLBACK;                 undo everything

• Autocommit: by default each statement is its own transaction. BEGIN turns it off until commit.
• An error inside a PostgreSQL transaction aborts the whole transaction until you roll back (or roll back to a savepoint). MySQL usually lets you continue after a failed statement.
• Keep transactions short. A transaction that waits for user input or a network call holds locks and pins old row versions.

-- runnable
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 100), (2, 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;   -- a mistake
ROLLBACK TO after_debit;                                      -- undo only the mistake
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT id, balance FROM accounts ORDER BY id;                 -- 70 and 80: the transfer is intact

BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
ROLLBACK;                                                     -- nothing happened
SELECT balance FROM accounts WHERE id = 1;                    -- still 70

3. Anomalies and Isolation Levels

3.1 The Anomalies

3.2 The Levels (SQL Standard View)

The standard says nothing about lost update and write skew, and real engines differ from the table, so know the engine.

3.3 What Real Engines Do

Practical consequences:

• Read Committed (PostgreSQL default): each statement sees data committed before the statement began. Two statements in a transaction can see different data.
• Repeatable Read / Snapshot: the whole transaction sees one consistent snapshot, ideal for reports. Writers who collide on the same row: PostgreSQL aborts the later one with a serialization error, MySQL waits on the lock.
• Serializable gives the illusion of serial execution. You pay with aborts and retries, so your application must retry on serialization failures.

4. MVCC

Multi-Version Concurrency Control keeps several versions of a row so that readers do not block writers and writers do not block readers.

How engines implement it:

A snapshot decides which versions a transaction sees: the ones committed before it (or the statement) started.

Consequences worth stating:

• Long-running transactions are dangerous: they hold back cleanup (bloat, growing undo), and idle-in-transaction sessions keep locks. Set idle_in_transaction_session_timeout.
• Reads take no locks, so reporting queries do not block writes. This is why snapshot isolation is so popular.
• Updates are inserts: in PostgreSQL an update rewrites the row and may touch every index (unless HOT), so high-churn tables need vacuum tuning.
• Transaction ID wraparound (PostgreSQL 32-bit transaction ids): vacuum "freezes" old rows to prevent it. Ignoring vacuum eventually forces the database into read-only protection.

5. Locking

Locks protect data that a transaction is changing. MVCC removes most read locking, but write-write conflicts still need locks.

5.1 Lock Modes

An UPDATE takes row locks and holds them until the transaction ends. So another transaction updating the same row waits.

5.2 Explicit Row Locks

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;               lock the row, blocks other writers and lockers
SELECT * FROM accounts WHERE id = 1 FOR SHARE;                block writers, allow other readers
SELECT ... FOR UPDATE NOWAIT;                                 fail immediately if the row is locked
SELECT ... FOR UPDATE SKIP LOCKED;                            skip rows other transactions have locked

FOR UPDATE is the tool for read-then-write logic: read, decide, write, with nobody changing the row in between.

The job queue pattern with SKIP LOCKED lets many workers pull work without blocking each other:

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'PENDING'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;          -- each worker gets a different batch, no waiting
-- process, then:
UPDATE jobs SET status = 'DONE' WHERE id = ANY(:ids);
COMMIT;

Works in PostgreSQL 9.5+, MySQL 8.0+, Oracle, and SQL Server (READPAST). It is the basis of database-backed queues, see Backend async processing.

5.3 DDL and Locks

Schema changes can take strong locks. Adding a column with a default, changing a type, or building a plain index can block writes on a big table. Use online schema change techniques, see Replication, Sharding and Operations. Always set a lock timeout on migrations so a blocked ALTER does not queue up every other query behind it.

6. Deadlocks

A deadlock is a cycle of transactions each waiting for a lock another holds.

• The database keeps a wait-for graph and detects cycles (PostgreSQL checks after deadlock_timeout, InnoDB checks immediately). One transaction is chosen as the victim and rolled back (error 40P01 in PostgreSQL, 1213 in MySQL).
• Prevent by always acquiring locks in a consistent order (for example lower account id first, exactly as in the bank transfer in Concurrency in LLD), by keeping transactions short, by updating fewer rows per transaction, and by indexing the columns used in WHERE so that fewer rows are locked (InnoDB locks every row it scans).
• Handle by catching the deadlock error and retrying the whole transaction. Treat deadlocks as a normal, retryable event.
• Diagnose with the deadlock graph in the server log (log_lock_waits, SHOW ENGINE INNODB STATUS).

7. Optimistic and Pessimistic Concurrency

Optimistic locking with a version column:

Read:    SELECT qty, version FROM stock WHERE sku = 'A';           -> qty 5, version 0
Write:   UPDATE stock SET qty = 10, version = version + 1
         WHERE sku = 'A' AND version = 0;
Check:   rows affected = 1 means success, 0 means someone else changed it: re-read and retry (or tell the user)

ORMs implement this with @Version (JPA), a lock_version column (Rails), or concurrency tokens (Entity Framework).

-- runnable
CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0), version INTEGER NOT NULL DEFAULT 0);
INSERT INTO stock VALUES ('A', 5, 0);

-- Atomic conditional decrement: the check and the write are ONE statement, so no oversell.
-- RETURNING prints a row when it succeeded. No row printed means "not enough stock".
UPDATE stock SET qty = qty - 3 WHERE sku = 'A' AND qty >= 3 RETURNING 'reserved 3, left' AS result, qty;
UPDATE stock SET qty = qty - 3 WHERE sku = 'A' AND qty >= 3 RETURNING 'reserved 3, left' AS result, qty;   -- prints nothing

-- Optimistic locking. Two clients both read version 0.
SELECT qty, version FROM stock WHERE sku = 'A';
-- Client 1 writes first with a version check: succeeds and bumps the version
UPDATE stock SET qty = 10, version = version + 1 WHERE sku = 'A' AND version = 0 RETURNING 'client 1 won, new version' AS result, version;
-- Client 2 still holds version 0: zero rows updated, nothing printed. It must re-read and retry.
UPDATE stock SET qty = 20, version = version + 1 WHERE sku = 'A' AND version = 0 RETURNING 'client 2 won' AS result, version;
SELECT sku, qty, version FROM stock;

8. Patterns That Work

These patterns cover most real concurrency bugs.

8.1 Never Read-Modify-Write in the Application

BAD:   qty = SELECT qty ...; UPDATE ... SET qty = qty_in_app - 1     race: two requests both read 10, both write 9
GOOD:  UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty >= 1     atomic, and the CHECK stays true

Push the arithmetic and the condition into one statement, and use the affected row count as the result.

8.2 Let Constraints Do the Work

A constraint is checked under concurrency by the database, an application if is not.

8.3 Reserve Then Confirm (Holds)

For tickets and inventory, insert a hold row with an expiry, confirm on payment, and let a background job expire stale holds. The ticket booking design and the movie booking code use exactly this.

8.4 Idempotency Table

CREATE TABLE idempotency_keys (key TEXT PRIMARY KEY, request_hash TEXT NOT NULL, response JSONB, created_at TIMESTAMPTZ NOT NULL);

BEGIN;
INSERT INTO idempotency_keys (key, request_hash, created_at) VALUES (:k, :h, now());   -- duplicate key error means a retry
-- do the work, store the response
COMMIT;

8.5 Counters and Sequences

• Increment in SQL (SET n = n + 1), not in the app.
• Hot counters (page views) become a contention point on one row, so shard the counter across N rows and sum, or aggregate asynchronously.
• Sequences and identity columns hand out unique numbers but leave gaps (rolled back transactions, cached ranges). Never require gapless invoice numbers from a sequence alone.

8.6 Advisory or Named Locks

For "only one instance runs this job", use pg_try_advisory_lock(key) or GET_LOCK() in MySQL. The lock is released at session end, which handles crashes.

9. Write Skew and Serializable

Write skew slips through snapshot isolation.

Each transaction updated a different row, so there is no row conflict for snapshot isolation to detect.

Fixes, in order of preference:

1. Serializable isolation (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE). PostgreSQL detects the dangerous read-write dependency cycle and aborts one transaction. The application retries.
2. Lock the rows the decision depends on: SELECT ... FROM on_call WHERE shift = ? FOR UPDATE, so the second transaction waits and then sees the updated state.
3. Materialize the conflict: add a row both transactions must update (a per-shift counter), forcing a write-write conflict.
4. Enforce the rule in the database: a constraint or trigger where possible.

Retry loop for serializable transactions (pseudocode):

for attempt in 1..5:
    try:
        BEGIN ISOLATION LEVEL SERIALIZABLE
        ... work ...
        COMMIT
        break
    catch serialization_failure (40001) or deadlock (40P01):
        ROLLBACK
        sleep(backoff with jitter)

The whole transaction body must be idempotent or side-effect free apart from the database, because it may run more than once. Do not send emails or call payment APIs inside it, use an outbox row instead.

10. Durability and the WAL

To make commit fast and safe, databases use a write-ahead log (WAL).

1. The change is appended to the sequential log and the log is flushed (fsync) to disk.
2. Commit returns.
3. The modified data pages are written to the data files later, lazily, in the background (checkpoints).

After a crash, recovery replays the log to redo committed changes and undo uncommitted ones. Sequential log writes are far cheaper than random page writes, which is why commit is fast.

Details of the log, checkpoints and recovery are in Database Internals.

For transactions across several databases or services, see two-phase commit and sagas in Distributed Systems.

11. Choosing an Isolation Level

Rules of thumb:

• Read Committed plus atomic statements, constraints and FOR UPDATE where needed covers most OLTP code.
• Use Repeatable Read or Snapshot for consistent reads across several queries.
• Use Serializable when correctness across rows matters more than throughput, and you can retry.
• Whatever you pick, test under concurrency with many parallel clients and assert invariants, see Concurrency in LLD for the start-gate technique.

12. Questions and Answers

Q1. Explain ACID with a bank transfer. Atomicity: debit and credit both happen or neither. Consistency: total money and non-negative balances hold. Isolation: a concurrent report never sees money in transit. Durability: once committed, the transfer survives a crash.

Q2. What is MVCC and why is it useful? Keeping multiple row versions so each transaction reads a consistent snapshot without locks. Readers do not block writers, at the cost of storing old versions and cleaning them up (vacuum or undo purge).

Q3. What is a lost update and how do you prevent it? Two read-modify-write cycles where one overwrites the other. Use an atomic UPDATE ... SET x = x + 1, SELECT FOR UPDATE, a version column, or serializable isolation.

Q4. Explain write skew. Two transactions read the same data, then update different rows, jointly violating an invariant, invisible to row-level conflict detection. Prevent with serializable isolation, locking the rows read, or materializing the conflict.

Q5. What is a deadlock and how do you handle it? A cycle of lock waits. The database detects it and aborts a victim. Prevent with consistent lock ordering and short transactions, and always retry on deadlock errors.

Q6. Pessimistic or optimistic locking? Pessimistic when contention is high and the critical section is short. Optimistic when conflicts are rare or when users hold data during think time, using a version check and retry.

Q7. What does SELECT FOR UPDATE SKIP LOCKED do? Locks the selected rows and skips rows already locked by others, so concurrent workers pull different rows from a queue without blocking.

Q8. How do you prevent overselling? One conditional statement: UPDATE stock SET qty = qty - :n WHERE sku = :s AND qty >= :n, and check the affected row count, backed by CHECK (qty >= 0).

Q9. Read Committed vs Repeatable Read in PostgreSQL? Read Committed: each statement sees the latest committed data. Repeatable Read: the whole transaction sees one snapshot taken at its first statement and errors on conflicting concurrent updates.

Q10. Why can long transactions hurt? They hold locks, block vacuum or undo purge causing bloat, and increase the chance of conflicts and deadlocks.

Q11. How does the WAL provide durability and speed? Commits append to a sequential log and flush it, then data files are updated later. After a crash the log is replayed.

Q12. How do you enforce "one active booking per room and time range"? A PostgreSQL exclusion constraint on a range type, or lock the room row with FOR UPDATE and check for overlap in the same transaction.

Next: Database Internals.