Accessing data

1. Accessing data Transactions

2. Agenda • Questions from last class? • Transactions • concurrency • Locking • rollback

3. Transactions • 1+ SQL statements that are performed completely, or not at all • ACID properties • Atomic • Consistent • Isolation • Durability • Why develop these?

4. has Customer Account Banking

5. Transactions Checking T1 Savings T2 Loan T3 T1 – CustomerA checks her balance and withdraws money from checking account T2 – An automatic withdrawal from CustomerA’s checking account goes towards a mortgage loan that she holds with the same bank T3 – An automatic transfer moves money from CustomerA’s checking account into her savings account

6. Concurrency Problems: Lost Update T1 - read R1 T2 - read R1 T1 - write new R1 T2 - write new R1

7. Concurrency Problems: Temporary Update or Dirty Read T1 - read R T1 - update R T2 - read R T1 - rollback to original value of R

8. Concurrency Problems: Incorrect Summary T1 - read T2 - update R1 R2 R3

9. Concurrency Problems: Phantom Record T1 - query DB, retrieve set of records T2 - insert new record that satisfies T1’s query

10. Dealing with concurrency (OR: how to handle the pressure!) • Timestamp ordering • Locking • Multiversion protocols • Optimistic protocols (validation protocol)

11. Locks • Granularity – lock on table, record, page, ets. • Escalation – raising granularity in the middle of a transaction • Index locking – primarily for adding/deleting records, but also changes to index field

12. Locking: Binary Lock • 2 states: locked or unlocked • Rules: • Transaction must issue lock on item before doing any reads or writes of it. • Transaction must issue unlock of item after completing all reads and writes of it.

13. Locking: Multiple-mode Lock • 2 types of locks: Shared, S, or Read lock. Any number of S locks are allowed on an item. Exclusive, X, or Write lock. If an item is X-locked, there can be no other locks on it. • Transaction must have appropriate lock on item before performing the action.

14. Locking: Multiple-mode Lock (1) • Rules: • Transaction must issue S or X lock before reading an item. • Transaction must issue an X lock before writing an item. • Transaction must release locks after all reads and writes are completed.

15. Locking: Multiple-mode Lock (2) • Transaction may upgrade S lock to X lock if no one else has S lock. • Transaction may downgrade X lock to S lock when all writes are completed.

16. Intention locking • Acts on next higher level – to write to a record, acquire intention lock on table and then acquire lock on record • 3 types of locks • Intention read • Intention write • Read intention write • Must check for locks on at least two levels

17. Concurrency with Locking:Lost Update T1 - request S lock on R T1 - read R - S lock T2 - request S lock on R T2 - read R - S lock T1 - request X lock - wait T2 - request X lock - wait

18. Concurrency with Locking:Temporary Update T1 - request S lock on R T1 - read R - S lock T1 - request X lock on R T1 - update R - X lock T2 - request S lock on R - wait T1 - release X lock on R T2 - read R - S lock

19. Concurrency with Locking:Incorrect Summary (1) T1 - request S lock on R1 T1 - read R1 - S lock T1 - request S lock on R2 T1 - read R2 - S lock T2 - request S lock on R3 T2 - read R3 - S lock

20. Concurrency with Locking:Incorrect Summary (2) T2 - request X lock on R3 T2 - update R3 - X lock T2 - request S lock on R1 T2 - read R1 - S lock T2 - request X lock on R1 - wait T1 - request S lock on R3 - wait

21. Concurrency with Locking:Deadlock • Simplest example: T1 - write R1 - X lock T2 - write R2 - X lock T1 - request X lock on R2 - wait T2 - request X lock on R1 - wait