ADVANCED DATABASE SYSTEMS Index Locking & Latching @ - PowerPoint PPT Presentation

CMU 15-721 (Spring 2019) 16 LATCH IM PLEM EN TATIO NS Choice #4: Reader-Writer Locks → Allows for concurrent readers → Have to manage read/write queues to avoid starvation → Can be implemented on top of spinlocks Latch read write =0 =1 =2 =0 =0 =0

CMU 15-721 (Spring 2019) 16 LATCH IM PLEM EN TATIO NS Choice #4: Reader-Writer Locks → Allows for concurrent readers → Have to manage read/write queues to avoid starvation → Can be implemented on top of spinlocks Latch read write =0 =1 =2 =0 =0 =0

CMU 15-721 (Spring 2019) 16 LATCH IM PLEM EN TATIO NS Choice #4: Reader-Writer Locks → Allows for concurrent readers → Have to manage read/write queues to avoid starvation → Can be implemented on top of spinlocks Latch read write =0 =1 =2 =0 =0 =0 =1

CMU 15-721 (Spring 2019) 16 LATCH IM PLEM EN TATIO NS Choice #4: Reader-Writer Locks → Allows for concurrent readers → Have to manage read/write queues to avoid starvation → Can be implemented on top of spinlocks Latch read write =0 =1 =2 =0 =0 =0 =1

CMU 15-721 (Spring 2019) 16 LATCH IM PLEM EN TATIO NS Choice #4: Reader-Writer Locks → Allows for concurrent readers → Have to manage read/write queues to avoid starvation → Can be implemented on top of spinlocks Latch read write =0 =1 =2 =0 =0 =1 =0 =1

CMU 15-721 (Spring 2019) 17 LATCH CRABBIN G / CO UPLIN G Acquire and release latches on B+Tree nodes when traversing the data structure. A thread can release latch on a parent node if its child node considered safe . → Any node that won’t split or merge when updated. → Not full (on insertion) → More than half-full (on deletion)

CMU 15-721 (Spring 2019) 18 LATCH CRABBIN G Search: Start at root and go down; repeatedly, → Acquire read ( R ) latch on child → Then unlock the parent node. Insert/Delete: Start at root and go down, obtaining write ( W ) latches as needed. Once child is locked, check if it is safe: → If child is safe, release all locks on ancestors.

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 R A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 We can release the latch on A as R soon as we acquire the latch for C . A 20 R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 We can release the latch on A as soon as we acquire the latch for C . A 20 R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 We can release the latch on A as soon as we acquire the latch for C . A 20 R B C 10 35 R D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 19 EXAM PLE # 1: SEARCH 23 We can release the latch on A as soon as we acquire the latch for C . A 20 B C 10 35 R D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 W A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 We may need to coalesce C , so we W can’t release the latch on A . A 20 W B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 We may need to coalesce C , so we W can’t release the latch on A . A 20 G will not merge with F , so we can release latches on A and C . W B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 We may need to coalesce C , so we can’t release the latch on A . A 20 G will not merge with F , so we can release latches on A and C . B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 20 EXAM PLE # 2: DELETE 4 4 We may need to coalesce C , so we can’t release the latch on A . A 20 G will not merge with F , so we can release latches on A and C . B C 10 35 X W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 W A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so W we can release the latch on A . A 20 W B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so we can release the latch on A . A 20 W B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so we can release the latch on A . A 20 G has to split, so we can’t release the latch on C . W B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so we can release the latch on A . A 20 G has to split, so we can’t release the latch on C . W B C 10 35 W D E F G H 6 12 23 38 44 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so we can release the latch on A . A 20 G has to split, so we can’t release the latch on C . W B C 10 35 44 W D E F G H 6 12 23 38 40 44 44

CMU 15-721 (Spring 2019) 21 EXAM PLE # 3: IN SERT 4 0 C has room if its child has to split, so we can release the latch on A . A 20 G has to split, so we can’t release the latch on C . B C 10 35 44 D E F G H 6 12 23 38 44 40 44

CMU 15-721 (Spring 2019) 22 O BSERVATIO N What was the first step that the DBMS took in the two examples that updated the index? Delete 44 Insert 40 W W A A 20 20

