CS 764: Topics in Database Management Systems Lecture 6: Granularity - - PowerPoint PPT Presentation

▶
cs 764 topics in database management systems lecture 6
SMART_READER_LITE
LIVE PREVIEW

CS 764: Topics in Database Management Systems Lecture 6: Granularity - - PowerPoint PPT Presentation

Feb 21, 2024 373 likes •884 views

CS 764: Topics in Database Management Systems Lecture 6: Granularity of Locks Xiangyao Yu 9/23/2020 1 Discussion Highlights SELECT JOB.title, count(*) |EMP| = 10000 tuples FROM JOB, EMP, DEPT |DEPT| = 100 tuples WHERE JOB.jid =

slide-1
SLIDE 1

Xiangyao Yu 9/23/2020

CS 764: Topics in Database Management Systems Lecture 6: Granularity of Locks

1

slide-2
SLIDE 2

Discussion Highlights

SELECT JOB.title, count(*) FROM JOB, EMP, DEPT WHERE JOB.jid = EMP.jid AND EMP.did = DEPT.did AND DEPT.location=“Madison” GROUP BY JOB.title

2

Consider only nested loop join and only the cost in terms of the # comparisons in the join (note that which relation is inner vs. outer in a join does not matter in this case) Q1: If only one department is in Madison, what’s the cheapest plan? (hint: group-by can be partially pushed down) Q2 [optional]: If all departments are in Madison, what’s the cheapest plan? |EMP| = 10000 tuples |DEPT| = 100 tuples |JOB| = 10 tuples

* assuming one-on-one mapping between jid and title

slide-3
SLIDE 3

Discussion Highlights – One Dept. in Madison

SELECT JOB.title, count(*) FROM JOB, EMP, DEPT WHERE JOB.jid = EMP.jid AND EMP.did = DEPT.did AND DEPT.location=“Madison” GROUP BY JOB.title

3

|EMP| = 10000 tuples |DEPT| = 100 tuples |JOB| = 10 tuples

EMP DEPT

⋈

JOB

⋈

Group by EMP.jid and EMP.did EMP JOB

⋈

DEPT Group by EMP.jid and EMP.did Group by jid Group by title 1000 1 [1000x1] 10 10 [10x10] 1000 10 [1000x10] 1 [1000x1]

* assuming one-on-one mapping between jid and title

⋈

slide-4
SLIDE 4

Discussion Highlights – All Dept. in Madison

SELECT JOB.title, count(*) FROM JOB, EMP, DEPT WHERE JOB.jid = EMP.jid AND EMP.did = DEPT.did AND DEPT.location=“Madison” GROUP BY JOB.title

4

|EMP| = 10000 tuples |DEPT| = 100 tuples |JOB| = 10 tuples

EMP DEPT

⋈

JOB

⋈

Group by EMP.jid and EMP.did EMP JOB

⋈

DEPT

⋈

Group by EMP.jid and EMP.did Group by jid Group by title 1000 100 [1000x100] 10 10 [10x10] 1000 10 [1000x10] 100 [1000x100]

* assuming one-on-one mapping between jid and title

slide-5
SLIDE 5

Today’s Paper: Granularity of Locks

Modelling in Data Base Management Systems 1976

5

slide-6
SLIDE 6

Agenda

6

Transaction basics Locking Degree of consistency

slide-7
SLIDE 7

ACID Properties in Transactions

7

Atomicity: Either all operations occur, or nothing occurs (all or nothing) Consistency: Integrity constraints are satisfied Isolation: How operations of transactions interleave Durability: A transaction’s updates persist when system fails This lecture touches A, C, and I

slide-8
SLIDE 8

Locking Granularity

8

Locks are a critical part of concurrency control Choosing a locking granularity

  • Entire database
  • Relation
  • Records …
slide-9
SLIDE 9

Locking Granularity

9

Locks are a critical part of concurrency control Choosing a locking granularity

  • Entire database
  • Relation
  • Records …

Goal: high concurrency and low cost

Increasing concurrency Increasing overhead when many records are accessed

slide-10
SLIDE 10

Locking Granularity

10

Locks are a critical part of concurrency control Choosing a locking granularity

  • Entire database
  • Relation
  • Records …

Goal: high concurrency and low cost Solution: Hierarchical locks

Increasing concurrency Increasing overhead when many records are accessed

slide-11
SLIDE 11

Hierarchical Locks

11

DB | Areas | Files | Records DB | Areas / \ Files Indices \ / Records Lock a high-level node if a large number of records are accessed

  • All descendants are implicitly locked in the same mode
slide-12
SLIDE 12

Hierarchical Locks

12

DB | Areas | Files | Records DB | Areas / \ Files Indices \ / Records Lock a high-level node if a large number of records are accessed

  • All descendants are implicitly locked in the same mode
  • Intention lock to avoid conflict with implicit locks
slide-13
SLIDE 13

Locking Modes

13

Basic locking modes

  • S: Shared lock
  • X: Exclusive lock
slide-14
SLIDE 14

Locking Modes

14

Basic locking modes

  • S: Shared lock
  • X: Exclusive lock

