1 / 27

Lecture 9: Granularity of Locks & Degrees of Consistency

Lecture 9: Granularity of Locks & Degrees of Consistency. Sept. 26, 2007 ChengXiang Zhai. Most slides are adapted from Kevin Chang’s lecture slides. Transactions. Transactions: each as a “program” of atomic database operations atomic units of database transformation & recovery

azana
Download Presentation

Lecture 9: Granularity of Locks & Degrees of Consistency

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lecture 9: Granularity of Locks &Degrees of Consistency Sept. 26, 2007 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)

More Related