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