Lecture Slides on Granular Locking and Degree Consistency | CS 511, Study notes of Deductive Database Systems

Download Lecture Slides on Granular Locking and Degree Consistency | CS 511 and more Study notes Deductive Database Systems in PDF only on Docsity! 1 CS511 Design of Database Management Systems Lecture 10: Granular Locking/Degree of Consistency Kevin C. Chang 2 Transactions l Transactions: – each as a “program” of atomic database operations – atomic units of database transformation & recovery l 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 l Atomicity: – all or nothing at all l Consistency – from one consistent state to another l Isolation – intermediate effects must be invisible to other l Durability – results permanent after commit 4 ?? What Ensure ACID? 5 Transaction Concurrency l Concurrency control: – ensures that transactions are interleaved correctly Xact T1 Xact 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) 6 ?? Transaction Interleaving l Before: A = 0, B = 0, then? l ? Correct schedule? Wrong schedule? Why? Xact T1 Xact 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 T1 Xact 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 T1 Xact 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 2 7 Transaction Interleaving l Schedule 1 (interleaved, more concurrent) = Schedule 3 (serial) l Consistency and isolation: transform DB in serial – (A=0,B=0) --- T1 --> (A=1,B=1) -- T2 --> (A=2,B=2) Xact T1 Xact 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 T1 Xact 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 8 Dependency of Transactions l Dependencies: defining ordering of transactions: rw, wr, ww l Serialization graph: Ti --> Tj for a dependency from Ti to Tj l ? Serializable if ... (called conflict serializable) Xact T1 Xact 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) schedule 1 T1 T2 Xact T1 Xact 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) schedule 2 T1 T2 9 ?? Atomic Actions lWhy only reads and writes? l How does CC knows what actions are? lWill knowing more “action semantics” help? 10 More “Semantics” of Atomic Actions l Atomic action: – inc(x, i): read(x), x = x + i, write(x) l ? Any dependencies between T1 and T2? Xact T1 Xact T2 inc(A,1) inc(A,2) inc(B,1) inc(B,2) 11 Locking Protocol l Locking: a “protocol” for accessing data – well-formed xacts lock/unlock “access units” before/after using them – lock manager grant/manage locks l Goals of locking protocol – ensure serializability – preserve high concurrency l ?? Parameters of a locking protocol? 12 Locking Protocol: Parameters lWhat “modes” of locks to provide? Compatibility? – e.g., S for shared, X for exclusive lWhat “units” to lock? – database? table? tuple? what else? l How to “well-behave” to obtain and hold locks? – in what sequence? – how long to hold?