Intention modes:

  • IS: Intention to share
  • IX: Intention to acquire X lock below the lock hierarchy
  • SIX: Read large portions and update a few parts
slide-15
SLIDE 15

Locking Modes

15

Basic locking modes

  • S: Shared lock
  • X: Exclusive lock

Intention modes:

  • IS: Intention to share
  • IX: Intention to acquire X lock below the lock hierarchy
  • SIX: Read large portions and update a few parts

Example: read record (T1)

DB | Areas | Files | Records

IS IS IS S

slide-16
SLIDE 16

Locking Modes

16

Basic locking modes

  • S: Shared lock
  • X: Exclusive lock

Intention modes:

  • IS: Intention to share
  • IX: Intention to acquire X lock below the lock hierarchy
  • SIX: Read large portions and update a few parts

Example: read record (T1) update record (T2)

DB | Areas | Files | Records

IS IS IS S IX IX IX X

slide-17
SLIDE 17

Locking Modes

17

Basic locking modes

  • S: Shared lock
  • X: Exclusive lock

Intention modes:

  • IS: Intention to share
  • IX: Intention to acquire X lock below the lock hierarchy
  • SIX: Read large portions and update a few parts

Example: read record (T1) update record (T2) scan + occasional updates (T3)

DB | Areas | Files | Records

IS IS IS S IX IX IX X IX IX SIX lock specific records in X mode

slide-18
SLIDE 18

Lock Compatibility

IS IX S SIX X IS Y Y Y Y N IX Y Y N N N S Y N Y N N SIX Y N N N N X N N N N N

18

Increasing lock strength

X | SIX / \ S IX \ / IS | NL

Most privileged least privileged

slide-19
SLIDE 19

Lock Compatibility

IS IX S SIX X IS Y Y Y Y N IX Y Y N N N S Y N Y N N SIX Y N N N N X N N N N N

19

Increasing lock strength

X | SIX / \ S IX \ / IS | NL

Most privileged least privileged

slide-20
SLIDE 20

Lock Compatibility

IS IX S SIX X IS Y Y Y Y N IX Y Y N N N S Y N Y N N SIX Y N N N N X N N N N N

20

Increasing lock strength

X | SIX / \ S IX \ / IS | NL

Most privileged least privileged

slide-21
SLIDE 21

Rules for Lock Requests

21

  • Before requesting S or IS on a node, all ancestor nodes of the

requested node must be held in IS or IX

slide-22
SLIDE 22

Rules for Lock Requests

22

  • Before requesting S or IS on a node, all ancestor nodes of the

requested node must be held in IS or IX

  • Before requesting X, SIX, or IX on a node, all ancestor nodes of the

requesting node must be held in SIX or IX

slide-23
SLIDE 23

Rules for Lock Requests

23

  • Before requesting S or IS on a node, all ancestor nodes of the

requested node must be held in IS or IX

  • Before requesting X, SIX, or IX on a node, all ancestor nodes of the

requesting node must be held in SIX or IX

  • Locks requested root to leaf
  • Locks released leaf to root or any order at the end of the

transaction

slide-24
SLIDE 24

Summary of Lock Granularity

Implicit lock

  • Desc. lock
  • Anc. lock (DAG)

IS (Intention share) None S or IS IX or IS, at least one parent IX (Intention exclusive) None X, SIX, IX, IS SIX or IX, all parents S (Share) S on all desc

  • IX or IS, at least one

parent SIX (Shared and intention exclusive) S on all desc X, SIX, IX SIX or IX, all parents X (Exclusive) X o all desc

  • SIX or IX, all parents

24

slide-25
SLIDE 25

Summary of Lock Granularity

Implicit lock

  • Desc. lock
  • Anc. lock (DAG)

IS (Intention share) None S or IS IX or IS, at least one parent IX (Intention exclusive) None X, SIX, IX, IS SIX or IX, all parents S (Share) S on all desc

  • IX or IS, at least one

parent SIX (Shared and intention exclusive) S on all desc X, SIX, IX SIX or IX, all parents X (Exclusive) X o all desc

  • SIX or IX, all parents

25

slide-26
SLIDE 26

Summary of Lock Granularity

Implicit lock

  • Desc. lock
  • Anc. lock (DAG)

IS (Intention share) None S or IS IX or IS, at least one parent IX (Intention exclusive) None X, SIX, IX, IS SIX or IX, all parents S (Share) S on all desc

  • IX or IS, at least one

parent SIX (Shared and intention exclusive) S on all desc X, SIX, IX SIX or IX, all parents X (Exclusive) X o all desc

  • SIX or IX, all parents

26

slide-27
SLIDE 27

Summary of Lock Granularity

Implicit lock

  • Desc. lock
  • Anc. lock (DAG)

IS (Intention share) None S or IS IX or IS, at least one parent IX (Intention exclusive) None X, SIX, IX, IS SIX or IX, all parents S (Share) S on all desc

  • IX or IS, at least one

parent SIX (Shared and intention exclusive) S on all desc X, SIX, IX SIX or IX, all parents X (Exclusive) X o all desc

  • SIX or IX, all parents

27

slide-28
SLIDE 28

