Concurrency Control

Concurrency control is the part of a database that decides how transactions running at the same time may interleave, so that the result is still correct. Without it, two people booking the last seat both succeed, two counter increments become one, and reports read half-finished updates. In the previous lesson you learned the goal: serializable, recoverable schedules. This lesson covers the mechanisms: locks and two-phase locking, deadlock handling, timestamp ordering, optimistic concurrency control and multi-version concurrency control (MVCC). It ends with the part interviewers probe most in practice: SQL isolation levels, which anomalies each one allows, how PostgreSQL and MySQL actually behave, and how to use SELECT ... FOR UPDATE correctly.

The problems concurrency control prevents

Each anomaly below is shown as a timeline. Time flows downward; the left column is transaction T1, the right is T2.

Lost update

Two transactions read the same value, both compute a new value from it, and both write. The second write silently overwrites the first.

  T1 (add 100)                 T2 (add 50)          balance
  ---------------------------  -------------------  -------
  read balance = 1000                                1000
                               read balance = 1000   1000
  write balance = 1100                               1100
                               write balance = 1050  1050
  commit                                             1050
                               commit                1050

The correct result is 1150. T1's deposit is lost. This is the most common real-world concurrency bug, and it happens whenever application code does "read, compute in the app, write back" without protection.

Dirty read

T2 reads a value that T1 has written but not committed. T1 then rolls back, so T2 acted on data that never officially existed.

  T1                            T2
  ----------------------------  ----------------------------
  write seats_left = 0
                                read seats_left = 0
                                show "Sold out" to customer
  rollback (seats_left = 1)

Non-repeatable read

T1 reads the same row twice and gets different values, because T2 updated and committed in between.

  T1                            T2
  ----------------------------  ----------------------------
  read price = 500
                                update price = 600
                                commit
  read price = 600   (changed!)

Phantom read

T1 runs the same query twice and gets a different set of rows, because T2 inserted (or deleted) rows that match the condition. The individual rows T1 saw did not change; new "phantom" rows appeared.

  T1                                 T2
  ---------------------------------  -----------------------------
  SELECT COUNT(*) FROM bookings
   WHERE room = 12 AND day = 'Mon'
   -> 0
                                     INSERT booking (room 12, Mon)
                                     commit
  SELECT COUNT(*) ... same query
   -> 1   (a phantom row)

A non-repeatable read is about an existing row changing; a phantom is about rows entering or leaving a result set. Locking existing rows prevents the first but not the second, because you cannot lock a row that does not exist yet.

Write skew

Two transactions each read an overlapping set of rows, each checks a rule, and each updates a different row. Neither overwrites the other, yet together they break the rule.

The classic example: a hospital requires at least one doctor on call. Alice and Bob are both on call, and both feel unwell at the same moment.

  T1 (Alice goes off call)          T2 (Bob goes off call)
  --------------------------------  --------------------------------
  SELECT COUNT(*) FROM doctors
   WHERE on_call -> 2
                                    SELECT COUNT(*) FROM doctors
                                     WHERE on_call -> 2
  2 >= 2, so it is safe:
  UPDATE doctors SET on_call=false
   WHERE name='Alice'
                                    2 >= 2, so it is safe:
                                    UPDATE doctors SET on_call=false
                                     WHERE name='Bob'
  commit                            commit
  -> nobody is on call

Write skew is important because it survives snapshot isolation (explained below), which is what PostgreSQL's Repeatable Read gives you. Only true serializability prevents it.

Locks

A lock is a marker a transaction places on a data item to say "I am using this; others must wait or share". The lock manager grants and queues lock requests, usually with a hash table from item to the list of granted and waiting requests.

Shared and exclusive locks

• A shared lock (S), also called a read lock, lets the holder read the item. Many transactions can hold S on the same item at once.
• An exclusive lock (X), also called a write lock, lets the holder read and write. Only one transaction can hold X, and nobody else can hold S at the same time.

A request that is not compatible with locks already held by other transactions waits. A transaction holding S that later needs X performs a lock upgrade.

Lock granularity

