CS 764: Topics in Database Management Systems Lecture 9: B-tree - - PowerPoint PPT Presentation

Xiangyao Yu 10/5/2020

CS 764: Topics in Database Management Systems Lecture 9: B-tree Locking

1

Today’s Paper: B-tree Locking

ACM Trans. Database Syst. 1981

2

Agenda

3

Index in OLTP database B tree, B+ tree, and B* tree Blink-tree

Index in an OLTP Database

4

Primary index (id)

Data store

Select name From student Where id=xxx ptr to tuple or page id

Index in an OLTP Database

5

Primary index (id)

Data store

Select name From student Where id=xxx Primary index (id)

Data store

Select name From student Where email=xxx Secondary index (email) ptr to tuple or page id id ptr to tuple or page id

B-tree

6

Algorithm Average Worst case Space O(n) O(n) Search O(log n) O(log n) Insert O(log n) O(log n) Delete O(log n) O(log n)

Balanced tree data structure

• Data is sorted
• Supports: search, sequential scan, insets, and deletes

B-tree

7

Algorithm Average Worst case Space O(n) O(n) Search O(log n) O(log n) Insert O(log n) O(log n) Delete O(log n) O(log n)

Balanced tree data structure

• Data is sorted
• Supports: search, sequential scan, inserts, and deletes

Properties

• Every node has at most m children.
• Every non-leaf node (except root) has at least ⌈m/2⌉ child nodes.
• All the leaf nodes of the B-tree must be at the same level.

B-tree vs. B+ Tree vs. B* Tree

8

B-tree: data pointers stored in all nodes

10 8 14 16 4 9 13 15 17 19

B-tree

B-tree vs. B+ Tree vs. B* Tree

9

B-tree: data pointers stored in all nodes B+ tree:

• Data pointers stored only in leaf nodes
• The leaf nodes are linked

10 8 14 16 4 9 13 15 17 19 10 8 14 16 4 9 13 15 17 19

B-tree B+ tree

B-tree vs. B+ Tree vs. B* Tree

10

B-tree: data pointers stored in all nodes B+ tree:

• Data pointers stored only in leaf nodes
• The leaf nodes are linked

B* tree is a misused term in B-tree literature

• Typically means a variant of B+ tree in which each node is least 2/3 full
• In this paper: B+ tree with high key appended to non-leaf nodes (upper bound on values)

10 8 14 16 4 9 13 15 17 19 10 8 14 16 4 9 13 15 17 19

B-tree B+ tree

10 8 14 16 4 9 13 15 17 19

B* tree

9 19 19

high key

B* Tree Structure

Within each node, keys in ascending order Each node contains at least k keys and at most 2k keys (k is a tree parameter) Values stored in a subtree are bounded by the the two key values

Ki-1 < v ≤ Ki

Example: search key 53

11

B* Tree Insertion

Insert to leaf if the leaf node has fewer than 2k entries If leaf has 2k entries, split the node into two nodes (split may happen recursively)

12

Challenge of Concurrent Operations

Concurrent search and insert operations may cause problems

13

Blink-Tree

14

Adds a link field that points to the next node at the same level of the tree as the current node The link pointer of the rightmost node on a level is a null pointer

10 8 14 16 4 9 13 15 17 19 10 8 14 16 4 9 13 15 17 19

Blink tree B* tree

9 19 19

link pointer

9 19 19

Blink-Tree: Search Algorithm

Example: search Key=13

15

5 10 23 11 13 17 19

… … … …

Key: 13

…

root

… …

Blink-Tree: Search Algorithm

Example: search Key=13

16

5 10 23 11 13 17 19

… … … …

Key: 13

…

root

… …

Blink-Tree: Insert Algorithm

17

Insert to leaf if the leaf node if not full Illustration of node split (node a is split into a’ and b’)

Before split Step 1 Step 2 Step 3

Blink-Tree: Insert Algorithm

18

5 10 23

… …

root

… …

11 13 17 23

… … … …

Example: Insert 14

F

Blink-Tree: Insert Algorithm

19

5 10 23

… …

root

… … stack = { F root, }

11 13 17 23

… … … …

Example: Insert 14

F

Blink-Tree: Insert Algorithm

20

5 10 23

… …

root

… …

11 13 17 23

… … … …

Example: Insert 14

F

initially, w is the data page to be inserted

Blink-Tree: Insert Algorithm

21

5 10 23

… …

root

… …

11 13 17 23

… … … …

Example: Insert 14

F

Blink-Tree: Insert Algorithm

22

5 10 23 11 13 17 23

… … … … …

root

… …

Example: Insert 14

A

…

B

Allocate new block on disk

F

Blink-Tree: Insert Algorithm

23

5 10 23 11 13 17 23

… … … … …

root

… …

11 13 14 17 23

…

Example: Insert 14

create two pages in memory

A B F

Blink-Tree: Insert Algorithm

24

5 10 23 11 13 17 23

… … … … …

root

… …

11 13 14 17 23

…

Example: Insert 14

A B F

update the two disk pages (page B first)

Blink-Tree: Insert Algorithm

25

5 10 23 11 13 14

… …

root

… …

A

17 23

B

Example: Insert 14

F

update the two disk pages (page B first)

Blink-Tree: Insert Algorithm

26

5 10 23 11 13 14

… …

root

… …

F A

17 23

B

Example: Insert 14

try to insert (key=14, ptr=B) to F

Blink-Tree: Insert Algorithm

27

5 10 14 23 11 13 14

… …

root

… …

F A

17 23

B

Example: Insert 14

insert (key=14, ptr=B) to F

Blink-Tree: Insert Algorithm

28

5 7 9 10 12 13 14

… …

root

… …

F A

17 23

B

Example: Insert 14

At most three locks are being during an insert

11 14 23

Blink-Tree: Insert Algorithm

29

root

… …

Example: Insert 14

At most three locks are being during an insert

5 7 9 10 12 13 14

… …

F A

17 23

B

11 14 23

Blink-Tree: Insert Algorithm

30

root

… …

Example: Insert 14

At most three locks are being during an insert

5 7 9 10 12 13 14

… …

F A

17 23

B

11 14 23

Revisit Concurrent Operations

key=15 is less than max key in node y Follow the link ptr to the next leaf node and 15 is found!

31

Other Issues

Delete: allow fewer than k entries in a leaf node

• Observations: insertions are much more frequent than deletions

Deadlock freedom: locks are acquired bottom-up and left to right => total order Livelock: keep following the link pointer due to node splits

32

Q/A – B-tree Locking

33

B+ tree vs. B* tree? Which variant of B-tree are modern DBMSs using? Would a left pointer add benefit? Experimental comparison What’s the typical value of k? Binary search within a node? Disk utilization w.r.t. deletion Deadlock vs. livelock?

Before Next Lecture

Submit review before next lecture

• C. Mohan, et al. ARIES: A Transaction Recovery Method Supporting Fine-

Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. ACM

• Trans. Database Syst. 1992.

34