Concurrency Control Instructor: Matei Zaharia cs245.stanford.edu - PowerPoint PPT Presentation

Concurrency Control Instructor: Matei Zaharia cs245.stanford.edu

Outline What makes a schedule serializable? Conflict serializability Precedence graphs Enforcing serializability via 2-phase locking » Shared and exclusive locks » Lock tables and multi-level locking Optimistic concurrency with validation Concurrency control + recovery Beyond serializability CS 245 2

Which Objects Do We Lock? Tuple A Disk Table A block Tuple B A Tuple C Table B Disk block ... ... B ? ... Idea: Multi-level locking DB DB DB CS 245 3

Example R 1 t 1 t 4 t 2 t 3 CS 245 4

Example T 1 (IS) R 1 t 1 t 4 t 2 t 3 T 1 (S) CS 245 5

Example T 1 (IS) , T 2 (S) R 1 t 1 t 4 t 2 t 3 T 1 (S) CS 245 6

Example 2 T 1 (IS) R 1 t 1 t 4 t 2 t 3 T 1 (S) CS 245 7

Example 2 T 1 (IS) , T 2 (IX) R 1 t 1 t 4 t 2 t 3 T 2 (X) T 1 (S) CS 245 8

Example 3 T 1 (IS) , T 2 (S), T 3 (IX)? R 1 t 1 t 4 t 2 t 3 T 1 (S) CS 245 9

Multiple Granularity Locks compat Requestor IS IX S SIX X T T T T F IS Holder IX T T F F F S T F T F F SIX T F F F F F F F F F X CS 245 10

Rules Within A Transaction Parent Child can be locked locked in by same transaction in IS IS, S P IX IS, S, IX, X, SIX none S X, IX, SIX SIX C none X CS 245 11

Multi-Granularity 2PL Rules 1. Follow multi-granularity compat function 2. Lock root of tree first, any mode 3. Node Q can be locked by T i in S or IS only if parent(Q) locked by T i in IX or IS 4. Node Q can be locked by T i in X, SIX, IX only if parent(Q) locked by T i in IX, SIX 5. T i is two-phase 6. T i can unlock node Q only if none of Q’s children are locked by T i CS 245 12

Exercise: Can T 2 access object f 2.2 in X mode? What locks will T 2 get? T 1 (IX) R 1 t 1 t 4 T 1 (IX) t 2 t 3 f 2.1 f 2.2 f 3.1 f 3.2 T 1 (X) CS 245 13

Exercise: Can T 2 access object f 2.2 in X mode? What locks will T 2 get? T 1 (IX) R 1 t 1 t 4 T 1 (X) t 2 t 3 f 2.1 f 2.2 f 3.1 f 3.2 CS 245 14

Exercise: Can T 2 access object f 3.1 in X mode? What locks will T 2 get? T 1 (IS) R 1 t 1 t 4 T 1 (S) t 2 t 3 f 2.1 f 2.2 f 3.1 f 3.2 CS 245 15

Exercise: Can T 2 access object f 2.2 in S mode? What locks will T 2 get? T 1 (SIX) R 1 t 1 t 4 T 1 (IX) t 2 t 3 f 2.1 f 2.2 f 3.1 f 3.2 T 1 (X) CS 245 16

Exercise: Can T 2 access object f 2.2 in X mode? What locks will T 2 get? T 1 (SIX) R 1 t 1 t 4 T 1 (IX) t 2 t 3 f 2.1 f 2.2 f 3.1 f 3.2 T 1 (X) CS 245 17

Insert + Delete Operations A ... Z a Insert CS 245 18

Changes to Locking Rules: 1. Get exclusive lock on A before deleting A 2. When T i inserts an object A, T i receives an exclusive lock on A CS 245 19

Still Have Problem: Phantoms Example: relation R (id, name,…) constraint: id is unique key use tuple locking R id name …. o 1 55 Smith o 2 75 Jones CS 245 20

T 1 : Insert <12,Mary,…> into R T 2 : Insert <12,Sam,…> into R T1 T2 l-S 1 (o 1 ) l-S 2 (o 1 ) l-S 1 (o 2 ) l-S 2 (o 2 ) Check Constraint Check Constraint ... ... Insert o 3 [12,Mary,..] Insert o 4 [12,Sam,..] CS 245 21

Solution Use multiple granularity tree Before insert of node N, lock parent(N) in X mode R 1 t 1 t 2 t 3 CS 245 22

Back to Example T 1 : Insert<12,Mary> T 2 : Insert<12,Sam> T 1 T 2 l-X 1 (R) l-X 2 (R) delayed Check constraint Insert<12,Mary> U 1 (R) l-X 2 (R) Check constraint Oops! id=12 already in R! CS 245 23

Instead of Locking R, Can Use Index Nodes for Ranges Example: R Index Index ... 0<id≤100 100<id≤200 ... ... id=2 id=5 id=107 id=109 CS 245 24

How Is Locking Implemented In Practice? Every system is different (e.g., may not even provide conflict serializable schedules) But here is one (simplified) way ... CS 245 25

Sample Locking System 1. Don’t ask transactions to request/release locks: just get the weakest lock for each action they perform 2. Hold all locks until the transaction commits # locks time CS 245 26

Sample Locking System Under the hood: lock manager that keeps track of which objects are locked » E.g. hash table Also need good ways to block transactions until locks are available, and to find deadlocks CS 245 27

Outline What makes a schedule serializable? Conflict serializability Precedence graphs Enforcing serializability via 2-phase locking » Shared and exclusive locks » Lock tables and multi-level locking Optimistic concurrency with validation Concurrency control + recovery Beyond serializability CS 245 28

