Chapter 16

1. Chapter 16 Concurrency Control CS157b Tom Mensch

2. Motivation: Bank Example • One bank account with $100 balance • Two different ATMs both trying to withdraw $100 from the same account at exactly the same time • Both ATMs check the balance of the account and see it’s $100

3. Bank Example Continued • Since the balance is enough, both ATMs give out $100 • After both transactions complete the balance is -$100 • One of the transactions should have been denied

4. Concurrent Transactions • Problem: How can data be accessed by multiple transactions without compromising data integrity? • Solution: Only allow a transaction to access data if it holds a “lock” on it.

5. Transactions with Locking • Both ATMs try to “lock” the account • One succeeds and withdraws $100 • The second ATM has to wait until the first ATM releases it’s lock before it can proceed • Now when the second ATM tries to withdraw the $100 it cannot because the balance is $0

6. Locks • Shared - transaction wants read access to data. • Exclusive - transaction wants both read and write access to data.

7. shared-mode lock • Multiple transactions can have shared-mode locks on the same data. • Ex: Transaction T1 has shared-mode lock on data A. Transaction T2 requests a shared-mode lock on data A; the request is granted.

8. exclusive-mode lock • Only one transaction can have an exclusive-mode lock on a data item. • Ex: Transaction T1 has exclusive-mode lock on data A. Transaction T2 requests a exclusive-mode lock on data A; the request is denied.

9. Lock-compatibility • Shared - other shared-mode lock requests granted. • Exclusive - all other lock requests denied.

10. Unlocking data • Transaction must unlock data when done accessing it. • Shared-mode locks wait for exclusive-mode locks to be released • Exclusive-mode locks wait for all other locks be be released

11. Indefinite postponement? • Can an exclusive-mode lock be indefinitely postponed (starved) by an infinite number of shared-mode lock requests? • No. Because the concurrency-control manager will make shared-mode requests wait if an exclusive-mode is already waiting for a shared-mode lock to be released.

12. Balance Transfer Example • Bank account A has a $100 balance • Bank account B has a $200 balance • Transaction T1 transfers $50 from account B to account A • T1 locks A, withdraws $50 and unlocks A • T1 then locks B, adds $50 and unlocks B

13. Bank Transfer Continued • Transaction T2 wants to find out the sum of the balances of accounts A and B • T2 asks for a lock on both accounts after T1 has released it’s lock on A but before T1 locks B • T1 must now wait for T2 to complete before adding the $50 to account A

14. Bank Transfer Continued • The result of T2 is $250 which is incorrect • Why? • T1 should have held it’s lock on A longer

15. Deadlocks • T1 requests exclusive lock on A. Granted • T2 requests exclusive lock on B. Granted • T1 requests exclusive lock on B. Wait • T2 requests exclusive lock on A. Wait

16. Deadlocks Continued • T2 will never release it’s lock on B until it gets a lock on A • T1 will never release it’s lock on A until it gets a lock on B • Both transactions will wait forever • This is bad

17. Two-Phase Locking Protocol • 1. Growing phase - transactions may acquire new locks • 2. Shrinking phase - transactions may release locks, but not acquire new ones

18. Two-Phase Locking Continued • Strict two-phase locking protocol - requires that all exclusive locks be held until the transaction commits • Rigorous two-phase locking protocol - requires that all locks be held until the transaction commits

19. Timestamp-Based protocols • Each transaction gets a timestamp when it enters the system • Timestamp can be either the system time, or simply a logical counter • W-timestamp(Q) - largest timestamp of any transaction that did write(Q) successfully • R-timestamp(Q) - largest timestamp of any transaction that did read(Q) successfully

20. Timestamp-Ordering • Transaction Ti with timestamp TS(Ti) issues read(Q) • TS(Ti) < W-timestamp(Q) then read(Q) is rejected and Ti is rolled back • TS(Ti) >= W-timestamp(Q) then read(Q) executes successfully

21. Timestamp-Ordering Cont. • Transaction Ti with timestamp TS(Ti) issues write(Q) • TS(Ti) < R-timestamp(Q) then write(Q) is rejected and Ti is rolled back • TS(Ti) < W-timestamp(Q) then write(Q) is rejected and Ti is rolled back • Otherwise write(Q) executes successfully

22. Thomas’ Write Rule • Transaction Ti with timestamp TS(Ti) issues write(Q) • TS(Ti) < R-timestamp(Q) then write(Q) is rejected and Ti is rolled back • TS(Ti) < W-timestamp(Q) then write(Q) is ignored and Ti continues • Otherwise write(Q) executes successfully

23. Validation-Based Protocols • Concurrency-control schemes impose additional overhead on system and can cause delays • Solution: find conflicts before they happen

24. Three-phase execution • Read phase - transaction reads data • Validation phase - transaction makes sure none of it’s writes to it’s temporary variables conflict with other operations • Write phase - apply actual changes to database

25. Validation timestamps • Validation requires three timestamps • Start - time the transaction began executing • Validation - time the Read phase competed and Validation phase began • Finish - time the transaction completed