The abstraction
- Every transaction should run as if it had dedicated access to the database, unaware of anything else running (
26:01,26:27). - Concurrency is achieved by interleaving reads and writes, while the final database state must be as if the transactions had executed one-by-one (
26:41,26:58).
Concurrency control protocols = the traffic cop
The protocol dynamically generates the schedule: who runs when, and what each operation is allowed to read or write (26:58, 27:28).
| Family | Idea | Lecture |
|---|---|---|
| Pessimistic (e.g. two-phase locking) | Check before every operation; block or abort to prevent problems (27:50) | Next Monday |
| Optimistic (OCC) | Let transactions do what they want, track reads/writes, validate at commit (27:50, 28:23) | Next Wednesday |
- Most systems implement one or the other, not both (
28:23).
The two-account worked example
Setup: accounts A and B hold $1,000 each. T1 transfers $100 from A to B; T2 applies 6% interest to both (28:38, 29:03).
- Under any serial ordering,
A + Bmust equal $2,120 — that is the correctness criterion, not the individual values of A and B (29:49,30:23). A = A - 100is not atomic to the DBMS: it seesREAD(A)thenWRITE(A)as two separate operations (29:25,35:10).- Two legal serial outcomes: T1→T2 or T2→T1. Both give different A and B values, both sum to $2,120 (
31:28,31:44).
Good interleaving
T1 debits A, blocks on a page miss; T2 applies interest to A; T1 resumes, credits B, commits; T2 applies interest to B and commits. This is equivalent to T1 followed by T2 — every update to a given object in T1 precedes the update to that same object in T2 (32:53, 33:18).
Bad interleaving
T1 debits A; T2 applies interest to A and to B; T1 then credits B. Total comes to $2,114 — $6 missing (34:49, 35:10).
Order of arrival does not matter
- The DBMS may execute submitted transactions in any order, regardless of arrival order (
30:23). - To force T1 before T2, the application must sequence it itself — submit T2 only after T1's commit acknowledgement (
30:43,39:00). - Google Spanner is the exception, providing external consistency: the submission order is the commit order. Most systems don't offer it and most applications don't need it (
30:43,39:14).
Definitions
- Serial schedule: no interleaving between transactions (
36:53). - Equivalent schedules: starting from the same database state, both end in the same database state (
36:53). - Serializable schedule: an arbitrary interleaving whose final state matches some serial ordering — the gold standard isolation level (
37:15). - Above it sits strong serializability / strong consistency (execution in arrival order), which most systems do not provide and most people do not need (
37:15). - Correctness here is decoupled from arrival time and from commit time — the commit order may differ from both (
37:53,38:08).