Transactions – Concurrency Control

1. Transactions – Concurrency Control

2. Concurrency Control Algorithms • Two-phase locking • Optimistic concurrency control • Timestamp ordering

3. Simple Concurrency Control • Acquire lock when object needed. • Release when transaction no longer needs object. • Why does this not work?

4. Two-Phase Locking • Growing phase – acquire locks • Shrinking phase – release locks • Transaction can acquire a lock for x only once

5. Tb x = x + 2 y = y + 3 z = z + 4 Ta x = x + 1 y = y + 2 Acquire x.lock r(x)0 Acquire x.lock w(x)1 Release x.lock r(x)1 w(x)3 Release x.lock Acquire y.lock r(y)0 w(y)3 Can Ta commit at this point? Release y.lock Acquire z.lock Acquire y.lock r(y)3 w(y)5 r(z)0 Release y.lock w(z)4 Release z.lock

6. Strict Two-Phase Locking • Release locks when transaction completed

7. Transaction T : Transaction U : balance = b.getBalance() balance = b.getBalance() b.setBalance(bal*1.1) b.setBalance(bal*1.1) a.withdraw(bal/10) c.withdraw(bal/10) Operations Locks Operations Locks openTransaction bal = b.getBalance() lock B openTransaction b.setBalance(bal*1.1) bal = b.getBalance() waits for T ’s a.withdraw(bal/10) lock A lock on B closeTransaction unlock A , B lock B b.setBalance(bal*1.1) c.withdraw(bal/10) lock C closeTransaction unlock B , C

8. Deadlock Management • Prevention – Lock everything atomically • Detection – Finding cycles in graphs • Time-outs – Lock given for limited amount of time

9. Held by Waits for A T U U T B Waits for Held by Deadlock Detection

10. Optimistic Concurrency Control • Each transaction makes changes in its private workspace • Working phase • Validation phase • Update phase

11. Working Validation Update T 1 Earlier committed transactions T 2 T 3 Transaction T being validated v active 1 Later active active 2 transactions Optimistic Concurrency Control

12. Validation in Optimistic Concurrency • Backward validation • Compare my version of objects with version of objects committed. • Forward validation • Compare my version of objects with use of object by transactions that are ongoing.

13. Analysis • Simple • Deadlock free • Maximum parallelism possible • May cause many rollbacks

14. Timestamp Ordering • Each transaction T • Has a timestamp (T.ts) • Each operation in T has same timestamp (T.ts) • Each object x has • read timestamp x.rts • write timestamp x.wts • Scheduler ensures all transactions have unique timestamps • If T.ts < T.ts then T must appear before T in the schedule

15. TO Algorithm – read request Scheduler receives r(x)from transactionT if T.ts<x.wts then reject r(x) roll T back else execute r(x) x.rts = max(x.rts , T.ts)

16. TO Algorithm – write request Scheduler receives w(x)vfrom transactionT if (T.ts<x.rts) or (T.ts<x.wts) then reject w(x)v roll T back else execute w(x)v x.wts = max(x.wts , T.ts)

17. Analysis Timestamp Order • Deadlock-free • Schedule is serializable