Locks can be taken at different sizes: database, table, page, or row. This is granularity.

             Database
                |
       +--------+--------+
     Table A           Table B
       |
   +---+---+
 Page 1  Page 2
   |
 +-+-+
 r1  r2   (rows)

Fine locks give more concurrency but cost memory and lock-manager time. Some databases perform lock escalation: when a transaction holds too many row locks, they are replaced by one table lock (SQL Server does this; PostgreSQL and InnoDB do not escalate row locks to table locks).

Intention locks and multiple-granularity locking

Suppose T1 holds X on one row of orders, and T2 wants an S lock on the whole orders table. To decide, the lock manager would have to scan every row lock. Intention locks solve this. Before locking something fine-grained, a transaction places an intention lock on every ancestor:

• IS (intention shared): "I will take S locks somewhere below."
• IX (intention exclusive): "I will take X locks somewhere below."
• SIX (shared + intention exclusive): "I read the whole node (S) and will write some parts below (IX)." Typical for "scan the table, update a few rows".

So T1 takes IX on the database, IX on orders, then X on the row. When T2 asks for S on orders, the lock manager sees an IX already held there and makes T2 wait, without looking at any rows.

The compatibility matrix (Yes = can be granted together):

The protocol rules: lock from the root down, release from the leaves up; to get S or IS on a node you need IS or IX on its parent; to get X, IX or SIX you need IX or SIX on its parent. MySQL InnoDB uses exactly this scheme: table-level IS and IX locks plus row-level S and X locks.

Two-phase locking (2PL)

Locks alone do not guarantee serializability. If T1 locks A, reads it, unlocks it, then locks B, another transaction can slip between and see an inconsistent mix. Two-phase locking adds one rule about when locks may be released.

Basic 2PL

Each transaction has two phases:

1. Growing phase: it may acquire locks but not release any.
2. Shrinking phase: it may release locks but not acquire any.

The moment it acquires its last lock (the end of the growing phase) is its lock point.

 locks held
     ^
     |          lock point
     |             *
     |           /   \
     |         /       \
     |       /           \
     |     /               \
     +---------------------------> time
       growing     shrinking

Why 2PL guarantees conflict serializability

Order the transactions by their lock points. Claim: this order is an equivalent serial schedule. Proof sketch:

1. Suppose the precedence graph has an edge Ti → Tj. Then Ti and Tj performed conflicting operations on some item Q, Ti first.
2. Conflicting operations need incompatible locks. So Tj could only lock Q after Ti released it.
3. Ti released a lock, so Ti was already in its shrinking phase: Ti's lock point came before that release.
4. Tj acquired a lock after that release, so Tj's lock point comes after it.
5. Hence every edge Ti → Tj means lockpoint(Ti) is earlier than lockpoint(Tj). A cycle T1 → T2 → ... → T1 would need lockpoint(T1) earlier than itself, which is impossible. The graph is acyclic, so the schedule is conflict serializable.

The problems with basic 2PL

Basic 2PL guarantees serializability but has two weaknesses:

• Cascading aborts. T1 can release its X lock on A during its shrinking phase, before it commits. T2 then reads A (a dirty read). If T1 aborts, T2 must abort too.
• Deadlocks. Transactions wait for each other's locks. 2PL does nothing to stop this.

Strict and rigorous 2PL

Strict 2PL is what most lock-based databases implement, because holding X locks until commit means nobody can read or overwrite uncommitted data, so undo is simple and aborts never cascade. Rigorous 2PL is even simpler to reason about: the equivalent serial order is the commit order.

Worked example: 2PL and the lost update

Run the lost-update timeline under strict 2PL. Both transactions read before they write, so they take S first and upgrade to X:

  T1                              T2
  ------------------------------  ------------------------------
  S-lock(balance)  granted
  read balance = 1000
                                  S-lock(balance)  granted (S+S ok)
                                  read balance = 1000
  X-lock(balance)  WAIT (T2 has S)
                                  X-lock(balance)  WAIT (T1 has S)
  ----------------- deadlock: each waits for the other -----------

The lost update is prevented, but by a deadlock: the database aborts one transaction (say T2), T1 proceeds to 1100 and commits, and T2 retries and produces 1150. This is called an upgrade deadlock, and it is the reason SELECT ... FOR UPDATE exists: taking X at read time means T2 waits at its first read instead of deadlocking later.