Dynamic Lock Graphs

28

The lock graph can be dynamic (e.g., indices created, records inserted) Must deal with Phantoms

slide-29
SLIDE 29

Phantom Effect

29

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2

Age Rating 80 1 75 1 90 2 85 2 Sailors

slide-30
SLIDE 30

Phantom Effect

30

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1

Age Rating 80 1 75 1 90 2 85 2 Sailors

slide-31
SLIDE 31

Phantom Effect

31

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1 T2 inserts a tuple with (age:99, rating:1)

Age Rating 80 1 75 1 90 2 85 2 99 1 Sailors

slide-32
SLIDE 32

Phantom Effect

32

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1 T2 inserts a tuple with (age:99, rating:1) T2 deletes oldest sailor with rating 2

Age Rating 80 1 75 1 90 2 85 2 99 1 Sailors

slide-33
SLIDE 33

Phantom Effect

33

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1 T2 inserts a tuple with (age:99, rating:1) T2 deletes oldest sailor with rating 2 T2 commits

Age Rating 80 1 75 1 85 2 99 1 Sailors

slide-34
SLIDE 34

Phantom Effect

34

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1 T2 inserts a tuple with (age:99, rating:1) T2 deletes oldest sailor with rating 2 T2 commits T1 locks oldest sailor in rating 2

Age Rating 80 1 75 1 85 2 99 1 Sailors

slide-35
SLIDE 35

Phantom Effect

35

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 T1 locks oldest sailor in rating 1 T2 inserts a tuple with (age:99, rating:1) T2 deletes oldest sailor with rating 2 T2 commits T1 locks oldest sailor in rating 2 T1 commits. Output: (80,1), (85, 2)

Age Rating 80 1 75 1 85 2 99 1 Sailors

slide-36
SLIDE 36

Phantom Effect

36

T1: Find oldest sailors for ratings 1 and 2 T2: Insert (age:99, rating:1) and delete oldest sailor with rating 2 Output: (80,1), (85, 2) Different from all sequential execution output

  • T1 -> T2. Output: (80, 1), (90, 2)
  • T2 -> T1. Output: (99, 1), (85, 2)

Age Rating 80 1 75 1 85 2 99 1 Sailors Phantom

slide-37
SLIDE 37

Solution to Phantoms

37

Observation: Inserts and deletes are writes to the index; lookups are reads to the index Can lock the index in X or S mode Optimization: lock intervals and predicate locking

  • E.g., lock age=80 and the interval of age > 80 (prevent age 99 from

inserted)

slide-38
SLIDE 38

Degree of Consistency (Isolation)

38

How can transactions interleave? One extreme: concurrent execution produces the same results as some serial execution (serializability)

  • Limited concurrency and performance
  • Intuitive and easy to reason about

Another extreme: transaction operations can arbitrarily interleave

slide-39
SLIDE 39

Degree of Consistency (Isolation)

39

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-40
SLIDE 40

Degree of Consistency (Isolation)

40

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-41
SLIDE 41

Degree of Consistency (Isolation)

41

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-42
SLIDE 42

Degree of Consistency (Isolation)

42

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-43
SLIDE 43

Degree of Consistency (Isolation)

43

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-44
SLIDE 44

Degree of Consistency (Isolation)

44

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-45
SLIDE 45

Degree of Consistency (Isolation)

45

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No Serializable W->W W->R R->W Degree 2 Long-X Short-R No No Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes None

slide-46
SLIDE 46

Degree of Consistency (Isolation)

46

Locks Non- Recoverable Dirty Reads Non-repeatable

  • r fuzzy Reads

Phantom SQL Isolation level Dependenc y Degree 3 Long-X Long-R No No No No Serializable W->W W->R R->W No No No Yes Repeatable reads Degree 2 Long-X Short-R No No Yes Yes Read committed W->W W->R Degree 1 Long-X No Yes Yes Yes Read uncommitted W->W Degree 0 Short-X Yes Yes Yes Yes None

slide-47
SLIDE 47

Two-Phase Locking

47

A transaction is two phase if it does not lock an entity after unlocking some entity

  • Growing phase: acquiring locks
  • Shrinking phase: releasing locks

Two-phase locking (2PL) guarantees serializability

slide-48
SLIDE 48

Two-Phase Locking

48

A transaction is two phase if it does not lock an entity after unlocking some entity

  • Growing phase: acquiring locks
  • Shrinking phase: releasing locks

Two-phase locking (2PL) guarantees serializability Strict 2PL: 2PL + all exclusive locks released after transaction commits

  • Strict 2PL guarantees ACA (Avoiding Cascading Aborts)
slide-49
SLIDE 49

Q/A – Granularity of Locks

49

Multi-granularity locks used in modern database? Research papers focus on tuple-level locking? SQL vs. NoSQL regarding locking? How is the action of placing a lock itself thread-safe? Implementation of Internal locking? (checkout next-key locking)

slide-50
SLIDE 50

Before Next Lecture

Submit review for

  • H. T. Kung, John T. Robinson, On Optimistic Methods for Concurrency
  • Control. ACM Trans. Database Syst. 1981.

50