Lecture 10: Granularity of Locks & Degrees of Consistency

1. Lecture 10: Granularity of Locks &Degrees of Consistency Sept. 27, 2006 ChengXiang Zhai Most slides are adapted from Kevin Chang’s lecture slides

2. Transactions • Transactions: • each as a “program” of atomic database operations • atomic units of database transformation & recovery • Consistency guaranteed if: • each transaction is “well behaved” • start/end with consistent DB • each transaction runs in serial • no interleaving to mess up

3. ACID Properties of Transactions • Atomicity: • all or nothing at all • Consistency • from one consistent state to another • Isolation • as if executed alone • Durability • results permanent after commit

4. Transaction Concurrency • Concurrency control: • ensures that transactions are interleaved correctly Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B + 1 write(B) read (B) B = B * 2 write (B)

5. Transaction Interleaving • Before: A = 0, B = 0, then? • Correct schedule? Wrong schedule? Why? Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B + 1 write(B) read (B) B = B * 2 write (B) Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B * 2 write(B) read (B) B = B + 1 write (B) Xact T1Xact T2 read(A) A = A + 1 write(A) read(B) B = B + 1 write(B) read(A) A = A * 2 write(A) read(B) B = B * 2 write(B) schedule 1 schedule 2 schedule 3

6. Transaction Interleaving • Schedule 1 (interleaved, more concurrent) = Schedule 3 (serial) • Consistency and isolation: transform DB in serial • (A=0,B=0) --- T1 --> (A=1,B=1) -- T2 --> (A=2,B=2) Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B + 1 write(B) read (B) B = B * 2 write (B) Xact T1Xact T2 read(A) A = A + 1 write(A) read(B) B = B + 1 write(B) read(A) A = A * 2 write(A) read(B) B = B * 2 write(B) schedule 1 schedule 3

7. Dependency of Transactions • Dependencies: defining ordering of transactions: rw, wr, ww • Serialization graph: Ti --> Tj for a dependency from Ti to Tj • Serializable if ... (called conflict serializable) Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B * 2 write(B) read (B) B = B + 1 write (B) Xact T1Xact T2 read(A) A = A + 1 write(A) read(A) A = A * 2 write(A) read(B) B = B + 1 write(B) read (B) B = B * 2 write (B) T1 T1 T2 T2 schedule 1 schedule 2

8. Atomic Actions • Why only reads and writes? • How does CC know what actions are? • Will knowing more “action semantics” help?

9. More “Semantics” of Atomic Actions • Atomic action: • inc(x, i): read(x), x = x + i, write(x) • Any dependencies between T1 and T2? Xact T1Xact T2 inc(A,1) inc(A,2) inc(B,1) inc(B,2)

10. Locking Protocol • Locking: a “protocol” for accessing data • well-formed xacts lock/unlock “access units” before/after using them • lock manager grants/manages locks • Goals of locking protocol • ensure serializability • preserve high concurrency • Parameters of a locking protocol?

11. Locking Protocol: Parameters • What “modes” of locks to provide? Compatibility? • e.g., S for shared, X for exclusive • What “units” to lock? • database? table? tuple? what else? • How to “well-behave” to obtain and hold locks? • in what sequence? • how long to hold?

12. Motivation: A “Simple” Protocol • Lock modes: • S for shared and X for exclusive access • compatibility: (S, S) = T, otherwise F • Unit: • a relation • Behavior: • lock (the maximal mode) before access • unlock immediately after

13. Simple Protocol: What’s Wrong? Xact T1Xact T2 X.lock(A’s relation) read(A) A = A + 1 write(A) X.unlock(A’s relation) X.lock(A’s relation) read(A) A = A * 2 write(A) X.unlock(A’s relation) X.lock(B’s relation) read(B) B = B + 1 write(B) X.unlock(B’s relation) X.lock(B’s relation) read (B) B = B * 2 write (B) X.unlock(B’s relation)

