DataIntensive Computing Systems Concurrency Control II Shivnath Babu
Data-Intensive Computing Systems Concurrency Control (II) Shivnath Babu 1
How to enforce serializable schedules? Option 1: run system, recording P(S); at end of day, check for P(S) cycles and declare if execution was good 2
How to enforce serializable schedules? Option 2: prevent P(S) cycles from occurring T 1 T 2 …. . Tn Scheduler DB 3
A locking protocol Two new actions: lock (exclusive): unlock: T 1 li (A) ui (A) T 2 scheduler lock table 4
Rule #1: Well-formed transactions Ti: … li(A) … pi(A) … ui(A). . . 5
Rule #2 Legal scheduler S = ……. . li(A) ………. . . ui(A) ……. . . no lj(A) 6
Exercise: • What schedules are legal? What transactions are well-formed? S 1 = l 1(A)l 1(B)r 1(A)w 1(B)l 2(B)u 1(A)u 1(B) r 2(B)w 2(B)u 2(B)l 3(B)r 3(B)u 3(B) S 2 = l 1(A)r 1(A)w 1(B)u 1(A)u 1(B) l 2(B)r 2(B)w 2(B)l 3(B)r 3(B)u 3(B) S 3 = l 1(A)r 1(A)u 1(A)l 1(B)w 1(B)u 1(B) l 2(B)r 2(B)w 2(B)u 2(B)l 3(B)r 3(B)u 3(B) 7
Exercise: • What schedules are legal? What transactions are well-formed? S 1 = l 1(A)l 1(B)r 1(A)w 1(B)l 2(B)u 1(A)u 1(B) r 2(B)w 2(B)u 2(B)l 3(B)r 3(B)u 3(B) S 2 = l 1(A)r 1(A)w 1(B)u 1(A)u 1(B) l 2(B)r 2(B)w 2(B)l 3(B)r 3(B)u 3(B) S 3 = l 1(A)r 1(A)u 1(A)l 1(B)w 1(B)u 1(B) l 2(B)r 2(B)w 2(B)u 2(B)l 3(B)r 3(B)u 3(B) 8
Schedule F T 1 T 2 l 1(A); Read(A) A A+100; Write(A); u 1(A) l 2(A); Read(A) A Ax 2; Write(A); u 2(A) l 2(B); Read(B) B Bx 2; Write(B); u 2(B) l 1(B); Read(B) B B+100; Write(B); u 1(B) 9
Schedule F A B T 1 T 2 25 25 l 1(A); Read(A) A A+100; Write(A); u 1(A) 125 l 2(A); Read(A) A Ax 2; Write(A); u 2(A) 250 l 2(B); Read(B) B Bx 2; Write(B); u 2(B) 50 l 1(B); Read(B) B B+100; Write(B); u 1(B) 150 250 10
Rule #3 Two phase locking (2 PL) for transactions Ti = ……. li(A) ………. . . ui(A) ……. . . no unlocks no locks 11
# locks held by Ti Time Growing Phase Shrinking Phase 12
Schedule G T 1 l 1(A); Read(A) A A+100; Write(A) l 1(B); u 1(A) T 2 delayed l 2(A); Read(A) A Ax 2; Write(A); l 2(B) 13
Schedule G T 1 l 1(A); Read(A) A A+100; Write(A) l 1(B); u 1(A) T 2 delayed l 2(A); Read(A) A Ax 2; Write(A); l 2(B) Read(B); B B+100 Write(B); u 1(B) 14
Schedule G T 1 l 1(A); Read(A) A A+100; Write(A) l 1(B); u 1(A) T 2 delayed l 2(A); Read(A) A Ax 2; Write(A); l 2(B) Read(B); B B+100 Write(B); u 1(B) l 2(B); u 2(A); Read(B) B Bx 2; Write(B); u 2(B); 15
Schedule H (T 2 reversed) T 1 l 1(A); Read(A) A A+100; Write(A) l 1(B) delayed T 2 l 2(B); Read(B) B Bx 2; Write(B) l 2(A) delayed 16
• Assume deadlocked transactions are rolled back – They have no effect – They do not appear in schedule E. g. , Schedule H = This space intentionally left blank! 17
Next step: Show that rules #1, 2, 3 conflictserializable schedules 18
Conflict rules for li(A), ui(A): • li(A), lj(A) conflict • li(A), uj(A) conflict Note: no conflict < ui(A), uj(A)>, < li(A), rj(A)>, . . . 19
Theorem Rules #1, 2, 3 conflict (2 PL) serializable schedule To help in proof: Definition Shrink(Ti) = SH(Ti) = first unlock action of Ti 20
Lemma Ti Tj in S SH(Ti) <S SH(Tj) Proof of lemma: Ti Tj means that S = … pi(A) … qj(A) …; p, q conflict By rules 1, 2: S = … pi(A) … ui(A) … lj(A). . . qj(A) … By rule 3: SH(Ti) SH(Tj) So, SH(Ti) <S SH(Tj) 21
Theorem Rules #1, 2, 3 conflict (2 PL) serializable schedule Proof: (1) Assume P(S) has cycle T 1 T 2 …. Tn T 1 (2) By lemma: SH(T 1) < SH(T 2) <. . . < SH(T 1) (3) Impossible, so P(S) acyclic (4) S is conflict serializable 22
• Beyond this simple 2 PL protocol, it is all a matter of improving performance and allowing more concurrency…. – Shared locks – Multiple granularity – Inserts, deletes, and phantoms – Other types of C. C. mechanisms 23
- Slides: 23