Validation Approach Transactions have 3 phases: 1. Read » Read all DB values needed » Write to temporary storage » No locking 2. Validate » Check whether schedule so far is serializable 3. Write » If validate OK, write to DB CS 245 29

Key Idea Make validation atomic If the validation order is T 1 , T 2 , T 3 , …, then resulting schedule will be conflict equivalent to S s = T 1 , T 2 , T 3 , … CS 245 30

Implementing Validation System keeps track of two sets: FIN = transactions that have finished phase 3 (write phase) and are all done VAL = transactions that have successfully finished phase 2 (validation) CS 245 31

Example That Validation Must Prevent: ≠ ∅ RS(T 2 )={B} RS(T 3 )={A,B} Ç WS(T 2 )={B,D} WS(T 3 )={C} T 2 T 2 T 3 T 3 start validated validated start time CS 245 32

Example That Validation Must Allow: ≠ ∅ RS(T 2 )={B} RS(T 3 )={A,B} Ç WS(T 2 )={B,D} WS(T 3 )={C} T 2 T 2 T 3 start validated validated T 2 T 3 time finish start phase 3 CS 245 33

Another Thing Validation Must Prevent: RS(T 2 )={A} RS(T 3 )={A,B} WS(T 2 )={D,E} WS(T 3 )={C,D} T 2 T 3 validated validated finish time T 2 CS 245 34

Another Thing Validation Must Prevent: RS(T 2 )={A} RS(T 3 )={A,B} WS(T 2 )={D,E} WS(T 3 )={C,D} T 2 T 3 validated validated finish time T 2 BAD: w 3 (D) w 2 (D) CS 245 35

Another Thing Validation Must Allow: RS(T 2 )={A} RS(T 3 )={A,B} WS(T 2 )={D,E} WS(T 3 )={C,D} T 2 T 3 validated validated finish time T 2 CS 245 36

Validation Rules for T j : when T j starts phase 1: ignore(T j ) ¬ FIN at T j Validation: if Check(T j ) then VAL ¬ VAL ∪ {T j } do write phase FIN ¬ FIN ∪ {T j } CS 245 37

Check(T j ) for T i Î VAL – ignore(T j ) do if (WS(T i ) ∩ RS(T j ) ≠ ∅ or (T i Ï FIN and WS(T i ) ∩ WS(T j ) ≠ ∅ )) then return false return true CS 245 38

start Exercise validate finish U: RS(U)={B} W: RS(W)={A,D} WS(U)={D} WS(W)={A,C} V: RS(V)={B} T: RS(T)={A,B} WS(V)={D,E} WS(T)={A,C} CS 245 39

Is Validation = 2PL? Val Val 2PL 2PL Val 2PL 2PL Val CS 245 40

S: w 2 (y) w 1 (x) w 2 (x) Achievable with 2PL? Achievable with validation? CS 245 41

S: w 2 (y) w 1 (x) w 2 (x) S can be achieved with 2PL: l 2 (y) w 2 (y) l 1 (x) w 1 (x) u 1 (x) l 2 (x) w 2 (x) u 2 (x) u 2 (y) S cannot be achieved by validation: The validation point of T 2 , val 2 , must occur before w 2 (y) since transactions do not write to the database until after validation. Because of the conflict on x, val 1 < val 2 , so we must have something like: S: val 1 val 2 w 2 (y) w 1 (x) w 2 (x) With the validation protocol, the writes of T 2 should not start until T 1 is all done with writes, which is not the case. CS 245 42

Validation Subset of 2PL? Possible proof (Check!): » Let S be validation schedule » For each T in S insert lock/unlocks, get S’: • At T start: request read locks for all of RS(T) • At T validation: request write locks for WS(T); release read locks for read-only objects • At T end: release all write locks » Clearly transactions well-formed and 2PL » Must show S’ is legal (next slide) CS 245 43

Validation Subset of 2PL? Say S’ not legal (due to w-r conflict): S’: ... l1(x) w2(x) r1(x) val1 u1(x) ... » At val1: T2 not in Ignore(T1); T2 in VAL » T1 does not validate: WS(T2) Ç RS(T1) ¹ Æ » contradiction! Say S’ not legal (due to w-w conflict): S’: ... val1 l1(x) w2(x) w1(x) u1(x) ... » Say T2 validates first (proof similar if T1 validates first) » At val1: T2 not in Ignore(T1); T2 in VAL » T1 does not validate: T2 Ï FIN AND WS(T1) Ç WS(T2) ¹ Æ ) » contradiction! CS 245 44

Is Validation = 2PL? Val Val 2PL 2PL Val 2PL 2PL Val CS 245 45

When to Use Validation? Validation performs better than locking when: » Conflicts are rare » System resources are plentiful » Have tight latency constraints CS 245 46

Outline What makes a schedule serializable? Conflict serializability Precedence graphs Enforcing serializability via 2-phase locking » Shared and exclusive locks » Lock tables and multi-level locking Optimistic concurrency with validation Concurrency control + recovery Beyond serializability CS 245 48

Concurrency Control & Recovery Example: T j T i … … w j (A) … r i (A) … Commit T i … … Abort T j avoided by Non-persistent commit (bad!) recoverable schedules CS 245 49

Concurrency Control & Recovery Example: T j T i … … w j (A) … r i (A) … w i (B) … … Abort T j [Commit T i ] avoided by avoids-cascading Cascading rollback (bad!) -rollback (ACR) schedules CS 245 50