14. Simple Protocol: Serializability? • Serializability? • fail to interleave dependencies in same order • Solution: 2PL protocol • for well-formed xacts, legal scheduler: 2PL --> serializable Xact T1Xact T2 X.lock(A) … X.unlock(A) X.lock(A) … X.unlock(A) X.lock(B) … X.unlock(B) X.lock(B) … X.unlock(B) Xact T1Xact T2 X.lock(A) … X.lock(B) X.unlock(A) X.lock(A) … … X.unlock(B) X.lock(B) … X.unlock(A) X.unlock(B)

15. Simple Protocol: Granularity? • Unit of locking: How to increase concurrency? • coarse units? fine units? • Granularity: • concurrence vs. overhead • hierarchical lockable units: • DB, areas, files, pages, tuples, attributes • Correctness problem: • T1 S.locks a tuple, T2 X.locks the file? • solution?

16. Granularity Locking • Database: as hierarchy of lockable units • Locking: to lock a unit, • first lock all containing units with “intension” • why? • intension locks: IS, IX, SIX (intension to upgrade) • why not just “I” ? • Unlocking: • release all relevant locks at once, or leaf to root

17. Granularity Locking: DAG • Generalization: DAG of units: • S locks at least one path to the node • X locks all paths to the node • Q: Why implicit S if one parent is X (or S, SIX)?

18. Lock Compatibilty Table X SIX S IX IS NL privilege ordering

19. Compatibility Examples • Questions: • why SIX is useful? • (SIX, S) = No? (SIX, IS) = Yes? (SIX, IX) = No? Grant S? IS? IX? SIX DB SIX SIX Relation Student Relation Enrollment X X Tuple Tuple Tuple Tuple

20. Consistency: Dirty-Data Based • How to lock for each degree? T does not overwrite dirty data of other xacts 0 T does not commit any writes until EOT 1 T does not read dirty data from other xacts 2 other xacts do not dirty any data read by T before T completes 3

21. Consistency: Locking-Based • Corresponding to each condition: • T does not overwrite dirty data of other xacts • set write locks on dirty data (well-formed on w) • T does not commit any writes until EOT • set “long” write locks (2P/EOT on w) • T does not read dirty data from other xacts • set read locks (well-formed on r) • other xacts do not dirty any data read by T before T completes • set “long” read locks (2P/EOT on r)

22. How “long” to hold a lock? • 2PL requirement: • only “shrink” after “growing” • hold locks until no more new locks will be made • why doing so? • problem? • End of transaction: • unlock (to make data accessible) at xact commit • why?

23. Examples • Lost updates • T1: w(A) abort • T2: w(A) • ? what degree can prevent T1.abort from masking T2.w(A)? • Dirty Reads: • T1: w(A) abort • T2: r(A) • ? what degree can prevent T2.r(A) from reading bogus A? • Inconsistent Reads • T1: w(A) • T2: r(A) r(A) • ? what degree can ensure repeated reads will be consistent?

24. Additional Issues • Deadlock? • Concurrency control by timestamps (optimistic=assume rare unserializable behavior) • Timestamping • Validation

25. CC Support in Practice IBM DB2: • Optional explicit locking at table level • LOCK TABLE students in SHARE/EXCLUSIVE MODE • locks held until and automatically released at EOT • Automatic implicit locking with “isolation level” setting SQL transaction isolation levels: • serializable • repeated read • serializable except not lock indexes, thus phantom insert possible • read committed • set write/read locks • read uncommitted • set write but not read locks (somewhat different names in DB2) • For more information, see Jim Gray’s well known book “Transaction Processing”

26. What You Should Know • Why is granularity of locks necessary? • How is granularity of locks achieved? • What is two-phase locking? • What are the four degrees of consistency? • How are the degrees of consistency related to locking

27. Carry Away Messages • Once again, we see a messy problem gets formulated nicely • Look for important, but messy problems • A solution that captures the essence of a problem has a long-lasting impact • Observe the need for granularity -> a good solution to the problem • Cross-field advantages (OS-> DB)