17 Locking Intro to Database Systems Andy Pavlo AP AP - PowerPoint PPT Presentation

Two-Phase 17 Locking Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Carnegie Mellon University Fall 2019

2 LAST CLASS Conflict Serializable → Verify using either the "swapping" method or dependency graphs. → Any DBMS that says that they support "serializable" isolation does this. View Serializable → No efficient way to verify. → Andy doesn't know of any DBMS that supports this. CMU 15-445/645 (Fall 2019)

3 EXAM PLE Schedule T 1 T 2 BEGIN R(A) BEGIN R(A) TIM E W(A) W(A) COMMIT R(A) COMMIT CMU 15-445/645 (Fall 2019)

4 O BSERVATIO N We need a way to guarantee that all execution schedules are correct (i.e., serializable) without knowing the entire schedule ahead of time. Solution: Use locks to protect database objects. CMU 15-445/645 (Fall 2019)

5 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN LOCK (A) Granted (T 1 →A) R(A) BEGIN LOCK (A) TIM E W(A) R(A) UNLOCK (A) R(A) W(A) COMMIT UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

5 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN LOCK (A) Granted (T 1 →A) R(A) BEGIN LOCK (A) TIM E Denied! W(A) R(A) UNLOCK (A) R(A) W(A) COMMIT UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

5 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN LOCK (A) Granted (T 1 →A) R(A) BEGIN LOCK (A) TIM E Denied! W(A) R(A) UNLOCK (A) R(A) W(A) COMMIT UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

5 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN LOCK (A) Granted (T 1 →A) R(A) BEGIN LOCK (A) TIM E Denied! W(A) R(A) UNLOCK (A) Released (T 1 →A) R(A) W(A) COMMIT UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

5 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN LOCK (A) Granted (T 1 →A) R(A) BEGIN LOCK (A) TIM E Denied! W(A) R(A) UNLOCK (A) Released (T 1 →A) R(A) Granted (T 2 →A) W(A) COMMIT UNLOCK (A) Released (T 2 →A) COMMIT CMU 15-445/645 (Fall 2019)

6 TO DAY'S AGEN DA Lock Types Two-Phase Locking Deadlock Detection + Prevention Hierarchical Locking Isolation Levels CMU 15-445/645 (Fall 2019)

7 LO CKS VS. LATCH ES Locks Latches Separate… User transactions Threads Protect… Database Contents In-Memory Data Structures During… Entire Transactions Critical Sections Modes… Shared, Exclusive, Update, Read, Write Intention Deadlock Detection & Resolution Avoidance …by… Waits-for, Timeout, Aborts Coding Discipline Kept in… Lock Manager Protected Data Structure Source: Goetz Graefe CMU 15-445/645 (Fall 2019)

8 BASIC LO CK TYPES S-LOCK : Shared locks for reads. X-LOCK : Exclusive locks for writes. Compatibility Matrix Shared Exclusive Shared ✔ X Exclusive X X CMU 15-445/645 (Fall 2019)

9 EXECUTIN G WITH LO CKS Transactions request locks (or upgrades). Lock manager grants or blocks requests. Transactions release locks. Lock manager updates its internal lock-table. → It keeps track of what transactions hold what locks and what transactions are waiting to acquire any locks. CMU 15-445/645 (Fall 2019)

10 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E UNLOCK (A) BEGIN X-LOCK (A) W(A) UNLOCK (A) S-LOCK (A) R(A) UNLOCK (A) COMMIT COMMIT CMU 15-445/645 (Fall 2019)

10 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E UNLOCK (A) Released (T 1 →A) BEGIN X-LOCK (A) W(A) UNLOCK (A) S-LOCK (A) R(A) UNLOCK (A) COMMIT COMMIT CMU 15-445/645 (Fall 2019)

10 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E UNLOCK (A) Released (T 1 →A) BEGIN X-LOCK (A) Granted (T 2 →A) W(A) Released (T 2 →A) UNLOCK (A) S-LOCK (A) R(A) UNLOCK (A) COMMIT COMMIT CMU 15-445/645 (Fall 2019)

10 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E UNLOCK (A) Released (T 1 →A) BEGIN X-LOCK (A) Granted (T 2 →A) W(A) Released (T 2 →A) UNLOCK (A) S-LOCK (A) Granted (T 1 →A) R(A) UNLOCK (A) Released (T 1 →A) COMMIT COMMIT CMU 15-445/645 (Fall 2019)

10 EXECUTIN G WITH LO CKS Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E UNLOCK (A) Released (T 1 →A) BEGIN X-LOCK (A) Granted (T 2 →A) W(A) Released (T 2 →A) UNLOCK (A) S-LOCK (A) Granted (T 1 →A) R(A) UNLOCK (A) Released (T 1 →A) COMMIT COMMIT CMU 15-445/645 (Fall 2019)

11 CO N CURREN CY CO N TRO L PROTO CO L Two-phase locking (2PL) is a concurrency control protocol that determines whether a txn can access an object in the database on the fly. The protocol does not need to know all the queries that a txn will execute ahead of time. CMU 15-445/645 (Fall 2019)

