- 01Read A
- 02Calculate locally
−1 - 03Write A
- 04Commit
- Latest read of A
- —
- Local calculation
- —
- Uncommitted writes
- —
DATABASE CONCURRENCY / 01
Predict the result, then trace an overwritten stock deduction.
Order one buys 1 unit; order two buys 2. Each reads stock, calculates locally, then writes its calculated constant back.
This models an application reading, calculating, then writing a constant. It differs from an atomic database update such as UPDATE stock = stock - n.
Finish T1 before T2 reads to get 7. This is not an atomic UPDATE statement.
10Integer 10–100. Applying it starts a new run.
−1B15Commit publishes writes; rollback discards the transaction’s buffered writes.
No locks held yet
−2Next T1 · Read A
Run a transaction’s next operation, or follow the preset.
None of these four anomalies observed yet. This does not prove every schedule safe.
Running an operation from an earlier state replaces its later history. Changing the scenario, model or applied stock resets the schedule.
T1 changes A from 10 to 6. T2 reads before T1 rolls back. The dirty read occurs when T2 reads 6.
Compare T2’s local read with committed stock. A rollback cannot retract a value already read.
T1 reads A twice. T2 changes it to 6 and commits between the reads. Compare the value of the same row.
Watch T1’s reads: 10 → 6. The RR row-lock model makes T2’s write wait.
T1 queries stock ≥ 10 twice. T2 inserts C with stock 12 and commits in between. {A, B} becomes {A, B, C}.
Compare product IDs, not just counts. This RR locks existing rows; SER also protects the range.
Both read 10 and calculate 9 and 8. T2 commits its stale result after T1 commits, overwriting one deduction.
Finish T1 before T2 reads to get 7. This is not an atomic UPDATE statement.
Each model starts independently with stock 10 and runs the same order until its first blocked operation or the end.
May read uncommitted buffers
No read locks here; writes still require exclusive locks.
Choose a scenario, then run the comparison.
Latest committed data per statement
No retained read locks; commits may change later reads.
Choose a scenario, then run the comparison.
Retain shared locks on read rows
Writers may wait; no range protection in this model.
Choose a scenario, then run the comparison.
Retained row locks + range protection
Potentially more waits; range locking is simplified.
Choose a scenario, then run the comparison.
“Not observed” refers only to this schedule. A blocked model shows an intermediate stock value, not a final result.
T1Committed dataRU / RC / RR / SEROther write bufferReadable only at RUT2This abstract teaching model combines committed-version reads at RC, retained row locks at RR, and extra range protection at SER. It does not reproduce a particular database.
Implementations differ. PostgreSQL runs RU as RC, and its RR also prevents phantom reads. Do not apply this page’s locking model directly to a real product.
RU can read other transactions’ uncommitted buffered writes. Each RC statement reads current committed data. All models can read their own buffered writes.
Writes stay private until commit; rollback discards them. All models hold exclusive write locks until completion, preventing dirty writes to the same row.
RR retains shared locks on rows it reads, without range protection. SER adds a coarse range lock. Here, it conflicts with all other inserts and writes that may change the query range, as a teaching simplification.
A blocked operation does not run or advance. Two readers upgrading locks can wait for each other. Roll one back and retry the other. This lab does not automatically select a deadlock victim.
Official sources explain concepts and implementation differences. The explicit rules above define this lab’s actual behaviour.
PostgreSQL · Transaction isolation ↗SQL Server · Isolation levels ↗SQL Server · Locks & row versions ↗