1 / 21

Concurrency Control I

Concurrency Control I. Concurrency Control. T1 T2 … Tn. DB (consistency constraints). Enforce Conflict Serializable Schedules. P revent cycles in precedence graph from occurring T 1 T 2 ….. T n. Scheduler. DB. A locking protocol.

alena
Download Presentation

Concurrency Control I

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 I Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  2. Concurrency Control T1 T2 … Tn DB (consistency constraints) Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  3. Enforce Conflict Serializable Schedules Prevent cycles in precedence graph from occurring T1 T2 ….. Tn Scheduler DB Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  4. A locking protocol • For transaction i • Use li to lock an item • Use ui to unlock the lock enforced by transaction i T1 T2 lock table scheduler Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  5. Well behaved transactions Ti: … li(A) … pi(A) … ui(A) ... Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  6. T1:l1(A); read (A); u1(A); l1(B); read (B); u1(B); display(A+B) Sufficient to guarantee serializability ? Example of a transaction performing locking Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  7. Example: T2: Read(A) T3: Read(A) A  A+100 A  A2 Write(A) Write(A) Read(B) Read(B) B  B+100 B B2 Write(B) Write(B) Constraint: A=B Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  8. Schedule A A B T2 T3 25 25 l1(A);Read(A) A A+100;Write(A);u1(A) 125 l2(A);Read(A) A Ax2;Write(A);u2(A) 250 l2(B);Read(B) B Bx2;Write(B);u2(B) 50 l1(B);Read(B) B B+100;Write(B);u1(B) 150 250 150 Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  9. Phase 1: Growing Phase transaction may obtain locks transaction may not release locks Phase 2: Shrinking Phase transaction may release locks transaction may not obtain locks Two-Phase Locking Protocol Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  10. Ti = ……. li(A) ………... ui(A) ……... no unlocks no locks Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  11. # locks held by Ti Time Growing Shrinking Phase Phase Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  12. What happens to a transaction which tries to lock an item but failed? Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  13. Schedule B T2 T3 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A); l2(B) Read(B);B B+100 Write(B); u1(B) l2(B); u2(A);Read(B) B Bx2;Write(B);u2(B); delayed Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  14. 2PL  conflict-serializable schedules? To help in proof: Definition Shrink(Ti) = SH(Ti) = first unlock action of Ti Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  15. First: Ti  Tj in S  SH(Ti) <S SH(Tj) Proof: Ti  Tj means that S = … pi(A) … ui(A) … lj(A) ... qj(A) … Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  16. Then: (1) Assume P(S) has cycle T1 T2 …. Tn T1 (2) By lemma: SH(T1) <SH(T2) < ... <SH(T1) (3) Impossible, so P(S) acyclic (4)  S is conflict serializable Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  17. To handle a deadlock one of T4 or T5 must be rolled back and its locks released. Deadlock T4 T5 l3(B) read(B) write(B) l4(A) read(A) l4(B) l3(A) Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  18. A transaction does not get its turn for a long time Example: A transaction may be waiting for a lock on an item, while a sequence of other transactions request and are granted an lock on the same item. The same transaction is repeatedly rolled back due to deadlocks. Concurrency control manager can be designed to prevent starvation. Starvation Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  19. Are schedules from 2PL transactions deadlock free? 2PL and Deadlock Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  20. 2PL and Possible Schedules • Does 2PL allow all possible conflict serializable schedules? Yan Huang - CSCI5330 Database Implementation –Concurrency Control

  21. Beyond this simple 2PL protocol, it is all a matter of improving performance and allowing more concurrency…. • Shared locks • Multiple granularity • Inserts, deletes and phantoms • Other types of C.C. mechanisms Yan Huang - CSCI5330 Database Implementation –Concurrency Control

More Related