Concurrency problems: lost update, dirty read, unrepeatable read, phantom

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. Which ACID property keeps concurrent transactions from seeing each other's intermediate states? from Lesson 6.1.1
2. Who ensures consistency? from Lesson 6.1.1
3. Name the file-processing problem when two clerks both sell the last seat. from Lesson 0.1.1

1 · The Hook

Running transactions one after another is safe but slow: the CPU idles while one transaction waits for the disk. So the DBMS interleaves them. Without control, interleaving produces four classic errors. GATE describes a schedule and asks which one it shows.

2 · Predict First

A = 1000. T1 withdraws 100: R1(A), A − 100, W1(A). T2 deposits 50: R2(A), A + 50, W2(A). They run as R1(A) R2(A) W1(A) W2(A). What is A at the end, and what should it be?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

Both read 1000. T1 writes 900; T2 then writes 1050. Final A = 1050; correct is 950. T1's update is lost.

1 · Lost update (write–write after reads)

Two transactions read the same item, then both write it; the second write overwrites the first. Pattern: R1(X) R2(X) W1(X) W2(X).

2 · Dirty read (temporary update, write–read)

T2 reads a value written by T1 before T1 commits; then T1 aborts. T2 has used a value that never officially existed. Pattern: W1(X) R2(X) … A1.

3 · Unrepeatable read (read–write)

T1 reads X, T2 changes X and commits, T1 reads X again and gets a different value within one transaction. Pattern: R1(X) W2(X) C2 R1(X).

4 · Phantom

T1 runs a query with a condition (all CS students); T2 inserts (or deletes) a row satisfying it and commits; T1 re-runs the query and sees a new "phantom" row. Item-level locks on existing rows cannot prevent it; it needs predicate or index-range locking.

A fifth, incorrect summary, is a variant: an aggregate (sum of balances) computed while another transaction is half-way through transfers.

SQL isolation levels (which anomalies they allow)

Lost updates are prevented at every level in the standard.

4 · Visual

LOST UPDATE            DIRTY READ              UNREPEATABLE READ        PHANTOM
T1        T2           T1        T2            T1        T2             T1                 T2
R(A)                   W(A)                    R(A)=5                   count CS = 3
          R(A)                   R(A) <- dirty           W(A)=8                            insert CS row
W(A)                   abort                             commit                            commit
          W(A) <- wins           (uses bad A)  R(A)=8 (!)               count CS = 4 (!)

5 · Worked Examples

Example 1 Easy

Name the anomaly: W1(X) R2(X) W2(Y) C2 A1.

Solution

Dirty read: T2 read X written by uncommitted T1, which then aborted.

Example 2 Medium

A = 500. T1: R(A), A × 2, W(A). T2: R(A), A + 100, W(A). Schedule R1(A) R2(A) W2(A) W1(A). Final A? Which anomaly?

Solution

Both read 500; T2 writes 600; T1 writes 1000. Final 1000; serial results would be 1100 (T1 then T2) or 1200 (T2 then T1). Lost update (T2's).

Example 3 GATE

Which isolation level prevents dirty reads but still allows unrepeatable reads?

Solution

READ COMMITTED.

8 · Connect the Dots

Lesson 6.1.1 defined transactions; this lesson showed what uncontrolled interleaving breaks. Next: a precise way to write interleavings, schedules, and to count how many there are.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Anomaly

R1(X) W2(X) C2 R1(X) shows:

1. (A) unrepeatable read
2. (B) dirty read
3. (C) lost update
4. (D) phantom

Q2 · NAT · 2 mark(s) — Lost update

X = 100. T1: R(X), X + 20, W(X). T2: R(X), X − 30, W(X). Schedule R1(X) R2(X) W1(X) W2(X). Final X?

Numerical answer: ________

Q3 · MCQ · 1 mark(s) — Isolation levels

Which isolation level allows phantoms but prevents unrepeatable reads?

1. (A) REPEATABLE READ
2. (B) READ UNCOMMITTED
3. (C) SERIALIZABLE
4. (D) READ COMMITTED

Q4 · MCQ · 2 mark(s) — Dirty read

In W1(A) R2(A) W2(A) C2 A1, the main problem is:

1. (A) T2 committed a value derived from data that was rolled back
2. (B) T1 lost its update
3. (C) T2 read A twice
4. (D) there is no problem

Q5 · MSQ · 2 mark(s) — Phantom

Which statements about phantoms are true?

1. (A) SERIALIZABLE isolation prevents them.
2. (B) A phantom is the same as a dirty read.
3. (C) Locking every existing row a query read is enough to prevent them.
4. (D) They involve rows inserted or deleted by another transaction.

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

interleavinglost updatedirty read (temporary update)unrepeatable readphantomincorrect summarySQL isolation levels