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 2020

2 ADM IN ISTRIVIA Project #3 is due Sun Nov 22 nd @ 11:59pm. Homework #4 is due Sun Nov 8 th @ 11:59pm. 15-445/645 (Fall 2020)

3 ADM IN ISTRIVIA Sign up for the student-run discussion groups. → Small group of at most 10 students where you can discuss the implementation details of the projects. → You can share test code, but you are not allowed to share implementation code. See Piazza@906 for more details. 15-445/645 (Fall 2020)

4 UPCO M IN G DATABASE TALKS MySQL Query Optimizer → Monday Nov 2 nd @ 5pm ET EraDB "Magical Indexes" → Monday Nov 9 th @ 5pm ET FaunaDB Serverless DBMS → Monday Nov 16 th @ 5pm ET 15-445/645 (Fall 2020)

5 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. 15-445/645 (Fall 2020)

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

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

7 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. 15-445/645 (Fall 2020)

8 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 15-445/645 (Fall 2020)

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

10 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 15-445/645 (Fall 2020)

11 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 15-445/645 (Fall 2020)

12 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. 15-445/645 (Fall 2020)

13 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 15-445/645 (Fall 2020)

13 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 15-445/645 (Fall 2020)

14 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. 15-445/645 (Fall 2020)

15 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. 15-445/645 (Fall 2020)

16 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 15-445/645 (Fall 2020)

17 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 15-445/645 (Fall 2020)

18 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 15-445/645 (Fall 2020)

18 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 15-445/645 (Fall 2020)

19 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 . 15-445/645 (Fall 2020)

20 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 15-445/645 (Fall 2020)

21 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 15-445/645 (Fall 2020)

22 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 15-445/645 (Fall 2020)

23 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. 15-445/645 (Fall 2020)

24 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 15-445/645 (Fall 2020)

25 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 T 2 Output TIM E W(A) UNLOCK (A) A + B =1100 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 15-445/645 (Fall 2020)

26 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 T 2 Output TIM E W(A) X-LOCK (B) A + B =2000 UNLOCK (A) R(A) S-LOCK (B) R(B) B=B+100 W(B) UNLOCK (B) R(B) COMMIT UNLOCK (A) UNLOCK (B) ECHO A+B COMMIT 15-445/645 (Fall 2020)

27 STRO N G STRICT 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 W(A) T 2 Output TIM E X-LOCK (B) R(B) A + B =2000 B=B+100 W(B) UNLOCK (A) UNLOCK (B) R(A) COMMIT S-LOCK (B) R(B) ECHO A+B UNLOCK (A) UNLOCK (B) COMMIT 15-445/645 (Fall 2020)

28 UN IVERSE O F SCH EDULES All Schedules View Serializable Conflict Serializable No Cascading Strong Strict 2PL Aborts Serial 15-445/645 (Fall 2020)

29 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 (Rigorous) May lead to deadlocks. → Solution: Detection or Prevention 15-445/645 (Fall 2020)

30 SH IT J UST GOT REAL, SO N Schedule Lock Manager T 1 T 2 BEGIN BEGIN Granted (T 1 →A) X-LOCK (A) S-LOCK (B) Granted (T 2 →B) R(B) TIM E S-LOCK (A) Denied! R(A) X-LOCK (B) Denied! 15-445/645 (Fall 2020)

30 SH IT J UST GOT REAL, SO N Schedule Lock Manager T 1 T 2 BEGIN BEGIN Granted (T 1 →A) X-LOCK (A) S-LOCK (B) Granted (T 2 →B) R(B) TIM E S-LOCK (A) Denied! R(A) X-LOCK (B) Denied! 15-445/645 (Fall 2020)

31 2PL DEADLO CKS A deadlock is a cycle of transactions waiting for locks to be released by each other. Two ways of dealing with deadlocks: → Approach #1: Deadlock Detection → Approach #2: Deadlock Prevention 15-445/645 (Fall 2020)