Deadlocks in databases

A deadlock is a set of transactions each waiting for a lock held by another in the set, so none can proceed. Databases handle deadlocks in three ways: detect and break them, prevent them with a rule, or use timeouts.

Detection with a wait-for graph

The wait-for graph has a node per active transaction and an edge Ti → Tj when Ti is waiting for a lock that Tj holds. A cycle means a deadlock.

  T1 holds A, wants B          +----+  waits for  +----+
  T2 holds B, wants C          | T1 | ----------> | T2 |
  T3 holds C, wants A          +----+             +----+
                                 ^                  |
                                 |   waits for      | waits for
                                 |                  v
                                 |               +----+
                                 +-------------- | T3 |
                                                 +----+

The database periodically (or on every wait) checks for cycles, picks a victim and aborts it. Victim choice usually favours the transaction that has done the least work or holds the fewest locks, and avoids repeatedly picking the same transaction (starvation).

• MySQL InnoDB detects deadlocks immediately when a lock wait would create a cycle and rolls back the transaction it considers smallest (measured by the number of rows it inserted, updated or deleted), returning error 1213 "Deadlock found when trying to get lock". It also has innodb_lock_wait_timeout (default 50 seconds) for plain waits.
• PostgreSQL waits deadlock_timeout (default 1 second) before running the check, because most lock waits end on their own and the check is expensive. The victim receives ERROR: deadlock detected (SQLSTATE 40P01).

Prevention with timestamps: wait-die and wound-wait

Prevention schemes give each transaction a timestamp when it starts; a smaller timestamp means older. When transaction Ti requests a lock held by Tj, a rule decides who waits and who is aborted. An aborted transaction restarts with its original timestamp, so it eventually becomes the oldest and cannot starve.

Wait-die (non-preemptive, "old waits for young, young dies"):

• If Ti is older than Tj: Ti waits.
• If Ti is younger than Tj: Ti dies (aborts itself and restarts later).

Wound-wait (preemptive, "old wounds young, young waits"):

• If Ti is older than Tj: Ti wounds Tj (Tj is aborted and releases its locks).
• If Ti is younger than Tj: Ti waits.

In both schemes, waits only ever go in one direction of age (wait-die: old waits for young; wound-wait: young waits for old), so a cycle is impossible.

Worked example

Three transactions with timestamps T1 = 5 (oldest), T2 = 10, T3 = 15 (youngest).

Now the deadlock scenario: T1 holds A, T2 holds B. T2 requests A, then T1 requests B.

• Wait-die: T2 (10) requests A held by T1 (5). T2 is younger, so T2 dies and releases B. T1 then gets B. No deadlock.
• Wound-wait: T2 (10) requests A held by T1 (5). T2 is younger, so T2 waits. T1 (5) requests B held by T2. T1 is older, so it wounds T2: T2 aborts and releases B, and T1 gets B. No deadlock.

Timeouts

The simplest scheme: if a lock wait exceeds a limit, abort the waiter. It is cheap but imprecise: a too-short timeout aborts healthy transactions, a too-long one leaves real deadlocks blocking for ages. Databases use timeouts as a safety net beside detection.

Timestamp-ordering protocols

Timestamp ordering (TO) avoids locks entirely. Each transaction Ti gets a timestamp TS(Ti) at start, and the database forces the schedule to be equivalent to the serial schedule in timestamp order. Each item Q stores:

• RTS(Q): the largest timestamp of any transaction that has read Q.
• WTS(Q): the largest timestamp of any transaction that has written Q.

Rules for transaction Ti:

Read(Q)

• If TS(Ti) < WTS(Q): a younger transaction has already overwritten Q, so Ti would read a value "from its future". Reject: roll back Ti and restart with a new timestamp.
• Otherwise: allow, and set RTS(Q) = max(RTS(Q), TS(Ti)).

Write(Q)

• If TS(Ti) < RTS(Q): a younger transaction has already read the old value; Ti's write comes too late. Reject.
• If TS(Ti) < WTS(Q): a younger transaction has already written Q. Reject (basic TO).
• Otherwise: allow, and set WTS(Q) = TS(Ti).

