1 / 23

Concurrency Control

Concurrency Control. Example Schedules. Transactions: T1: transfers $50 from A to B T2: transfers 10% of A to B. Example 1: a “serial” schedule. T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B). T2 read(A) tmp = A*0.1 A = A – tmp write(A)

quanda
Download Presentation

Concurrency Control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Concurrency Control

  2. Example Schedules Transactions: T1: transfers $50 from A to B T2: transfers 10% of A to B Example 1: a “serial” schedule T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Constraint: The sum of A+B must be the same Before: 100+50 =150, consistent After: 45+105

  3. Example Schedule • Another “serial” schedule: T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Before: 100+50 =150, consistent After: 40+110 Consistent but not the same as previous schedule.. Either is OK!

  4. Example Schedule (Cont.) Another “good” schedule: T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Effect: BeforeAfter A 100 45 B 50 105 Same as one of the serial schedules Serializable

  5. Example Schedules (Cont.) A “bad” schedule T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Before: 100+50 = 150 After: 50+60 = 110 !! Not consistent Non Serializable

  6. T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Equivalence by Swapping

  7. T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Equivalence by Swapping

  8. T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Equivalence by Swapping

  9. Example Schedules (Cont.) A “bad” schedule T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Can’t move Y below X read(B) and write(B) conflict Y X

  10. Example Schedules (Cont.) A “bad” schedule T1 read(A) A = A -50 write(A) read(B) B=B+50 write(B) T2 read(A) tmp = A*0.1 A = A – tmp write(A) read(B) B = B+ tmp write(B) Can’t move Y below X read(B) and write(B) conflict Y Other options don’t work either X So: Not Conflict Serializable

  11. Lock instructions • New instructions - lock-S: shared lock request - lock-X: exclusive lock request - unlock: release previously held lock Example: T1 T2 lock-X(B) read(B) B B-50 write(B) unlock(B) lock-X(A) read(A) A A + 50 write(A) unlock(A) lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B)

  12. Locking Issues • No xction proceeds: Deadlock - T1 waits for T2 to unlock A - T2 waits for T1 to unlock B Rollback transactions Can be costly...

  13. Locking Issues • Does not ensure serializability by itself: T1 lock-X(B) read(B) B B-50 write(B) unlock(B) lock-X(A) read(A) A A + 50 write(A) unlock(A) T2 lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B) T2 displays 50 less!!

  14. 2PhaseLocking • Example: T1 in 2PL Growing phase Shrinking phase

  15. 2PL Issues • 2PL does not prevent deadlock • > 2 xctions involved? - Rollbacks expensive

  16. Strict 2PL Strict 2PL will not allow that

  17. Dealing with Deadlocks • How do you detect a deadlock? • Wait-for graph • Directed edge from Ti to Tj • Ti waiting for Tj T2 T4 T1 T3 Suppose T4 requests lock-S(Z)....

  18. The highest level in the example hierarchy is the entire database. The levels below are of type area, file or relation and record in that order. Example of Granularity Hierarchy

  19. The compatibility matrix for all lock modes is: S IX S IX X IS  IS      IX     S      S IX      X      Compatibility Matrix with Intention Lock Modes requestor holder

  20. Parent Child can be locked in locked in IS IX S SIX X P IS, S IS, S, IX, X, SIX [S, IS] not necessary X, IX, [SIX] none C

  21. T1(IS) , T2(IX) T2(X) T1(S) Example R1 t1 t4 t2 t3

  22. Examples T1(IX) T1(IS) R R T1(IX) t3 T1(S) t4 t2 t1 t3 t4 t2 t1 T1(X) f4.2 f4.2 f2.2 f2.1 f4.2 f4.2 f2.2 f2.1 T1(SIX) Can T2 access object f2.2 in X mode? What locks will T2 get? R T1(IX) t3 t4 t2 t1 T1(X) f4.2 f4.2 f2.2 f2.1

  23. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö Ö S Ö Ö Ö X Examples • T1 scans R, and updates a few tuples: • T1 gets an SIX lock on R, then repeatedly gets an S lock on tuples of R, and occasionally upgrades to X on the tuples. • T2 uses an index to read only part of R: • T2 gets an IS lock on R, and repeatedly gets an S lock on tuples of R. • T3 reads all of R: • T3 gets an S lock on R. • OR, T3 could behave like T2; can use lock escalation to decide which.

More Related