Concurrency Control III

1. Concurrency Control III

2. General Overview • Relational model - SQL • Formal & commercial query languages • Functional Dependencies • Normalization • Physical Design • Indexing • Query Processing and Optimization • Transaction Processing and CC

3. Execution of transaction Tiis done in three phases. 1. Read and execution phase: Tireads all values and makes copies to local variables (private workspace.) Ti writes only to temporary local variables. No locking. 2. Validation phase: Transaction Ti performs a ``validation test'' to determine if local variables can be written without violating serializability. 3. Write phase: If Ti is validated, the updates are applied to the database; otherwise, Ti is rolled back. optimistic concurrency control: transaction executes fully in the hope that all will go well during validation Validation-Based Protocol

4. Each transaction Ti has 3 timestamps Start(Ti) : the time when Ti started its execution Validation(Ti): the time when Ti entered its validation phase Finish(Ti) : the time when Ti finished its write phase Serializability order is based on Validation(Ti). Key idea: validation is atomic! Validation-Based Protocol (Cont.)

5. Validation-Based Protocol To implement validation, system keeps the following sets: • FIN = transactions that have finished phase 3 (and are all done) • VAL = transactions that have successfully finished phase 2 (validation) • For each transaction the Read and Write Sets

6. Example of what validation must prevent: RS(T1)={B} RS(T2)={A,B} WS(T1)={B,D} WS(T2)={C} =   T1 validated T2 validated T1 start T2 start time T2 validation will fail!

7. Example of what validation must allow: RS(T1)={B} RS(T2)={A,B} WS(T1)={B,D} WS(T2)={C} =   T1 validated T2 validated T1 start T2 start T2 start T1 finish phase 3 time

8. BAD: w2(D) w1(D) Another thing validation must prevent: RS(T1)={A} RS(T2)={A,B} WS(T1)={D,E} WS(T2)={C,D} T1 validated T2 validated finish T1 finish T2 time

9. Another thing validation must allow: RS(T1)={A} RS(T2)={A,B} WS(T1)={D,E} WS(T2)={C,D} T1 validated T2 validated finish T1 finish T1 time

10. Validation rules for Tj: (1) When Tj starts phase 1: IGNORE(Tj)  FIN (2) at Tj Validation: if check (Tj) then [ VAL  VAL U {Tj}; do write phase; FIN  FIN U {Tj} ]

11. All transactions that either validated or finished after the start of Tj Check (Tj): For Ti  VAL - IGNORE (Tj) DO IF [ WS(Ti)  RS(Tj)   OR Ti  FIN ] THEN RETURN false; RETURN true; Is this check too restrictive ?

12. Improving Check(Tj) For Ti  VAL - IGNORE (Tj) DO IF [ WS(Ti)  RS(Tj)   OR (Ti  FIN AND WS(Ti)  WS(Tj)  )] THEN RETURN false; RETURN true;

13. Example: start validate finish U: RS(U)={B} WS(U)={D} W: RS(W)={A,D} WS(W)={A,C} V: RS(V)={B} WS(V)={D,E} T: RS(T)={A,B} WS(T)={A,C} U,T,V successful; W abort and roll back

14. Overheads in Optimistic CC • Must record read/write activity in ReadSet and WriteSet per Xact. • Must create and destroy these sets as needed. • Must check for conflicts during validation, and must make validated writes ``global’’. • Critical section can reduce concurrency. • Scheme for making writes global can reduce clustering of objects. • Optimistic CC restarts Xacts that fail validation. • Work done so far is wasted; requires clean-up.

15. ``Optimistic’’ 2PL • If desired, we can do the following: • Set S locks as usual. • Make changes to private copies of objects. • Obtain all X locks at end of Xact, make writes global, then release all locks. • In contrast to previous Optimistic CC protocols, this scheme results in Xacts being blocked, waiting for locks. • However, no validation phase, no restarts (modulo deadlocks).

16. Isolation Level DirtyRead Unrepeatable Read Phantom Problem Read Uncommitted Maybe Maybe Maybe Read Committed No Maybe Maybe Repeatable Reads No No Maybe Serializable No No No Transaction Support in SQL-92 • Each transaction has an access mode, a diagnostics size, and an isolation level.