1 / 21

### Concurrency Control in Database Transactions: Locking Protocols and Serializability ###

This article explores the complexities of concurrency control within database transactions, focusing on the Two-Phase Locking Protocol (2PL). It discusses how to enforce consistency and avoid conflicts through serializable schedules while preventing cycles in the precedence graph. Detailed examples illustrate how transactions acquire and release locks, ensuring well-behaved transactions through careful management of locking protocols. Additionally, the article addresses issues of deadlock and starvation in transaction scheduling, comparing 2PL to other concurrency control mechanisms for improved performance. ###

lysandra-cannon
Télécharger la présentation

### Concurrency Control in Database Transactions: Locking Protocols and Serializability ###

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. Concurrency Control T1 T2 … Tn DB (consistency constraints)

  3. Enforce Conflict Serializable Schedules Prevent cycles in precedence graph from occurring T1 T2 ….. Tn Scheduler DB

  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

  5. Well behaved transactions Ti: … li(A) … pi(A) … ui(A) ...

  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

  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

  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

  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

  10. Ti = ……. li(A) ………... ui(A) ……... no unlocks no locks

  11. # locks held by Ti Time Growing Shrinking Phase Phase

  12. What happens to a transaction which tries to lock an item but failed?

  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

  14. 2PL  conflict-serializable schedules? To help in proof: Definition Shrink(Ti) = SH(Ti) = first unlock action of Ti

  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) …

  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

  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)

  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

  19. Are schedules from 2PL transactions deadlock free? 2PL and Deadlock

  20. 2PL and Possible Schedules • Does 2PL allow all possible conflict serializable schedules?

  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

More Related