Database System Implementation: Concurrency Control, Study notes of Computer Science

Download Database System Implementation: Concurrency Control and more Study notes Computer Science in PDF only on Docsity! 1 CS 4420 Database System Implementation Concurrency Control Ling Liu Associate Professor College of Computing, Georgia Tech 2 Lecture Outline Today’s Lecture Phantoms Phenomenon Tree-based CC Validation CC Exercises Review of lectures since midterm 1 Failure recovery WAL Undo/Redo/Undo-Redo Concurrency Control motivation Theorems Locking protocol 3 Review of Concurrency Control Problems of concurrent access Basic Concepts of Concurrency Control Schedules Conflict actions Conflict Equivalent Schedules Conflict-Serializable schedules Locking schemes Two Phase Locking (2PL) Share and Exclusive Locks S/X locks with Increment Lock S/X Locks with Update Lock Lock Table and Locking System Locking Granularity Multiple Granularity Locks (IS, IX, SIX …) Today’s Lecture Phantoms Phenomenon Tree-based CC Validation CC 4 Example R1 t1 t2 t3 t4 T1(IS) T1(S) , T2(IX) T2(IX) 9 Solution Use multiple granularity tree Before insert of node Q, lock parent(Q) in X mode R1 t1 t2 t3 This approach can be generalized to multiple indexes... 10 Next: Tree-based concurrency control Validation concurrency control (Optimistic concurrency control) 11 Example A B C D E F • all objects accessed through root, following pointers T1 lock T1 lockT1 lock can we release A lock if we no longer need A?? 12 Idea: traverse like “Monkey Bars” A B C D E F T1 lock T1 lockT1 lock T1 lock 13 Rules: tree protocol (exclusive locks) (1) First lock by Ti may be on any item (2) After that, item Q can be locked by Ti only if parent(Q) locked by Ti (3) Items may be unlocked at any time (4) After Ti unlocks Q, it cannot relock Q 14 Tree-like protocols are used typically for B-tree concurrency control E.g., during insert, do not release parent lock, until you are certain (that) child does not have to split Root 19 Example of what validation must prevent: RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} time T2 start T2 validated T3 validated T3 start ∩ = φ 20 T2 finish phase 3 Example of what validation must prevent: RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} time T2 start T2 validated T3 validated T3 start ∩ = φ allow T3 start 21 Another thing validation must prevent: RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} time T2 validated T3 validated finish T2 BAD: w3(D) w2(D) 22 finish T2 Another thing validation must prevent: RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} time T2 validated T3 validated allow finish T2 23 Validation (also called optimistic concurrency control) is useful in some cases: - Conflicts rare - System resources plentiful - Have real time constraints 24 Summary Have studied C.C. mechanisms used in practice - 2 PL (Pessimistic C.C.) - Multiple granularity - Tree (index) protocols - Validation (Optimistic C.C.) 29 Exercise 2 (Cont) b) Assume that the undo/redo recovery algorithm with checkpoints is being used. The database crashes immediately after statement 7. (Assume that all the log records up to this point are on disk.) b.1) Which transactions would have to be undone? b.2) Which transactions would have to be redone? Answer: T1 Answer: T3 30 Exercise 2 (Cont) c) Again assume that the undo/redo recovery algorithm with checkpoints is being used, but now the databases crashes just after statement 18. (Assume that all the log records up to this point are on disk.) c.1) Which transactions would have to be undone? c.2) Which transactions would have to be redone? Answer: T1 Answer: None 31 Concurrency Control Correctness (informally) If we stop running transactions, DB left consistent Each transaction sees a consistent DB Recovery: Problems due to failures only CC: Problems due to data sharing only CC + Recovery: Problems due to failures and sharing (next lecture) 32 Concepts Transaction: sequence of ri(x), wi(x) actions Conflicting actions: r1(A) w2(A) w1(A) w2(A) r1(A) w2(A) Schedule: represents chronological order in which actions are executed Serial schedule: no interleaving of actions or transactions 33 Nodes: transactions in S Arcs: Ti → Tj whenever - pi(A), qj(A) are actions in S - pi(A) <S qj(A) - at least one of pi, qj is a write Precedence graph P(S) (S is schedule) TjTi A 34 Definition S1, S2 are conflict equivalent schedules if S1 can be transformed into S2 by a series of swaps on non-conflicting actions. Definition A schedule is conflict serializable if it is conflict equivalent to some serial schedule. Theorem P(S1) acyclic ⇐⇒ S1 conflict serializable Lemma S1, S2 conflict equivalent ⇒ P(S1)=P(S2) 39 Locking Schemes Binary Shared + Exclusive Extending the S/X locking scheme with new lock Examples Increment Lock Update Lock Intentional Lock (IS, IX, SIX) 40 Two-phase Locking (2PL) Protocol To guarantee serializability: • In a transaction, all lock operations (S_Lock or X_Lock) precede the first unlock operation. • No locks can be acquired after the first lock is released. We call a transaction satisfying two-phase locking protocol, if it obeys the above rules. Two-phase execution • Growing phase: lock acquisition only (no unlock) • Shrinking phase: lock release (no more lock) • Lock point divides the two phases. Obtain lock Release lock Lock point Phase 1 Phase 2 BEGIN END 41 Serializability of 2PL Theorem: For any legal schedule S • if every transaction in the schedule S satisfies the 2PL protocol, S is guaranteed to be serializable, • obviating the need to test for serializability of schedules. Note: 2PL is a sufficient, but not a necessary condition for serializability of schedules. 2PL protocol may limit the amount of concurrency that can occur in a schedule. Potential Problems • Livelock (Starvation) • Deadlock 42 Livelock Livelock occurs when one transaction cannot proceed for an indefinite period of time while other transactions continue normally. Cause: due to unfair waiting schemes (usually priority-based ones) Fair schemes FCFS priority-based, by increasing the priority of a transaction who waits longer 43 DeadLocks Deadlock occurs when each of two transactions is waiting for the other to release the lock on an item. X_Lock(A); R1(A); X_Lock(B); waiting X_Lock(B); R2(B); X_Lock(A); waiting T1 T2 t1 t2 t3t4 In general, a deadlock may involve n (n>2) transactions, and can be detected by using a Wait-for graph. Wait-for graph • Node: transaction; • Edges: pending lock request. If Ti is waiting for an object held (locked) by Tj, then there is an edge from Ti to Tj. • Root is the current lock holder. T1 T2 B A 44 Deadlock Avoidance: Conservative 2 PL Conservative 2 PL (static): requires a transaction T to • pre-declare all the read set of items and write set of items; and lock all the items it accesses before T begins execution. • If any of the pre-declared items can not be locked, T does not lock any item at all. Instead, T waits and tries again until all the items are available for locking. • Conservative 2 PL is deadlock-free Obtain lock Release lock BEGIN END Transaction durationperiod of data item use No. of locks Phase 2Phase 1