12 TWO - PH ASE LO CKIN G Phase #1: Growing → Each txn requests the locks that it needs from the DBMS’s lock manager. → The lock manager grants/denies lock requests. Phase #2: Shrinking → The txn is allowed to only release locks that it previously acquired. It cannot acquire new locks. CMU 15-445/645 (Fall 2019)

13 TWO - PH ASE LO CKIN G The txn is not allowed to acquire/upgrade locks after the growing phase finishes. Transaction Lifetime # of Locks Growing Phase Shrinking Phase TIM E CMU 15-445/645 (Fall 2019)

14 TWO - PH ASE LO CKIN G The txn is not allowed to acquire/upgrade locks after the growing phase finishes. 2PL Violation! Transaction Lifetime # of Locks Growing Phase Shrinking Phase TIM E CMU 15-445/645 (Fall 2019)

15 EXECUTIN G WITH 2PL Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E BEGIN X-LOCK (A) R(A) UNLOCK (A) COMMIT W(A) UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

15 EXECUTIN G WITH 2PL Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E BEGIN Denied! X-LOCK (A) R(A) UNLOCK (A) COMMIT W(A) UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

15 EXECUTIN G WITH 2PL Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E BEGIN Denied! X-LOCK (A) R(A) Released (T 1 →A) UNLOCK (A) COMMIT W(A) UNLOCK (A) COMMIT CMU 15-445/645 (Fall 2019)

15 EXECUTIN G WITH 2PL Schedule Lock Manager T 1 T 2 BEGIN X-LOCK (A) Granted (T 1 →A) R(A) W(A) TIM E BEGIN Denied! X-LOCK (A) R(A) Released (T 1 →A) UNLOCK (A) COMMIT Granted (T 2 →A) W(A) UNLOCK (A) Released (T 2 →A) COMMIT CMU 15-445/645 (Fall 2019)

16 TWO - PH ASE LO CKIN G 2PL on its own is sufficient to guarantee conflict serializability. → It generates schedules whose precedence graph is acyclic. But it is subject to cascading aborts . CMU 15-445/645 (Fall 2019)

17 2PL CASCADIN G ABO RTS Schedule T 1 T 2 BEGIN BEGIN X-LOCK (A) X-LOCK (B) R(A) TIM E W(A) UNLOCK (A) X-LOCK (A) R(A) W(A) R(B) ⋮ W(B) ABORT CMU 15-445/645 (Fall 2019)

17 2PL CASCADIN G ABO RTS Schedule This is a permissible schedule in T 1 T 2 2PL, but the DBMS has to also BEGIN BEGIN abort T 2 when T 1 aborts. X-LOCK (A) X-LOCK (B) → Any information about T 1 cannot R(A) TIM E be "leaked" to the outside world. W(A) UNLOCK (A) X-LOCK (A) R(A) W(A) This is all wasted work! R(B) ⋮ W(B) ABORT CMU 15-445/645 (Fall 2019)

18 2PL O BSERVATIO N S There are potential schedules that are serializable but would not be allowed by 2PL. → Locking limits concurrency. May still have "dirty reads". → Solution: Strong Strict 2PL (aka Rigorous 2PL) May lead to deadlocks. → Solution: Detection or Prevention CMU 15-445/645 (Fall 2019)

19 STRO N G STRICT TWO - PH ASE LO CKIN G The txn is not allowed to acquire/upgrade locks after the growing phase finishes. Allows only conflict serializable schedules, but it is often stronger than needed for some apps. # of Locks Release all locks at end of txn. Growing Phase Shrinking Phase TIM E CMU 15-445/645 (Fall 2019)

20 STRO N G STRICT TWO - PH ASE LO CKIN G A schedule is strict if a value written by a txn is not read or overwritten by other txns until that txn finishes. Advantages: → Does not incur cascading aborts. → Aborted txns can be undone by just restoring original values of modified tuples. CMU 15-445/645 (Fall 2019)

21 EXAM PLES T 1 – Move $100 from Andy’s account ( A ) to his bookie’s account ( B ). T 2 – Compute the total amount in all accounts and return it to the application. T 1 T 2 BEGIN BEGIN A=A-100 ECHO A+B B=B+100 COMMIT COMMIT CMU 15-445/645 (Fall 2019)

22 N O N- 2PL EXAM PLE Schedule Initial Database State T 1 T 2 A =1000, B =1000 BEGIN BEGIN X-LOCK (A) R(A) S-LOCK (A) A=A-100 TIM E W(A) UNLOCK (A) R(A) UNLOCK (A) S-LOCK (B) X-LOCK (B) R(B) UNLOCK (B) R(B) ECHO A+B B=B+100 COMMIT W(B) UNLOCK (B) COMMIT CMU 15-445/645 (Fall 2019)

22 N O N- 2PL EXAM PLE Schedule Initial Database State T 1 T 2 A =1000, B =1000 BEGIN BEGIN X-LOCK (A) R(A) S-LOCK (A) A=A-100 TIM E W(A) UNLOCK (A) R(A) UNLOCK (A) S-LOCK (B) X-LOCK (B) R(B) UNLOCK (B) R(B) ECHO A+B B=B+100 COMMIT W(B) UNLOCK (B) COMMIT CMU 15-445/645 (Fall 2019)