CMU 15-721 (Spring 2019) 23 BETTER LATCH CRABBIN G Optimistically assume that the leaf is safe. → Take R latches as you traverse the tree to reach it and verify. → If leaf is not safe, then do previous algorithm. CONCURRENCY O OF OPERATIONS ON B- TREES ACTA I INFORMATICA 1977

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 R A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 We assume that C is safe, so we can R release the latch on A . A 20 R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 We assume that C is safe, so we can release the latch on A . A 20 R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 We assume that C is safe, so we can release the latch on A . A 20 Acquire an exclusive latch on G . R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 We assume that C is safe, so we can release the latch on A . A 20 Acquire an exclusive latch on G . B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 24 EXAM PLE # 4 : DELETE 4 4 We assume that C is safe, so we can release the latch on A . A 20 Acquire an exclusive latch on G . B C 10 35 X W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 25 O BSERVATIO N Crabbing ensures that txns do not corrupt the internal data structure during modifications. But because txns release latches on each node as soon as they are finished their operations, we cannot guarantee that phantoms do not occur…

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 A 20 B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 R Txn #1: A 20 READ(25) B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 R Txn #1: A 20 READ(25) R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) R B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) B C 10 35 R D E F G ! 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) Txn #2: INSERT(25) B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) Txn #2: INSERT(25) B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) Txn #2: INSERT(25) B C 10 35 W D E F G 6 12 23 25 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) Txn #2: INSERT(25) B C 10 35 Txn #1: INSERT(25) D E F G 6 12 23 25 38 44

CMU 15-721 (Spring 2019) 26 PRO BLEM SCEN ARIO # 1 Txn #1: A 20 READ(25) Txn #2: INSERT(25) B C 10 35 Txn #1: W INSERT(25) D E F G 6 12 23 25 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] B C 10 35 D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] B C 10 35 R D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] B C 10 35 R R D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] Txn #2: W INSERT(21) B C 10 35 W D E F G 6 12 23 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] Txn #2: W INSERT(21) B C 10 35 W D E F G 6 12 21 23 23 38 44

CMU 15-721 (Spring 2019) 27 PRO BLEM SCEN ARIO # 2 Txn #1: A 20 [12,23] Txn #2: INSERT(21) B C 10 35 Txn #1: R R [12,23] D E F G 6 12 21 23 23 38 44

CMU 15-721 (Spring 2019) 28 IN DEX LO CKS Need a way to protect the index’s logical contents from other txns to avoid phantoms. Difference with index latches: → Locks are held for the entire duration of a txn. → Only acquired at the leaf nodes. → Not physically stored in index data structure. Can be used with any order-preserving index.

CMU 15-721 (Spring 2019) 29 IN DEX LO CKS Lock Table txn1 txn2 txn3 • • • X S S txn3 txn2 txn4 • • • S S S txn4 txn6 txn5 • • • IX X S

CMU 15-721 (Spring 2019) 30 IN DEX LO CKIN G SCH EM ES Predicate Locks Key-Value Locks Gap Locks Key-Range Locks Hierarchical Locking

CMU 15-721 (Spring 2019) 93 PREDICATE LO CKS Proposed locking scheme from System R. → Shared lock on the predicate in a WHERE clause of a SELECT query. → Exclusive lock on the predicate in a WHERE clause of any UPDATE , INSERT , or DELETE query. Never implemented in any system. THE NOTIONS OF CONSISTENCY AND PREDICATE LOCKS IN A DATABASE SYSTEM CACM 1976

CMU 15-721 (Spring 2019) 94 PREDICATE LO CKS SELECT SUM (balance) INSERT INTO account FROM account (name, balance) WHERE name = 'Biggie' VALUES ('Biggie', 100); Records in Table "account" name='Biggie' name='Biggie' ∧ balance=100

CMU 15-721 (Spring 2019) 95 KEY- VALUE LO CKS Locks that cover a single key value. Need “virtual keys” for non -existent values. B+Tree Leaf Node Key [14, 14] 10 12 14 16

CMU 15-721 (Spring 2019) 96 GAP LO CKS Each txn acquires a key-value lock on the single key that it wants to access. Then get a gap lock on the next key gap. B+Tree Leaf Node 10 12 14 16 {Gap} {Gap} {Gap} Gap (14, 16)

CMU 15-721 (Spring 2019) 97 KEY- RAN GE LO CKS A txn takes locks on ranges in the key space. → Each range is from one key that appears in the relation, to the next that appears. → Define lock modes so conflict table will capture commutativity of the operations available.

CMU 15-721 (Spring 2019) 98 KEY- RAN GE LO CKS Locks that cover a key value and the gap to the next key value in a single index. → Need “virtual keys” for artificial values (infinity) B+Tree Leaf Node Next Key [14, 16) 10 12 14 16 {Gap} {Gap} {Gap}

CMU 15-721 (Spring 2019) 98 KEY- RAN GE LO CKS Locks that cover a key value and the gap to the next key value in a single index. → Need “virtual keys” for artificial values (infinity) B+Tree Leaf Node 10 12 14 16 {Gap} {Gap} {Gap} Prior Key (12, 14]

CMU 15-721 (Spring 2019) 100 H IERARCH ICAL LO CKIN G Allow for a txn to hold wider key-range locks with different locking modes. → Reduces the number of visits to lock manager. B+Tree Leaf Node 10 12 14 16 {Gap} {Gap} {Gap}