1 / 39

Concurrency Control II

This lecture discusses the concept of conflict serializability and introduces different concurrency control approaches, such as two-phase locking, to guarantee schedule serializability and recovery.

mlaflamme
Download Presentation

Concurrency Control II

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. Concurrency Control II R &G - Chapter 17 Lecture 20 Smile, it is the key that fits the lock of everybody's heart. Anthony J. D'Angelo, The College Blue Book

  2. Administrivia • Homework 4 due today 10 p.m. • Homework 3 almost graded

  3. Review: Concurrency • Concurrent users introduce anomalies • Dirty reads (WR): T2 reads a value A that T1 wrote but didn’t commit • Unrepeatable Reads (RW): T1 reads a value A that is then written by T2 • Lost Updates (WW): T2 overwrites a write by T1 • Serializable schedules: • A schedule that is equivalent to some serial execution of the transactions. • Definition: Two operations conflictif: • They are by different transactions, • they are on the same object, • and at least one of them is a write. R(B) W(B) W(A) T1: R(A) R(A) W(A) R(B) W(B) T2:

  4. Conflict Serializable Schedules • Definition: Two schedules are conflict equivalent iff: • They involve the same actions of the same transactions, and • every pair of conflicting actions is ordered the same way • Definition: Schedule S is conflict serializable if: • S is conflict equivalent to some serial schedule. Conflict serializable schedules imply that conflicting operations can be arranged as though they executed serially

  5. R(B) W(B) W(B) R(B) R(A) W(A) R(A) W(A) Conflict Serializability – Intuition • A schedule S is conflict serializable if: • You are able to transform S into a serial schedule by swapping consecutive non-conflictingoperations of different transactions. • Example: R(B) T1: R(A) W(B) W(A) T2: R(A) W(A) W(B) R(B) T1: R(A) R(B) W(B) W(A) T2: R(A) W(A) W(B) R(B)

  6. Conflict Serializability • Every conflict serializable schedule is serializable • But some serializable schedules are NOT conflict serializable. • Example: T1: R(A) W(A) T2: W(A) T3: W(A) • Serializable because it is equivalent to the following serial schedule: But not conflict serializable; the writes of T1 and T2 are reversed. T1: R(A) W(A) T2: W(A) T3: W(A)

  7. Dependency Graph Ti Tj • Dependency graph: • One node per Xact • Edge from Ti to Tj if: • An operation Oi of Ti conflicts with an operation Oj of Tj and • Oi appears earlier in the schedule than Oj. • Theorem: Schedule is conflict serializable if and only if its dependency graph is acyclic.

  8. Conflict Serializability Dependency graph T1: R(A) W(A) T1 T2: • Dependency graph: • One node per Xact • Edge from Ti to Tj if: • An operation Oi of Ti conflicts with an operation Oj of Tj and • Oi appears earlier in the schedule than Oj. W(A) T3: W(A) T2 T3

  9. A B Another Example • A schedule that is not conflict serializable: • The cycle in the graph reveals the problem. • The output of T2 depends on T1’s value of A, and the output of T1 depends on T2’s value of B. T1: R(A), W(A), R(B), W(B) T2: T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) T1 T2 Dependency graph

  10. An Aside: View Serializability • Alternative (weaker) notion of serializability. • Schedules S1 and S2 are view equivalent if: • If Ti reads initial value of A in S1, then Ti also reads initial value of A in S2 • If Ti reads value of A written by Tj in S1, then Ti also reads value of A written by Tj in S2 • If Ti writes final value of A in S1, then Ti also writes final value of A in S2 • Basically, allows all conflict serializable schedules + “blind writes” 1 T1: R(A) W(A) T2: W(A) T3: W(A) T1: R(A),W(A) T2: W(A) T3: W(A) view 3

  11. Serializability and Locking • Locking approaches guarantee schedule serializability and recovery • We talked about two of them before the break…what are they?

  12. Review: Lock-Based Concurrency Control Two-phase Locking (2PL) Protocol: • Each Xact must obtain: • a S (shared) lock on object before reading, and • an X (exclusive) lock on object before writing. • If an Xact holds an X lock on an object, no other Xact can get a lock (S or X) on that object. • System can obtain these locks automatically • Two phases: acquiring locks, and releasing them • No lock is ever acquired after one has been released • “Growing phase” followed by “shrinking phase”. • Ensures acyclic dependency graphs • Allows only conflict serializable schedules

  13. Review: Strict 2 Phase Locking • Strict Two-phase Locking (Strict 2PL) Protocol: • Same as 2PL, except All locks held are released only when the transaction completes • Advantage: no other transaction reads anything you write until you commit. • e.g a transaction will only read committed data. • Disadvantage: transactions end up waiting. • Ensures acyclic dependency graphs • Allows only conflict serializable schedules • Allows only strict schedules • No values written by an Xact T can be read or overwritten until T commits or aborts.

  14. Summary: Guarantees provided by locking All Schedules View Serializable Conflict Serializable 2PL Strict recoverable avoids cascading aborts Strict 2PL Serial

  15. Lock Management • Lock and unlock requests are handled by the lock manager • Lock table entry: • Number of transactions currently holding a lock • Type of lock held (shared or exclusive) • Pointer to queue of lock requests • Locking and unlocking have to be atomic operations • requires latches (“semaphores”), which ensure that the process is not interrupted while managing lock table entries • see CS162 for implementations of semaphores • Lock upgrade: transaction that holds a shared lock can be upgraded to hold an exclusive lock • Can cause deadlock problems

  16. Deadlocks • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Two ways of dealing with deadlocks: • Deadlock prevention • Deadlock detection

  17. Deadlock Prevention • Assign priorities based on timestamps. • Oldest xacts have the highest priority • Assume Ti wants a lock that Tj holds. Two policies possible: • Wait-Die: new transactions aren’t allowed to wait If timestamp(Ti) < timestamp(Tj) then wait(Ti) else abort(Ti); restart(Ti) • Wound-wait: old transactions don’t have to wait If timestamp(Ti) < timestamp(Tj) then abort(Tj); restart(Tj); else wait(Ti) • When transaction re-starts, it gets its original timestamp • Why?

  18. Deadlock Detection • Create a waits-for graph: • Nodes are transactions • There is an edge from Ti to Tj if Ti is waiting for Tj to release a lock • Periodically check for cycles in the waits-for graph

  19. Deadlock Detection (Continued) Example: T1: S(A), S(D), S(B) T2: X(B) X(C) T3: S(D), S(C), X(A) T4: X(B) S(B) S(A) S(D) X(B) T1 T2 X(B) X(C) X(A) S(D) S(C) T4 T3

  20. Deadlock Detection (cont.) • In practice, most systems do detection rather than prevention • Experiments show that most waits-for cycles are length 2 or 3 • Hence few transactions need to be aborted • Implementations can vary • Can construct the graph and periodically look for cycles • Can do a “time-out” scheme: if you’ve been waiting on a lock for a long time, assume you’re deadlock and abort

  21. Lock Management • What should we lock? • We assume tuples so far, but that can be expensive! • If we do table locks, that’s too conservative • Multi-granularity locking • Locking in indexes • don’t want to lock a B-tree root for a whole transaction! • actually do non-2PL “latches” in B-trees • CC w/out locking • “optimistic” concurrency control • “timestamp” and multi-version concurrency control • locking usually better, though

  22. Database Tables Pages Tuples Multiple-Granularity Locks • Hard to decide what granularity to lock (tuples vs. pages vs. tables). • Shouldn’t have to make same decision for all transactions! • Data “containers” are nested: contains

  23. Database Tables Pages Tuples Multiple-Granularity Locks (cont) • Idea: • need locks of different granularity, sometimes need to lock >1 table. • if transaction wants to rewrite entire DBMS, get X lock on DBMS. • if transaction wants to rewrite entire Table, get X lock on Table • if transaction wants to read entire Table, get S lock on Table • etc. • but, how to ensure that one transaction doesn’t lock DBMS while another locks a tuple? contains

  24. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö S Ö Ö Ö Ö X Solution: New Lock Modes, Protocol • Allow Xacts to lock at each level, but with a special protocol using new “intention” locks. • Still need S and X locks, but before locking an item, Xact must have proper intension locks on all its ancestors in the granularity hierarchy. • Before locking an item, Xact must set “intention locks” on all its ancestors. • For unlock, go from specific to general (i.e., bottom-up). Lock compability

  25. Multiple Granularity Lock Protocol • Each Xact starts from the root of the hierarchy. • Special SIX lock used when reading many records, and updating a few. • SIX lock conflicts are all S and IX conflicts (e.g. only compatible with IS locks). • To get S or IS lock on a node, must hold IS or IX on parent node. • To get X or IX or SIX on a node, must hold IX or SIX on parent node. • Must release locks in bottom-up order.

  26. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. • T1 wants to read & change tuple 2 • gets IX lock on DBMS • gets IX lock on Sailor • gets IX lock on Page 1 • gets X lock on Tuple 2 & changes it • then releases locks in reverse order IX Database IX Sailor Table IX Page 1 Page 2 Tuple 2 Tuple 3 Tuple 4 Tuple 1 X

  27. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example 2 • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. • T1 wants to read & change tuple 2 • T2 wants to read & change tuple 3 • T1 gets IX lock on DBMS, Sailor, Page 1 • T1 gets X lock on Tuple 2 & changes it • T2 gets IX lock on DBMS, Sailor, Page 2 • T2 gets X lock on Tuple 3 & changes it • No problem! T2:IX T1:IX Database T2:IX T1:IX Sailor Table T1:IX Page 1 Page 2 T2:IX Tuple 2 Tuple 3 Tuple 4 Tuple 1 T1:X T2:X

  28. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example 3 • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. T2:IS • T1 wants to read & change tuple 2 • T2 wants to read all of Page 1 • T1 gets IX lock on DBMS, Sailor, Page 1 • T1 gets X lock on Tuple 2 & changes it • T2 gets IS lock on DBMS, Sailor • T2 tries to get S lock on Page 1, but S conflicts with IX lock. T2 blocks. • What if T2 had started first? T1:IX Database T2:IS T1:IX Sailor Table T1:IX Page 1 Page 2 T2:wait Tuple 2 Tuple 3 Tuple 4 Tuple 1 T1:X

  29. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example 3 • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. T2:IS • T1 wants to read & change tuple 2 • T2 wants to read all of Page 1 • T2 gets IS lock on DBMS, Sailor • T2 gets S lock on Page 1 • T1 gets IX lock on DBMS, Sailor • T1 tries to get IX lock on Page 1, waits T1:IX Database T2:IS T1:IX Sailor Table T1:waits Page 1 Page 2 T2:S Tuple 2 Tuple 3 Tuple 4 Tuple 1

  30. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example 4 • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. • T1 wants to read all tuples, change tuple 1 and Tuple 3 • T2 wants to read Tuple 4 • T1 gets IX lock on DBMS • T1 gets SIX lock on Sailor, Pages • T1 gets X lock Tuple 1 & 3 • T2 gets IS lock on DBMS, Sailor, Page 2 • T2 gets S lock on Tuple 4. If T1 had gotten an X lock on Tuple 4, T2 would have to wait. T2:IS T1:IX Database T2:IS T1:SIX Sailor Table T2:IS T1:SIX T1:SIX Page 1 Page 2 Tuple 2 Tuple 3 Tuple 4 Tuple 1 T2:S T1:X T1:X

  31. IX SIX S X IS IS Ö Ö Ö Ö IX Ö Ö SIX Ö Ö S Ö X Multi-Granularity Example 5 • Rules • Each Xact starts from the root of the hierarchy. • To get S or IS lock, must hold IS or IX on parent. • To get X or IX or SIX, must hold IX or SIX on parent. • Must release locks in bottom-up order. T2:IX T1:IX Database • T1 wants to read all tuples, change 1&3 • T2 wants to change Tuple 4 • T1 gets IX lock on DBMS • T1 gets SIX lock on Sailor, Pages • T1 gets X lock on Tuple 1 & 3 • T2 gets IX lock on DBMS. • T2 tries to get IX lock on Sailor, but this conflicts with T1’s SIX lock, so T2 blocks. T1:SIX Sailor Table T2:waits T1:SIX T1:SIX Page 1 Page 2 Tuple 2 Tuple 3 Tuple 4 Tuple 1 T1:X T1:X

  32. Locking in B+ Trees • What about concurrency control for indexes? • Simplistic solution: Ignore the tree structure, just lock pages while traversing the tree, following 2PL. T2: waits T1 wants to read 11,23 T2 wants to read 3 T3 wants to update 33 20 T2:S T1:S T2:S 10 24 T1:S T1:S 3 5 11 14 21 23 30 33 T2:S T1:S T1:S • Leads to lock contention at top of the tree • Root nodes become bottlenecks because every tree access begins at the root.

  33. Two useful observations • Higher levels of the tree only direct searches for leaf pages. • For inserts: • a node must be X locked only if a split can propagate up to it from the modified leaf. • Example: insert 9 vs insert 15 20 30 24 13 15 9 7 14 16 15 16 • We can exploit these observations to design efficient locking protocols that guarantee serializability even though they violate 2PL.

  34. A Simple Tree Locking Algorithm • Search: Start at root and go down; • S lock node. • Unlock its parent. • Insert/Delete: Start at root and go down, • X lock node. • If node is safe, release all locks on ancestors. • Safe node: Node such that changes will not propagate up beyond this node. • Inserts: Node is not full. • Deletes: Node is not half-empty.

  35. ROOT T1: Search 38 T2: Insert 45 T3: Insert 25 A 20 Example T1:S T3:X T2:X • Search: • S lock node. • Unlock its parent. • Insert/Delete: • X lock node. • If node is safe, release all locks on ancestors. T1:S B 35 T2:X T3:X T3:X F T1:S C 23 38 44 T2:X H G I D E 20* 22* 23* 24* 35* 36* 38* 41* 44* T3:X T3:X T1:S T2:X

  36. A Better Tree Locking Algorithm (See Bayer-Schkolnick paper) • Search: As before. • Insert/Delete: • Set locks as if for search, get to leaf, and set X lock on leaf. • If leaf is not safe, release all locks, and restart Xact using previous Insert/Delete protocol. • Gambles that only leaf node will be modified; if not, S locks set on the first pass to leaf are wasteful. In practice, better than previous alg.

  37. ROOT T1: Delete 38 T2: Insert 25 A 20 T1:S Example T2:S • Search: • S lock node. • Unlock its parent. • Insert/Delete: • Set locks for search. • X lock leaf. • If leaf not safe, restart using previous protocol. T2:X T1:S B 35 T2:S T2:X T2:S T2:X F C 23 38 44 T1:S H G I D E 20* 22* 23* 24* 35* 36* 38* 41* 44* T2:X T1:X

  38. Even Better Algorithm • Search: As before. • Insert/Delete: • Use original Insert/Delete protocol, but set IX locks instead of X locks at all nodes. • Once leaf is locked, convert all IX locks to X locks top-down: i.e., starting from node nearest to root. (Top-down reduces chances of deadlock.)

  39. Hybrid Algorithm • The likelihood that we really need an X lock decreases as we move up the tree. • Hybrid approach: Set S locks Set SIX locks Set X locks

More Related