Worked example: basic TO

TS(T1) = 10, TS(T2) = 20. All RTS and WTS start at 0.

At step 5, T1 wants to write A, but T2 (which is "later" in timestamp order) already read A. Allowing the write would mean T2 should have seen it. T1 restarts with a new, larger timestamp.

Thomas write rule

Consider this schedule with TS(T1) = 10, TS(T2) = 20:

R1(Q)  W2(Q)  W1(Q)

At W1(Q): TS(T1) = 10 ≥ RTS(Q) = 10, but 10 < WTS(Q) = 20. Basic TO rejects T1. Yet think about the timestamp-order serial schedule T1, T2: T1's write of Q would be overwritten by T2's write anyway, and nobody read T1's value in between. The write is obsolete.

The Thomas write rule changes one case: if TS(Ti) < WTS(Q) (and TS(Ti) ≥ RTS(Q)), ignore the write and let Ti continue instead of rolling it back. The resulting schedules are view serializable but not necessarily conflict serializable (the schedule above has a cycle in its precedence graph, but it is view equivalent to T1, T2, as shown in the previous lesson's blind-write example).

Properties of timestamp ordering

• Deadlock-free: nobody waits, so no wait cycles.
• Starvation possible: a long transaction can be restarted repeatedly.
• Not recoverable by default: a transaction can read data from one that has not committed. Fixing this needs extra rules such as delaying reads until the writer commits, or buffering writes until commit.
• High abort rates under contention, which is why pure TO is rare in production; its ideas live on in MVCC and in distributed databases that order transactions by timestamps.

Optimistic concurrency control (OCC)

Locking is pessimistic: it assumes conflicts are likely and prevents them up front. Optimistic concurrency control, also called validation-based concurrency control, assumes conflicts are rare. Each transaction runs in three phases:

1. Read phase: execute, reading from the database and writing only to a private workspace. Record the read set and write set.
2. Validation phase: check whether this transaction conflicts with any that committed while it ran.
3. Write phase: if validation passes, copy the private writes into the database; otherwise abort and restart.

The textbook validation test gives each transaction Tj a timestamp at validation time. For every earlier-validated Ti, one of these must hold:

1. Ti finished its write phase before Tj started its read phase (no overlap at all), or
2. Ti finished writing before Tj started validating, and Ti's write set does not intersect Tj's read set, or
3. Ti finished its read phase before Tj finished its read phase, and Ti's write set does not intersect Tj's read set or write set.

OCC shines when conflicts are rare and transactions mostly read. Under heavy contention it wastes work by repeatedly aborting.

Application-level optimistic locking

The same idea is widely used in application code with a version column. You read the row with its version, and the update only succeeds if nobody changed the version in between. This runs as shown in SQLite, PostgreSQL and MySQL:

CREATE TABLE products (
  id      INTEGER PRIMARY KEY,
  name    TEXT    NOT NULL,
  stock   INTEGER NOT NULL CHECK (stock >= 0),
  version INTEGER NOT NULL DEFAULT 1
);
INSERT INTO products VALUES (7, 'Keyboard', 10, 1);

-- 1. Read: remember stock = 10, version = 1
SELECT stock, version FROM products WHERE id = 7;

-- 2. Write only if the version is still 1
UPDATE products
   SET stock = 9, version = version + 1
 WHERE id = 7 AND version = 1;
-- rows affected = 1: success.
-- If another session got there first, rows affected = 0:
-- re-read and retry, or report a conflict to the user.

ORMs build this in: JPA/Hibernate's @Version, Django and Rails offer similar patterns (Rails calls it lock_version). HTTP APIs expose the same idea with ETag and If-Match headers.

Multi-version concurrency control (MVCC)

With plain locking, a reader must wait for a writer holding X, and a writer must wait for readers holding S. MVCC removes most of this waiting by keeping several versions of each row:

• A write creates a new version instead of overwriting.
• Each transaction (or each statement) reads from a snapshot: the set of versions that were committed at the moment the snapshot was taken.
• So readers never block writers and writers never block readers. Writers still block other writers of the same row.

  Row id=7 version chain (newest first)

  +---------------+     +---------------+     +---------------+
  | stock = 8     | --> | stock = 9     | --> | stock = 10    |
  | by tx 140     |     | by tx 120     |     | by tx 100     |
  | (uncommitted) |     | (committed)   |     | (committed)   |
  +---------------+     +---------------+     +---------------+

  A snapshot taken after tx 120 committed sees stock=9.
  A snapshot taken before tx 120 committed sees stock=10.
  Nobody but tx 140 sees stock=8 until it commits.

How PostgreSQL stores versions

• Every row version (a tuple) in the table's heap file carries hidden fields xmin (the ID of the transaction that created it) and xmax (the ID of the transaction that deleted or replaced it, or 0).
• An UPDATE does not modify the tuple in place. It writes a new tuple with xmin = the updating transaction, and sets xmax on the old one. A DELETE just sets xmax.
• A snapshot records which transaction IDs were committed when it was taken. A tuple is visible if its xmin committed before the snapshot and its xmax is empty, aborted, or not yet committed as of the snapshot.
• Old tuples that no running transaction can see any more are dead tuples. VACUUM (usually the background autovacuum) reclaims their space. Long-running transactions keep old snapshots alive and block this cleanup, causing table bloat.

You can see these hidden columns yourself in PostgreSQL:

-- PostgreSQL only
SELECT xmin, xmax, * FROM products WHERE id = 7;

How MySQL InnoDB stores versions

• InnoDB updates the row in place in the clustered index (the primary-key B+ tree).
• Before changing it, it writes the old values to the undo log (in rollback segments). Each row has hidden fields DB_TRX_ID (last transaction that modified it) and DB_ROLL_PTR (pointer to the undo record holding the previous version).
• A reader whose read view should not see the latest version follows the roll pointer chain through the undo log, rebuilding older versions until it finds a visible one.
• A background purge thread deletes undo records once no read view needs them. Long transactions make the undo history grow (visible as a growing "history list length").

Snapshot isolation and its limit

Running every transaction against one snapshot taken at its start is called snapshot isolation (SI). Writers use a first-committer-wins (or first-updater-wins) rule: if two concurrent transactions update the same row, the second one is aborted (PostgreSQL) or waits and then sees the newer version (InnoDB's "current read").

SI prevents dirty reads, non-repeatable reads, phantoms (for snapshot reads) and lost updates on the same row. It does not prevent write skew, because in the doctors example the two transactions update different rows, so there is no write-write conflict to detect. PostgreSQL's Serializable level adds Serializable Snapshot Isolation (SSI): it tracks read-write dependencies between concurrent transactions and aborts one when it detects a dangerous pattern that could produce a non-serializable result.

SQL isolation levels

The SQL standard defines four isolation levels by which anomalies they must prevent. A lower level allows more anomalies but usually blocks less.

The standard table is incomplete: it does not mention lost updates or write skew, and real databases often prevent more than the minimum. Here is how the two databases you are most likely to be asked about behave.

PostgreSQL

MySQL InnoDB

Setting the level:

-- PostgreSQL: for one transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- or, as the first statement inside the transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL: for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT ... FOR UPDATE and friends

A locking read reads rows and locks them in the same step, so the read-then-write pattern becomes safe at the default isolation level. These clauses work in PostgreSQL and MySQL 8.0+ (SQLite has no row locks and does not support them; it locks the whole database file for writes).

PostgreSQL also offers weaker FOR NO KEY UPDATE and FOR KEY SHARE modes, used internally for foreign-key checks so that updating non-key columns does not block child inserts.

Fixing the lost update

-- PostgreSQL or MySQL
BEGIN;
SELECT stock FROM products WHERE id = 7 FOR UPDATE;   -- locks row 7
-- application checks stock > 0
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;                                              -- releases lock

A second session running the same code waits at the SELECT ... FOR UPDATE until the first commits, then reads the new stock. Often you do not even need the SELECT: a single conditional update is atomic.

-- Works in every database, including SQLite
UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock > 0;
-- rows affected = 0 means out of stock

A job queue with SKIP LOCKED

-- PostgreSQL 9.5+ or MySQL 8.0+
BEGIN;
SELECT id, payload FROM jobs
 WHERE status = 'pending'
 ORDER BY id
 LIMIT 1
 FOR UPDATE SKIP LOCKED;
-- process the job, then:
UPDATE jobs SET status = 'done' WHERE id = :claimed_id;
COMMIT;

Each worker grabs the oldest job that no other worker has locked, so many workers can pull from one table without blocking each other or processing a job twice.

Fixing write skew

Locking the rows you read removes write skew, because the two transactions now conflict:

-- PostgreSQL or MySQL
BEGIN;
SELECT name FROM doctors WHERE on_call FOR UPDATE;   -- locks both rows
-- if the count is at least 2:
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

Bob's transaction blocks on the same SELECT, then re-reads and sees only one doctor on call. Alternatives are running at SERIALIZABLE and retrying, or turning the rule into a constraint. When the rule is about rows that do not exist yet (double booking), there is nothing to lock; use a unique or exclusion constraint instead, as shown in database design practice.

Practical guidance

• Prefer atomic statements. UPDATE t SET x = x + 1 or a conditional UPDATE ... WHERE stock > 0 avoids read-modify-write races at any isolation level.
• Use constraints for invariants. Unique indexes, CHECK, foreign keys and (in PostgreSQL) exclusion constraints are checked by the database under concurrency, which is far more reliable than an application-side "check then insert".
• Choose the lock strategy by contention. High contention on few rows: pessimistic (FOR UPDATE). Low contention, long user think time (edit forms): optimistic (version column).
• Lock in a consistent order. If every transaction locks accounts in ascending id order, two transfers cannot deadlock each other.
• Keep transactions short and never wait for a user or a slow external call while holding locks or an old snapshot.
• Retry on serialization failures and deadlocks, with a small retry limit.
• Know your default: Read Committed in PostgreSQL (and in Oracle and SQL Server), Repeatable Read in MySQL InnoDB.

Interview questions

Q1. What is the difference between a non-repeatable read and a phantom read?

A non-repeatable read is when a transaction reads the same row twice and sees different values because another transaction updated it and committed in between. A phantom read is when a transaction runs the same query twice and gets a different set of rows because another transaction inserted or deleted matching rows. Row locks prevent the first; preventing the second needs predicate, range or gap locks, or a snapshot.

Q2. What is write skew? Which isolation level prevents it?

Write skew happens when two concurrent transactions read overlapping data, each make a decision based on it, and update different rows, jointly violating a rule such as "at least one doctor on call". Snapshot isolation (PostgreSQL Repeatable Read) does not prevent it because the writes do not conflict. Serializable isolation does, as does explicitly locking the rows that were read with SELECT ... FOR UPDATE.

Q3. Explain shared and exclusive locks.

A shared lock lets a transaction read an item and is compatible with other shared locks, so many readers can proceed together. An exclusive lock lets a transaction write and is incompatible with any other lock on the item. A transaction requesting an incompatible lock waits until the holder releases it.

Q4. Why do we need intention locks?

They make multiple-granularity locking efficient. Before locking a row, a transaction places IS or IX on the table, so another transaction wanting a table-wide S or X lock can see the conflict at table level instead of scanning all row locks. IX is compatible with IX because two writers can work on different rows.

Q5. What is two-phase locking and why does it ensure serializability?

Each transaction acquires locks in a growing phase and releases them in a shrinking phase, never acquiring after releasing. If Ti precedes Tj on a conflict, Tj could only take the lock after Ti released it, so Ti's lock point comes before Tj's. Every precedence edge therefore points forward in lock-point order, which rules out cycles.

Q6. What is the difference between strict and rigorous 2PL?

Strict 2PL holds exclusive locks until the transaction commits or aborts, which prevents dirty reads and cascading aborts. Rigorous 2PL holds all locks, shared and exclusive, until the end, which additionally makes the serialization order equal to the commit order. Both can still deadlock.

Q7. How does a database detect deadlocks?

It maintains a wait-for graph where an edge Ti → Tj means Ti waits for a lock Tj holds, and periodically or on each wait it looks for a cycle. If one exists, it aborts a victim, usually the transaction with the least work done. InnoDB checks immediately; PostgreSQL checks after waiting deadlock_timeout, one second by default.

Q8. Compare wait-die and wound-wait.

Both use start timestamps, where older means smaller. In wait-die, an older requester waits and a younger requester aborts itself. In wound-wait, an older requester aborts the younger holder, and a younger requester waits. Both avoid deadlock and starvation because aborted transactions keep their original timestamp; wound-wait is preemptive and usually causes fewer restarts.

Q9. What is the Thomas write rule?

It is a change to timestamp ordering: when a transaction tries to write an item that a younger transaction has already written, and no younger transaction has read it, the write is obsolete and is simply skipped instead of aborting the transaction. It allows some view-serializable schedules that are not conflict serializable.

Q10. When would you use optimistic instead of pessimistic locking?

Use optimistic locking when conflicts are rare or when there is long think time between read and write, such as a user editing a form. Use a version column and make the update conditional on it; on zero rows affected, re-read and retry or show a conflict. Use pessimistic locking (SELECT ... FOR UPDATE) when many transactions compete for the same rows, because optimistic retries would waste work.

Q11. How does MVCC work in PostgreSQL versus InnoDB?

PostgreSQL writes a new tuple version for every update in the table heap, marking versions with creating and deleting transaction IDs; VACUUM removes dead versions. InnoDB updates rows in place in the clustered index and keeps previous versions in the undo log, reconstructing older versions for readers by following roll pointers; purge threads clean up. In both, readers see a snapshot and do not block writers.

Q12. What are the default isolation levels of PostgreSQL and MySQL?

PostgreSQL defaults to Read Committed; MySQL InnoDB defaults to Repeatable Read. PostgreSQL's Repeatable Read is snapshot isolation, while InnoDB's uses a transaction snapshot for plain reads plus next-key locking for locking reads and writes. PostgreSQL never allows dirty reads, even at Read Uncommitted.

Q13. What does SELECT ... FOR UPDATE SKIP LOCKED do?

It selects matching rows and takes exclusive locks on them, skipping any rows already locked by other transactions instead of waiting. It is the standard way to build a database-backed job queue where many workers each claim different jobs. It is available in PostgreSQL 9.5+ and MySQL 8.0+.

Q14. Is Serializable isolation enough to stop all bugs? What else is needed?

It guarantees the result equals some serial order, but transactions may fail with serialization errors, so the application must retry them. It also cannot enforce rules you never encoded in the transaction logic or constraints. And it costs throughput, so many systems use Read Committed plus targeted locking reads and constraints instead.

Q15. How do you prevent deadlocks in application code?

Acquire locks in a consistent global order, such as always locking the lower account id first in a transfer. Keep transactions short, take the strongest lock you will need up front (FOR UPDATE rather than read then upgrade), and add indexes so updates lock only the rows they need instead of scanning. Finally, catch deadlock errors and retry.

Key takeaways

• The anomalies are lost update, dirty read, non-repeatable read, phantom read and write skew; know a timeline for each.
• Shared locks are compatible with each other; exclusive locks with nothing. Intention locks (IS, IX, SIX) make table-level and row-level locking work together cheaply.
• Two-phase locking guarantees conflict serializability via lock points; strict 2PL also guarantees strict, cascadeless schedules. 2PL does not prevent deadlocks.
• Deadlocks are detected with wait-for graph cycles or prevented with wait-die (young requester dies) or wound-wait (old requester wounds the young holder).
• Timestamp ordering rejects operations that arrive "too late"; the Thomas write rule skips obsolete writes.
• Optimistic concurrency validates at commit; in apps it is a version column in the WHERE clause.
• MVCC lets readers and writers proceed together: PostgreSQL keeps old tuples in the heap (VACUUM), InnoDB keeps them in the undo log (purge).
• Defaults: PostgreSQL Read Committed, MySQL InnoDB Repeatable Read. Snapshot isolation still allows write skew; Serializable prevents it but requires retries.

Next lesson

Continue with the course overview.