Module 4 Isolation Concepts COP 6730 1 System

Module 4 Isolation Concepts COP 6730 1

System State • The system state consists of objects related in certain ways. These relationships are best thought of as invariants about the objects. Examples: – Account balances must be positive – All managers must be employers and so must appear in the EMPLOYEE table • The system state is said to be consistent if it satisfies all these invariants. 2

Appearance of Isolation (1) Often, state must be made temporarily inconsistent while it is being transformed (by a transaction) to a new, consistent state. Ø Concurrent transaction execution must be controlled so that correct programs do not malfunction. Ø System try to control concurrency by providing the appearance of isolation (e. g. , using locking). 3

Appearance of Isolation (2) Employee Transaction 2 Delete an employee Manager Transaction 1 Transaction 3 Appearance of isolation 4

Dependency Model of Isolation Ii Set of objects read by transaction Ti (it’s input) Oi Set of objects written by Ti (it’s output) {Ti} Set of concurrent transactions The set of transactions {Ti} can run in parallel with no concurrency anomalies if their outputs are disjoint from one another’s inputs and outputs, i. e. , Oi (Ij Oj) = for all i j 5

Cannot help in this case Employee Transaction 2 I am corrupting the data Manager Transaction 3 Transaction 1 Appearance of isolation The ACID property assumes that the transaction knows what it is doing to its data (i. e. , program is correct) If a transaction runs in isolation, it will correctly transform the system state. 6

Static Allocation - Early Systems • The transaction scheduler compares the new transaction’s needs to all the running transactions. • If there was a conflict, initiation of the new transaction is delayed until the conflicting transactions has completed. Problem: It is difficult to determine the inputs and outputs of a transaction before it runs. Example: Since a banking transaction potentially updates any record of the Account table, static allocation would probably reserve the entire table. → Result in serial execution of the transactions 7

Dynamic Allocation • Each transaction is viewed as a sequence of actions (rather then as an input-output set. ) • When an action accesses a particular object, the object is dynamically allocated to that transaction. Advantage: Allocation at action level → more concurrency 8

More Concurrency I am blocked Employee Delete an employee Transaction 2 Manager Transaction 1 I can proceed Transaction 3 Appearance of isolation 9

Versions of Objects • Transactions are sequences of actions operating on objects. • Objects go through a sequence of versions as they are written by these actions: – Nothing ever changes; rather, new versions of objects are created. Write Read <o, 1> T 1 <o, 1> T 2 Different versions of object o <o, 2> 10

Transaction Dependency (1) • If a transaction reads an object, the transaction depends on that object version • If the transaction writes an object, the resulting object version depends on the writing transaction. I depends on T 2 I depends on <0, 1> Write Read <o, 1> T 1 <o, 1> T 2 <o, 2> Since T 2 may overwrite the data T 1 depends on, we have a read-write dependency between T 1 and T 2 11

Transaction Dependencies (1) • If a transaction reads an object, the transaction depends on that object version • If the transaction writes an object, the resulting object version depends on the writing transaction. READ WRITE dependency WRITE READ dependency WRITE dependency Note: Only write actions create versions and dependencies. 12

Dependency Graph Write T 3 “T 3 →T 9” is not in the dependency graph <o, 2> 13

Isolation Theorems Any dependency graph without cycles implies an isolated execution of the transactions. T 1 T 2 T 3 T 1 T 2 T 4 Equivalent T 4 T 3 The transactions can be topologically sorted to make an equivalent execution history in which each transaction ran to completion before the next one began (i. e. , isolated execution). Example: T 1, T 2, T 4, T 3 14

Three Bad Dependencies T 1 • There is no topological order for this set of transactions concurrency anomalies. T 2 T 3 • Cycles take one of only three generic forms Lost Update, Dirty Read, and Unrepeatable Read 15

Lost Update Lost update: caused by WRITE->WRITE dependency T 2 READ < 0, 1 > T 1 WRITE < 0, 2> T 2 WRITE < 0, 3 > T 1’s write is ignored by T 2 Lost update ! 16

Dirty Read: caused by WRITE -> READ dependencies T 2 WRITE < 0, 2 > T 1 READ < 0, 2 > T 2 WRITE < 0, 3 > Dirty read – T 1 reads an object previously written by T 2, then – T 2 makes further changes to the object – T 1 is using an outdated version of the object 17

Unrepeatable Read: caused by READ -> WRITE dependencies T 1 READ < 0, 1 > T 2 WRITE < 0, 2 > T 1 READ Unrepeatable read < 0, 2 > T 1 reads an object twice: • once before T 2 updates it, and • once after committed T 2 has updated it. • The first read is not repeatable 18

A Cycle in all Three Forms Dirty read Lost update ! Unrepeatable read 19

ISOLATION Preventing the concurrency anomalies gives the effect of running transactions in isolation. 20

Dirty Outputs of a transaction are said to be uncommitted or dirty, if the transaction has not yet issued COMMIT WORK. 21

Isolated Transactions Preclude lost update Prevent dirty reads Transaction T is isolated from other transactions if: 1) T does not overwrite dirty data of other transactions. 2) T’s writes are neither read nor written by other transactions until COMMIT WORK 3) T does not read dirty data from other transactions. Prevent dirty reads Preclude lost update 4) Other transactions do not write any data read by T before T completes. Prevent unrepeatable read 22

Properties of Isolated Transactions • Each transaction reads a consistent input state. • Any execution of the system is equivalent to some serial execution (no concurrency anomalies) • None of the updates of committed transactions can be lost, and aborted transactions can be rerun. 23

NOTATIONS s = < a, b, c > : a sequence s || s : concatenation of two sequences s[i] : the ith element of sequence s < s[i] | predicate (s[i] ) > : a subsequence of s The system state, S, consists of an infinite set of named objects, each with a value. S is denoted {< name, value >} 24

Actions on Objects The system supports the following actions on these objects. READ BEGIN WRITE COMMIT XLOCK (exclusive lock) ROLLBACK SLOCK (share lock) UNLOCK To simplify the transaction model, – BEGIN, COMMIT, and ROLLBACK are defined in terms of the other actions (next slide), so that – only READ, WRITE, LOCK and UNLOCK actions remain. 25

COMMIT Example Original BEGIN SLOCK XLOCK READ WRITE COMMIT A B Simplified SLOCK XLOCK READ WRITE UNLOCK A B A B COMMIT action simply releases locks 26

ROLLBACK Example Original BEGIN SLOCK READ XLOCK WRITE ROLLBACK A A B B Simplified SLOCK READ XLOCK WRITE UNLOCK A A B B B /* UNDO A B ROLLBACK action must • first undo all changes to the objects the transaction wrote, and • then issue the unlock statements 27

Well-Formed Transactions A transaction is said to be well-formed if – All its READ, WRITE, and UNLOCK actions are covered by locks (preceded by locking of the corresponding object), and – Each lock action is eventually followed by a corresponding UNLOCK action. READ and UNLOCK actions on O 3 are not covered by an SLOCK or XLOCK WRITE action on O 1 is not covered by an XLOCK Not well-formed ! 28

2 -Phase Transactions 2 -Phase transaction A transaction is defined as 2 -phase if all its lock actions precede all its UNLOCK actions. in g Gr ow g in nk ri Sh – Shrinking Phase: releasing locks #locks – Growing phase: acquiring locks 29 time

Transaction Histories • A history lists the order in which actions of a set of transactions were successfully completed. Example: – A transaction requests a lock at the beginning of the history but has to wait – The lock is finally granted near the end of the history – that lock request will appear near the end of the history • A history preserves the order of the actions in each of the transactions. Initial State Trans. T 2 Trans. T 1 Trans. T 2 Trans. T 3 Trans. T 1 Trans. T 3 action History action Another State 30

Serial History • The simplest histories first run all the actions of one transaction, then run all the actions of another to completion, and so on. FThe transactions are isolated • Such one-transaction-at-a-time histories are called serial histories. • serial histories have no concurrencyinduced inconsistency and no transaction sees dirty data 31

Legal Histories • Locking constraints the set of allowed histories. This history cannot • Histories that obey the locking constraints are called Legal. happen: T 1 slock A T 2 xlock A T 2 write A T 1 read A 32

Legal Histories - Examples • Histories are not constructed, they are a byproduct of the system behavior. • Locking systems only produce legal behavior. T 2 would be made to wait until this time 33

States of a Transaction Begin Active Block Running Blocked Resume Restart (sometime) Reject Aborted Committed 34

Scheduler Active transactions T 1 t nex ion act T 2 serializable schedule Scheduler T 3 Legal history Blocked Actions Rejected Actions: Actions that violate the 2 -phase and/or Wellformed rules ion ct da T 5 actions in arriving order ba T 4 Histories are not constructed, they are a byproduct of the system behavior. Lock Compatibility Table Abort the transactions Rejected Actions 35

An Analogy Scheduler An action of transaction 1 An action of transaction 4 An action of transaction 2 An action of transaction 3 36

Version of an Object • < T, a, o >: a step of the history. It denotes an action a by transaction T on object o. • V(o, k): The version of an object o at step k of a history. At step k of history H, object o has a version equal to the number of writes of that object before this step. V(o, k) = |{< t, aj, o > H | j<k and aj = WRITE}| Set size Action at Step j 37

Dependency Relation • Each history H for a set of transactions {Ti } defines a ternary dependency relation DEP(H): < T, < o, V(o, j) >, T > DEP(H) if a 1 is a WRITE and a 2 is a WRITE, or a 1 is a WRITE and a 2 is a READ, or a 1 is a READ and a 2 is a WRITE. where a 1 and a 2 are actions performed on the object o by T and T , respectively. • This definition captures the WRITE, WRITE READ, and READ WRITE dependencies. DEP(H): [Source. Trans, OID, Obj. Version, Dest. Trans] 38

Dependency Graph • The dependency relation for a history defines a directed dependency graph: T 1 – transactions are the nodes, and T – object versions label the edges. • < T, < o, j >, T > DEP(H) the graph has an edge from node T to node T labeled by < o, j > T 2 <o, j > T’ 39

Isolated Histories • Two histories for the same set of transactions are equivalent if they have the same dependency relation, i. e. , DEP(H) = DEP(H ). They have the same effect on the database • A history is said to be isolated if it is equivalent to a serial history. 40

Equivalent Histories - Example History H T 3. 1 DEP(H) = DEP(SH) Serial history SH T 1. 1 T 1. 2 T 1. 1 T 3. 2 T 1. 3 T 1 T 2. 1 T 1. 4 T 1. 2 T 2. 1 T 3. 3 T 2. 2 T 3. 4 T 3 T 2. 4 T 4. 1 T 2. 5 T 2. 3 T 3. 1 T 3. 5 T 3. 2 T 2. 4 T 1. 3 T 4. 2 T 2. 5 T 4. 3 T 4. 4 T 4. 5 T 1. 4 • DEP(H) = DEP(SH) [ The corresponding transactions in H and SH read the same input and compute the same output [ H and SH are equivalent T 3. 3 T 3. 4 T 3. 5 T 4. 1 T 4. 2 T 4. 3 T 4. 4 T 4. 5 • SH executes the transactions in isolation [ H also executes the transactions in isolation 41

Dependencies in a History • The dependencies of a history define a time order of the transactions. This ordering is denoted by the symbol “<<<. ” – T<<< T if there is a path in the dependency graph from transaction T to transaction T , i. e. , T<<< T if <T, o, T > DEP(H), or /* direct */ T<<<HT and < T , o, T > DEP(H) /* indirect */ – All equivalent histories define the same ordering. • Notations: BEFORE(T) = {T | T <<<T} AFTER(T) = {T | T<<<T } T is called a wormhole transaction if T BEFORE(T) AFTER(T) T T A AF T ER Z Y T BE (T) B T’ X FORE( T) 42

WORMHOLES ARE NOT ISOLATED • T<<<T indicates a cycle in the dependency graph. • Since a dependency graph with cycles is not equivalent to any serial history, a history with wormholes is not isolated. 43

WORMHOLE TRANSACTIONS No wormhole transactions No cycles! T 3, T 4, T 5, T 6 are wormhole transactions Cycles! Note: Two transactions not related by <<< maybe arbitrarily scheduled with respect to each other, e. g. , T 2 and T 4 44

Wormhole Theorem A history is isolated if, and only if, it has no wormhole transactions. 45

Locking Theorems 1. Locking theorem: If all transactions are well-formed and two-phase, then any legal history will be isolated. Analogy: • Traffic lights provide a “safety” mechanism • If all drivers are good citizen, traffic lights prevent accidents 2. Locking theorem (converse): If a transaction is not well-formed or is not two-phase, then it is possible to write another transaction such that the resulting pair is a wormhole. Analogy: If there are bad drivers, then accidents can occur 46

Rollback Theorem An update transaction that does an UNLOCK and then a ROLLBACK is not two-phase. 47

Wormhole Theorem: Proof Wormhole Theorem: A history is isolated if, and only if, it has no wormhole transactions. We need to prove two things: 1. History H is isolated no wormholes (proof is by contradiction – next page) 2. H has no wormholes H is isolated (proof is by induction) 48

Wormhole Theorem: Proof (cont’d) 1. History H is Isolation no wormholes (proof is by contradiction) – Suppose H is an isolated history of {Ti | i = 1, . . , n} H is equivalent to some serial history SH = T 7 || T 4 || T 9 || … || Tn – Rename the corresponding transactions in H and SH to obtain the following transaction index pattern for SH: SH = T 1 || T 2 || T 3 || … || Tn (1) – Suppose that H has wormhole (contradiction): Not T <<< … Tj <<<H Ti <<<… <<< T, where i is the minimum possible transaction index in this wormhole. – By the minimality of i, we must have Ti <<<SH Tj according to (1) Tj<<<SH Ti is impossible. Tj<<<H Ti is not possible (because H and SH are equivalent) H has no wormholes. 49

Wormhole Theorem: Proof (cont’d) 2. H has no wormholes H is isolated: This proof is by induction on the number of transactions, n, that appear in the history, H. (1) If n < 2, then any history is a serial history any history is isolated. (2) Suppose the induction hypothesis is true for n - 1 transactions i. e. , “H has no wormholes H is isolated” is true for n-1 transactions (3) Considering some history H of n transactions that has no wormholes, prove that H is isolated (see next page) 50

History H has no wormholes H is isolated DEP(H) n transactions T a … History H T’ T* No wormholes → We can find a transaction without outgoing edges 51

History H has no wormholes H is isolated DEP(H) n transactions T … History H a T’ T* b Remove T* c This must be the dependency graph for H’ Remove T* History H’ T … n-1 transactios T’ DEP(H’) without T* 52

History H has no wormholes H is isolated DEP(H) n transactions T H’ has no wormhole → H’ is isolated → DEP(H’) = DEP(SH’) … History H a T’ T* b Remove T* c Remove T* History H’ T … n-1 transactios T’ DEP(H’) without T* d = due to induction hypothesis without T* DEP(SH’) 53

History H has no wormholes H is isolated DEP(H) n transactions T DEP(SH’) with T* added … History H a T’ T* b Remove T* c Remove T* History H’ T … n-1 transactios T’ DEP(H’) without T* T* T* + e 1 d = due to induction hypothesis without T* DEP(SH’) 54

History H has no wormholes H is isolated DEP(H) n transactions T a = T’ T* b Remove T* Some DEP(SH) … History H e 3 c Remove T* History H’ T … n-1 transactios T’ DEP(H’) without T* e 5 due to Step c Ξ DEP(SH’) with T* added + DEP(H’) with T* T* e 1 e 2, e 3 & e 5 → DEP(H) and DEP(SH) are equivalent d = due to induction hypothesis = due to Step d T* T* e 2 without T* DEP(SH’) 55

Wormhole Theorem: Proof (cont’d) (a) Pick a transaction T and construct the sequence S = < T, T , … T* > such that T <<<H … <<<H T* Since H has no wormhole, S must be finite. (b) Remove all actions due to T* from H to construct a history H’: H = < < Ti, a, o > H | Ti T* > (c) Construct DEP(H’) by removing from DEP(H), T* & its incoming edges: DEP (H ) = { < T, < o, i >, T > DEP(H) | T T* } (d) H has no wormholes H is isolated ( due to induction hypothesis – page 50) H has an equivalent serial history SH’ (e) DEP(SH) can be constructed by adding T* and its incoming edges to DEP(SH’) DEP(SH) = DEP(SH ) { < T , < o, i >, T* > DEP(H) }56

Wormhole Theorem: Proof (cont’d) (e) DEP(SH) can be constructed by adding T* and its incoming edges to DEP(SH’) DEP(SH) = DEP(SH ) { < T , < o, i >, T* > DEP(H) } = DEP (H ) { < T , < o, i >, T* > DEP(H) } /* DEP(SH’) = DEP(H’) due to Step (d) = { < T, < o, i >, T > DEP(H) | T T* } { < T , < o, i >, T* > DEP(H) } /* DEP(H’) = { < T, < o, i >, T > DEP(H) | T T* } due to Step (c) = Adding T* and its incoming edges to the DEP(H) with T* and its incoming edges removed = DEP(H). /* Thus, the induction step is proven. 57

Serializability Theorem • The wormhole Theorem is the basic result from which all the others follow. • The wormhole Theorem can be stated in many different ways. Serializability Theorem: A history H is isolated if, and only if, <<<H implies a partial order of the transactions (i. e. , no cycles). • An isolated history is also called a serializable schedule or a consistent schedule. 58

Serializability Theorem Same dependency graph DEP(H) DEP(SH) 59

Serializability Theorem DEP(H) DEP(SH) If there had been a cycle, it would have produced dependency graph arrows pointing up in the serial history SH (back in time). [ Executing SH would violate a dependency in the original history H [ DEP(H) ≠ DEP(SH) [ H is not a serializable schedule 60

Serializability Theorem DEP(H) DEP(SH) If there had been a cycle, it would have produced dependency graph arrows pointing up in the serial history SH (back in time). [ Executing SH would violate a dependency in the original history H [ DEP(H) ≠ DEP(SH) [ H is not a serializable schedule 61

Locking Theorem: Lemma • SHRINK(T) the index of the first unlock step of T in the history H. • Lemma: Let T and T be two well-formed and 2 -phase transactions of a legal history H. T <<<direct T SHRINK(T) < SHRINK(T ) T→T’ is in dependency graph 62

Locking Lemma - Proof Lemma: Let T and T be two well-formed and 2 -phase transactions of a legal history H. T <<<direct T SHRINK(T) < SHRINK(T ) T <<<direct T T’ waits for T on some lock T’ cannot have this lock until after SHRINK(T) T’ cannot release its first lock until after Number of Locks SHRINK(T) < SHRINK(T’) T <<< T’ T Release lock X Obtain lock X T T’ Request lock X SHRINK(T) T ’ is blocked SHRINK(T’) Release lock X T’ This occurs after SHRINK(T) Time 63

Locking Lemma – Formal Proof Lemma: Let T and T be two well-formed and 2 -phase transactions of a legal history H. T <<<direct T SHRINK(T) < SHRINK(T ) T <<<direct T there are some steps i < j of H such that H[i] = < T, a, o >, H[j] = < T , a , o >, and a are conflicting actions. T T’ H is legal H does not grant conflicting locks to two different transactions at the same time. there must be a k 1 and k 2 such that i < k 1 < k 2 < j, H[k 1] = < T, UNLOCK, o >, and H[k 2] = < T’, LOCK, o >. T and T are 2 -phase SHRINK(T) k 1 < k 2 < SHRINK(T ) 64

Locking Theorem: Proof Locking Theorem: If all transactions are well-formed and two-phase, then any legal history will be isolated (locking correctly done provides the isolation). This proof is by contradiction. – Suppose that H is not isolated. Wormhole theorem there must be a sequence < T 1, …, Tn > such that: T 1 <<<direct T 2 <<< direct … <<< direct Tn <<< direct T 1 – Lemma SHRINK(T 1) < SHRINK(T 2) < … < SHRINK(T 1) – “SHRINK(T 1) < SHRINK(T 1)” is not possible ! H cannot have any wormhole. 65

Degenerate Transactions A transaction is degenerate if it does any of the following: – Useless lock: Locks something that it never reads or writes – Orphan unlock: Unlocks something that it has not locked. – Orphan lock: Ends without unlocking some of its locks (impossible if it ends with COMMIT or ROLLBACK). 66

Locking Theorem - Converse Locking Theorem (converse): If a transaction is not well-formed or not two-phase, then it is possible to write another transaction such that the resulting pair has a legal but not isolated history (unless the transaction is degenerate). Need to prove two things: • Not well-formed legal but not isolated • Not two-phase legal but not isolated 67

Locking Theorem - Converse (Proof) Not well-formed legal but not isolated – Suppose that transaction T is not well formed and not degenerate: T = < <T[i] | i<k > || <T, ak, o> || <T[i] | i>k > > ak is a READ or WRITE that is not covered by a lock. READ case is proved here; WRITE case is similar – We can write a transaction T : T = < <T , XLOCK, o>, <T , WRITE, o>, <T , UNLOCK, o> T is 2 -phase and well-formed. Not covered > by lock – Consider the history: R W W H = <T[i] | i<k > || < T [1], T [2], T[k], T [3], T [4] > || < T[i] | i > k > H is legal since no conflicting locks are granted on object o at any point of the history. – H implies that <T , <o, j>, T> and <T, <o, j>, T > must be in DEP(H) for some j. T<<< T <<<T T is a wormhole. H is not isolated (Wormhole Theorem). 68

Locking Theorem (Converse): Proof (cont’d ) Not two-phase legal but not isolated – Suppose flat transaction T is not two-phase: T = < < T, a 1, o >, . . , < T, aj, o 1 >, . . , < T, ak, o 2 >, … > UNLOCK SLOCK or XLOCK – We can write a transaction T : A loop in dependency graph T = < < T , XLOCK, o 1 >, < T , XLOCK, o 2 >, < T , WRITE, o 1 >, < T , WRITE, o 2 >, < T , UNLOCK, o 1 >, < T , UNLOCK, o 2 > > T is two-phase and well-formed. – Consider the history: Since T is not degenerate, it read/write 02 after the UNLOCK at Step k H = < T[i] | i j > || T || < T[i] | i > j > H is legal Since T is not degenerate, it read/write 01 before the UNLOCK at Step j – < T, < o 1, m >, T > and < T , < o 2, n >, T > must be in DEP(H). T <<<H T T is a wormhole in H. H is not an isolated history (according to the Wormhole Theorem). 69

Locking Theorem • Locking Theorem: Program should be correct ! “If every transaction is well-formed and two-phase, a history is isolated. ” Note: An isolated history does not imply that its transactions are well-formed and two-phase. Example: A serial history is isolated even if its transactions are not well-formed or two-phase. (In fact, we don’t even need locking in this case). • It would be nice if the Locking Theorem were stronger and read: “A history is isolated if, and only if, every transaction is well-formed and two-phase. ” 70

Rollback Theorem - Proof Rollback Theorem: A transaction that does an UNLOCK of an XLOCK and then does a ROLLBACK is not wellformed and, consequently, is a potential wormhole (unless the transaction is degenerate). <XLOCK o> … <UNLOCK o> <ROLLBACK>

Rollback Theorem - Proof Rollback Theorem: A transaction that does an UNLOCK of an XLOCK and then does a ROLLBACK is not wellformed and, consequently, is a potential wormhole (unless the transaction is degenerate). <XLOCK o> <WRITE o> … Since the transaction is not degenerate, it must write o <UNLOCK o> <ROLLBACK>

Rollback Theorem - Proof Rollback Theorem: A transaction that does an UNLOCK of an XLOCK and then does a ROLLBACK is not wellformed and, consequently, is a potential wormhole (unless the transaction is degenerate). <XLOCK o> <WRITE o> … <UNLOCK o> <ROLLBACK> 1) <XLOCK o> <WRITE o> … <UNLOCK o> <WRITE o> UNDO - not covered by a lock The second WRITE is not covered by a lock the transaction is not well-formed the transaction is a potential wormhole. 2) <XLOCK o> <WRITE o> … <UNLOCK o> <XLOCK o> <WRITE o> The second WRITE is covered by a lock the transaction is not two phase the transaction is a potential wormhole. UNDO is covered by a lock IMPORTANT: XLOCK’s should be kept until the end 73

Rollback Theorem - Proof (2) Rollback Theorem: A transaction that does an UNLOCK of an XLOCK and then does a ROLLBACK is not well-formed and, consequently, is a potential wormhole (unless the transaction is degenerate). – T acquires XLOCK on object o & T is not degenerate T write o – The ROLLBACK action has to WRITE (UNDO) object o again. – The second WRITE is not covered by a lock the transaction is not well-formed the transaction is a potential wormhole. – It might be argued that the ROLLBACK should reacquire XLOCK on all such unlocked objects. However, that would violate the 2 -phase restriction and, in doing do, introduce another potential wormhole. 74

Summary of the Isolation Theorems • The implication of these theorems is that a transaction should: 1. Be well-formed: it should cover all actions with locks 2. Set XLOCK on any data it writes. 3. Be 2 -phase: it should not release locks until it knows it needs no more locks. 4. Hold XLOCKs until COMMIT or ROLLBACK. If these rules are followed, the execution history will give each transaction the illusion it is running in isolation. • On the other hand, if any of the above rules are violated, then the transactions may have inconsistent inputs or outputs. 75

Isolation: In Practice Most applications do not require true isolation. Implementers make compromise between correctness and performance. – Typical SQL system default is to ignore READ WRITE dependencies Cursor stability One the cursor is advanced to the next item, another transaction can WRITE the last item (i. e. , READ →WRITE) users must request true isolation as an option. – Many systems also allow the option to ignore WRITE READ isolation. Browse access allows queries to scan the database without acquiring locks and without delaying other transactions. 76 These queries ignore XLOCK’s by other transactions

Degree of Isolation • Degree 3: This is true isolation. – Lock protocol: two-phase and well-formed • Degree 2: A 2° isolated transaction has no lost updates and no dirty reads. – Lock protocol: two-phase with respect to XLOCKs and well-formed – Common name: Cursor stability Not 2 -phase w/r to read Ignore READ → WRITE Repeatable read is not guaranteed 77

Degree of Isolation(cont’d) • Degree 1: A 1° isolated transaction has no lost updates. – Lock protocol: two-phase with respect to XLOCKs and well-formed with respect to writes – Common name: browse mode Read operations do not require locking Ignore “READ → WRITE” & “WRITE → READ” • Degree 0: A 0° isolated transaction does not overwrite another transaction’s dirty data if the other transaction is 1° or greater. – Lock protocol: well-form with respect to write – Common name: anarchy /* ignore all dependencies */ 78

DEGREE OF ISOLATION Degree of Isolation Read WF 2 P Write Ignored Effect N/A True isolation Yes Read Write unrepeatable reads Yes Read Write Read unrepeatable reads dirty reads Yes No Read Write Read Write unrepeatable reads dirty reads lost updates WF 2 P Degree 3 Yes Yes Degree 2 (cursor stability) Yes Degree 1 (browse mode) Degree 0 (anarchy) No No No 79

Knowing What They Are Doing “Cursor stability” applications do not repeat read operations anyway ! As long as applications know what they are doing, better performance can be achieved without causing anomalies 80

Mixing Isolation Levels A 1 transaction’s inputs are 1 isolated the transaction may get dirty reads. such data may not satisfy the system inconsistency constraints. If the transaction uses dirty reads to update the database, then other transactions will see inconsistent values and may malfunction. such transaction systems assume that the 1 transaction knows what it is doing. Example: Most systems reserve 1 isolation (browse mode) for read-only transactions. 81

SQL systems • The lower degrees of isolation allow transactions to acquire fewer locks or to hold the locks for a shorter time. • SQL systems implement a slightly better form of isolation than pure 2. Pure 2 : After reading the cursor, the SLOCK is immediately released Cursor stability: To prevent lost updates, most SQL systems keep a shared lock on the record currently addressed by a cursor (see next page). 82

Cursor Stability Examples Look up balance Another transaction can update the balance at this time, and the update will be lost. “balance” value may be stale An SQL query is an atomic operation Account record is locked during this time period 83

Phantoms select * from EMP where eyes = “blue” and hair = “red”; • Technique 1: Locks all the individual records that satisfy the where clause (predicate) Problem: What is to prevent someone else from inserting a new, blue eyed, red-haired EMP during the execution of the above query. Just inserted Name Eyes Hair Andrew Blue Red Casey Blue Red Jane Blue Red Kailee Blue Red First match Second match “Casey” is a phantom ! 84

Phantoms(cont’d) select * from EMP where eyes = “blue” and hair = “red”; • Technique 2: Locks the whole EMP table Problem: significantly reduces concurrency (unacceptable) 85

Predicate Locks Rather than locking an individual object, a predicate lock can specify a subset of the database to which the lock applies. Example: <t, slock, emp. eyes = “blue” emp. hair = “red”> Transaction t requests slocks for all the emp tuples that satisfy this predicate. Predicate lock is an elegant solution for phantoms. 86

Predicate Locks - Compatibility <t, mode, p> and <t’, mode’, p’> are compatible if 1. t = t’, or 2. mode = mode’ = slock, or 3. (p AND p’) = FALSE /* t and t’ access a different Examples: subset of the database */ < t, slock, emp. eyes = “blue” emp. hair = “red” > < t, xlock, emp. eyes = “blue” > < t , slock, emp. eyes = “blue” > < t , xlock, emp. eyes = “blue” > compatible incompatible 2 3 87 1

Predicate Locks: Scheduling A predicate lock system can schedule transactions dynamically as follows: – Each time a transaction requests a predicate lock, the system compares the lock request with the other granted and waiting predicated locks. – If the lock request is compatible with all other requests, • it is added to the granted set and granted immediately. • Otherwise, it is marked as waiting and added to the waiting list. 88

Predicate Locks: Scheduling (cont’d ) • When a transaction ends, the waiting predicates in the waiting list are considered in turn. – The scheduler grants each predicate that is compatible with the new granted group, and adds it to the granted set. – When it encounters the end of the waiting list, or an incompatible predicate lock in the list, it stops and waits for the next lock or unlock request. This scheme is very expensive not used in practice. 89

Granular Locks • Predicate locks (predicate comparisons) are too expensive. Simplified predicate locks, called granular locks, are typically used in practice. • In granular locking, the system picks a fixed set of predicates (in essence, to precompute the predicate locks): – Each predicate is represented by a name, and – the predicates are organized into a lock hierarchy (for compatibility check). 90

Granular Locks - Example Chicago Los Angeles Orlando Jerry Example: – A finest granularity lock: Sites = “Orlando” Files = “Phone” Records = “Steven” – To lock all the data in Chicago (coarse grain) Sites = “Chicago”. 91

Implicit Locking When a transaction sets a lock on a granule at a given level of the granularity, it is implicitly locking in the same mode all its descendants. Example: when a lock is set on a relation, all tuples of this relation are locked in the same way. 92

Incompatible Locks It is necessary to guarantee that – after setting a basic lock (SLOCK, XLOCK) on a given vertex of the granularity hierarchy by a transaction T, – no other transaction T obtains an incompatible lock on any of its predecessors. 2 Sometime later, T’ XLOCKs “Chicago” (must be blocked) lock g n i y n f Veri ibility ca at ive comp y expens r be ve Los Angeles Chicago Orlando Jerry 1 Transaction T locks the file Phone 93

Intent Lock Mode Sometime later, T’ XLOCKs “Chicago” (must be blocked) Chicago Jerry Los Angeles Orlando Transaction T locks the file Phone There must be a way to prevent T from setting xlock on “Chicago”. A solution: using Intention locks. – Setting an intention lock at a given level implies basic locking at all lower levels. – The modes of intention locks depend directly on the modes of the basic lock. 94

Intention Locking - Example INTENT Chicago INTENT Los Angeles Orlando INTENT SHARED Jerry Setting a record-granularity SLOCK on “Jerry” consists of the following sequence of lock requests. LOCK “IN DATABASE” NODE = “Chicago” FILE = “PHONE” NAME = “Jerry” IN INTENT MODE IN SHARED MODE Intent mode represents the intention to set locks at a finer granularity. 95

Intention locks • IX: Intent to set shared or exclusive locks at finer granularity. • IS: Intent to set shared locks at finer granularity. • SIX: A shared lock on a given granule with intent to set finer-granularity shared or exclusive locks. 96

Intention locks - Compatibility Granted Mode Request Mode IS IX S SIX Update X None IS IX + + + + + - We can- block -the lock -request later if the IX actually has a - conflicting + X lock - in the- lower level of the hierarchy - “+”: compatible S SIX Update X “-”: incompatible 97

Intention locks - Compatibility Granted Mode Request Mode IS IX S SIX Update X None IS IX + + + + + - “+”: compatible S SIX Update X + + + We cannot grant the IX - since - nothing - will request prevent it from having an X - level - of thelock+in the lower hierarchy, otherwise. - “-”: incompatible 98

Intention locks - Compatibility Granted Mode Request Mode IS IX S SIX Update X None IS IX + + + + + - “+”: compatible S SIX Update X +We cannot + grant- the SIXrequest because the IX - have - an X lock - in themight lower level of the hierarchy + + - “-”: incompatible 99

Intention locks - Compatibility Granted Mode Request Mode IS IX S SIX Update X None IS IX S SIX Update X + + + + + - - - “+”: compatible “-”: incompatible 100

Common Deadlocks A typical construct in programs is to read a record and rewrite some of its fields. – If two different transactions both read an object at nearly the same time, then they both request it in exclusive mode. That creates a deadlock cycle. T 1 is now blocked T 2 is now blocked T 1 SLOCK A T 2 SLOCK A T 1 XLOCK A T 2 XLOCK A T 1 Wait-for Graph T 2 101

Wait-for Graph ≠ Dependency Graph T 1 t nex i act on T 2 serializable schedule Scheduler T 3 T 4 T 5 actions in arriving order Blocked Actions Result in a wait-for graph Result in a dependency graph A cycle in the wait-for graph does not imply a non-serializable schedule 102

Common Deadlocks A typical construct in programs is to read a record and rewrite some of its fields. – If two different transactions both read an object at nearly the same time, then they both request it in exclusive mode. That creates a deadlock cycle. T 1 SLOCK A T 2 SLOCK A T 1 XLOCK A T 2 XLOCK A T 1 Wait-for Graph T 2 – A study of system R showed that virtually all deadlocks in that system were of this form. – A possible solution is to make shared locks incompatible - Reduce concurrency ! 103

Update Locks A better solution: use UPDATE lock mode. – When an SQL cursor is declared with the clause For UPDATE, record locks acquired by the cursor are acquired in UPDATE mode. Cursor acquired in UPDATE mode – If the cursor updates the data • then the update mode lock is converted to an XLOCK, which is held to transaction commit. • else the update mode lock is downgraded to shared mode – UPDATE is not compatible with a subsequent UPDATE request No Update data SLOCK mode Improve concurrency Yes XLOCK mode 104

Update locks - Compatibility Granted Mode Request Mode IS IX S SIX Update X None IS IX S SIX Update X + + + + + - - - The compatibility matrix for update mode locks is asymmetric: • Update is compatible with share, but share is not compatible with update • This allows the updater to read, but delays other transaction readers and updaters, since the transaction is about to update 105

Update Lock Example T 2 deferred • A typical construct in programs is to read a record and rewrite some of its fields. T 1 UPDATE A T 2 UPDATE A (T 2 is blocked) T 1 WRITE A (the update lock becomes XLOCK) … T 1 COMMIT T 2 WRITE A (T 2 is resumed, the update lock becomes XLOCK) … T 2 COMMIT • No deadlock in the above action sequence 106

Granular Locking Protocol • Acquire locks from root to leaf • Release locks from leaf to root. • To acquire an S mode or IS mode lock on a non-root node, its predecessor must be held in IS mode or higher (one of {IS, IX, S, SIX, U, X}) • To acquire an X, U, SIX, or IX mode lock on a non-root node, its predecessor must be held in IX mode or higher (one of {IX, SIX, U, X}) 107

Granular Locking Example • Transaction T 2 has record-granularity locks on File-2, and is waiting for T 1 to release an XLOCK on one of these records. • Transaction T 3 wants to lock the entire database in shared mode. It is waiting for T 1 and T 2 to complete. 108

Granular Locking Example • Transaction T 2 has record-granularity locks on File-2, and is waiting for T 1 to release an XLOCK on one of these records. • Transaction T 3 wants to lock the entire database in shared mode. It is waiting for T 1 and T 2 to complete. 109

Key-Range Locking • A key range is denoted by the first key in the range and by the first key after the range. – Example: [R, S) all k, such that R k < S. • A fixed number of key ranges is predetermined – Example: [A, N), [N, X), [X, ) • Locking protocol: – A transaction acquires a lock on the key range before it accesses records in the key range. – Since key ranges are granules within files, intent mode locks on the file (and on any coarser-granularity objects) must first be acquired in accordance with the granular lock protocol. 110

Why Key-Range Locking works ? If the key ranges are thought of as predicates, they are simply predicate locks covering the records. key-range locking also prevents phantoms. Example: [R, S) all k, such that R k < S. This is a predicate 111

Dynamic Key-Range Locks • Standard key-range locking is not adaptive because there a fixed number of key ranges. • Dynamic key-range locking assigns a key range to every record in the file. 112

Dynamic Key-Range Locks (Example) • Example: The file has three records: W, Y, Z. – Current key ranges: [A, W), [W, Y), [Y, Z), [Z, ) 1 – After the insertion of X, 1 becomes: [A, W), [W, X), [X, Y), [Y, Z), [Z, ) /* Split – After the deletion of Y, 1 becomes: [A, W), [W, Z), [Z, ) /* Merge • Dynamic key ranges appear and disappear. This makes the locking protocol more complex than static key-range locking. 113

Dynamic Key-Range Locking - Protocol Given a sorted file of three records: W, Y, and Z. key ranges: [A, W), [W, Y), [Y, Z), [Z, ) • There are four operations of interest: 1. Read unique: read a unique record (say X), given its key. The transaction T must SLOCK [W, Y) preventing anyone else from updating that key range. Note: If the record is not found, the lock prevents a phantom insertion of X by another transaction Insert X Read X S [A, W), [W, Y), [Y, Z), [Z, ) 114

Dynamic Key-Range Locking - Protocol (Cont’d) Given a sorted file of three records: W, Y, and Z. key ranges: [A, W), [W, Y), [Y, Z), [Z, ) • There are four operations of interest: 2. Read next: read the next record (i. e. , Y ) after the record W. The transaction T must • hold a lock on [W, Y) - to prevent any phantoms from appearing in this key range, then • requests a SLOCK on key Y - this implicitly locks [Y, Z). Insert X Read next, i. e. , read “Y” S S [A, W), [W, Y), [Y, Z), [Z, ) 115

Dynamic Key-Range Locking - Protocol (Cont’d) Given a sorted file of three records W, Y, Z. key ranges: [A, W), [W, Y), [Y, Z), [Z, ) • There are four operations of interest: 3. Insert: insert a record X between W and Y. • The old key range [W, Y) (soon to be [W, X) ) must be locked in exclusive mode, then • the new key range [X, Y) should be locked in exclusive mode. Insert X X X [A, W), [W, Y), [X, Y), [Y, Z), [Z, ) Become [W, X) New range 116

Dynamic Key-Range Locking - Protocol (Cont’d) Given a sorted file of three records W, Y, Z. key ranges: [A, W), [W, Y), [Y, Z), [Z, ) • There are four operations of interest: 4. Delete: delete record Y. • First, lock [Y, Z) in exclusive mode, then • Lock [W, Y) (soon to be [W, Z) ) in exclusive mode. • When these two locks are granted, perform the delete. Delete Y X X [A, W), [W, Y), [Y, Z), [Z, ) Become [W, Z) 117

Dynamic Key-Range Locks: Problem • If a key-range lock has to wait to be granted; then when it is granted, the key range may have disappeared. • Example: The file has three records W, Y, Z. current key ranges: [A, W), [W, Y), [Y, Z), [Z, ) T 1 T 2 TIME locks [W, Y) in XLOCK Remark T 1 wants to insert X locks [X, Y) in XLOCK Waits for [X, Y) T 1 aborts T 2 wants to read record previous to “Y” the key range returns to [W, Y) lock on [X, Y) is granted but [X, Y) disappears. Solution: T 2 should release the lock on [X, Y), and request [W, Y) 118

Lock Conversion • Question: Can a transaction requests a look in shared mode, and later converts it to exclusive mode ? – The transaction should not release the shared lock, and then request the exclusive lock. • Reason: 2 P-locking is violated ! – If a lock is held in one mode and requested in a second mode, the request is converted to the max of the two modes 119

Lock Conversion Example • If the lock is held in IX mode and requested in U made, then the request is converted to a request for a X mode lock. conversion Requested mode Current mode 120

Lock Escalation If the system makes a mistake and uses fine granularity when coarse granularity is appropriate, the lock system may run out of storage. – Typical solution: If the number of locks held by a transaction reaches an escalation threshold, the lock manager converts fine-granularity locks to coarse locks. – Example: • looks for an IS lock with many children set in S mode, • converts the parent IS mode lock to an S mode, and • releases all the S mode locks on the children. IS Page 101 Record S 112 S 113 Record … Lock escalation Page 101 Record 112 Record 113 S Records are implicitly locked in shared mode Record … 121

Lock Escalation – Rdb’s Approach /rdb is a high-performance relational database management and application development system designed for Unix and implemented as a suite of shell-level commands. • It defaults to file-granularity locks. • If there is any contention on the filegranularity lock, each holder of the lock de -escalates to finer granularity on demand. 122

Hotspots • The virtue of page-granularity locking is that: – It is very easy to implement, – It works well in almost all cases, and – It can be used to give phantom protection (by locking the page on which the phantom would have to appear). • However, page-granularity locking can create hotspots. Examples: – Directories are typically small, high-traffic read-write databases. They can become hotspots. – The debit/credit transaction demonstrates main-memory hotspot problems. All the branch records (usually less than 100) typically fit in a couple of pages. – Inserting in a sequential file can give page locking systems heartburn, since locking the page containing the end of the file prevents any other transaction from inserting at the end of the file. 123

Sequential Nested Transaction Locking • The effects of a nested transaction are made durable and public only when the toplevel transaction invokes COMMIT work. • Implementation: – All subtransactions run under the same TRID. – Locks are maintained as a stack. When the transaction does a nested BEGIN work, that establishes a savepoint in the stack. – If the transaction does a ROLLBACK to one of these savepoints, all locks subsequent to the lock in the savepoint are released (i. e. , popped from the stack). 124

Parallel Nested Transaction Locking • The parallel subtransactions are each viewed as atomic units (they have A, C, and I, but not D of ACID) – Each subtransaction gets a unique TRID. – The nested transactions are viewed as nodes of the transaction family tree. • At commit, both inherited and acquired locks are given back to the parent; but at ROLLBACK only inherited locks are returned. 125

Parallel Nested Transaction Locking (cont’d) When a transaction requests a lock that is already held by another transaction, the following test is run: Is the other transaction in the same family? – If yes, is it an ancestor ? 1 • If yes, inherit 1 2 • If no, wait, then inherit. 2 3 – If no, wait. 3 acquired 3 1 2 Family F 1 Family F 2 126

Deadlocks • A situation in which each transaction of the deadlock set is waiting for another member of the set. • These transactions will not advance unless one transaction stops waiting. A solution: one of the transactions must rollback. Read-Write dependen cy Slock 1(x) Xlock 2(x) Slock 2(x) T 1 T 2 The Wait-for graph time Xlock 1(x) Read-Write dependency 127

Deadlock Avoidance Techniques 1. Linearly ordering resources and requesting them only in that order (avoiding wait-for cycles). T 1 T 2 (WAIT) Only Waiting for a transaction further to the right T 2 waits for T 1 cannot wait for T 2 2. Asking job to predeclare their maximum needs. 128

Deadlock Detection 1. Whenever a transaction waits more than a certain time, § declare that something is wrong, and § rollback that transaction. 2. After a transaction times out, the system runs a deadlock detector that looks for cycles in the wait-for graph. 129

Lock Data Structure • Each lock has two lists: granted list & waiting list • It is also convenient to have a transaction wait list to know where a transaction is waiting l 1 l 2 granted list waiting list t 2 is ahead of t 3 in waiting list • The edge T T should be added to the wait-for graph if T is in the waiting list and: – T is in the granted list or is ahead of T in the waiting list, and 130 – the lock modes m and m are incompatible.

Wait-for Graph and Deadlock Detection The wait-for graph can be examined for cycles by doing a depth-first search that runs in time proportional to the number of edges. Visiting a node (i. e. , transaction) that has been visited before A loop is detected A deadlock 131

Distributed Deadlock • The transaction wait list is distributed. • Typically, each site maintains a local fragment of the transaction wait list containing all transaction waiting at that node. How to do global deadlock detection ? 132

Centralized Deadlock Detection • Every site sends its lock request/release requests to a central control site which perform deadlock detection and resolution • Advantage: Simple • Disadvantages – Inefficient – Single point of failure – Phantom deadlock (a cycle caused by a transient arc) 133

Phantom Deadlocks • Since the wait-for graph is constantly changing, it is not possible to get a consistent picture of the graph. • Phantom deadlocks are due to cycles in the graph caused by transient arcs. 134

Phantom Deadlocks – At time t 1 Collecting subgraphs T log lock T Site 1 (T is finishing writing the log) – At time t 2 T T Site 2 (collecting subgraphs) Site 3 (serving T) Transient tarc T requests a service at site 3 T Site 1 (completed the log operation for T , currently serving T) T T Site 2 (a phantom deadlock is detected) Collecting subgraphs T Site 3 (serving T) Good news: the deadlock detector will not miss any real deadlocks. 135

Sharing Local Information • Special node “External” (EX) abstracts non-local portion of the global wait-for graph • For each cycles (e. g. , EX T 1 T 2 T 3 T 4 EX), sends this path to all sites where a subtransaction of T 1, T 2, T 3, T 4 may reside Some transaction outside is waiting for T 1 T 2 EX T 3 T 4 is waiting for some outside transaction 136

Path Pushing • Share wait-for information on potential global cycles • Each site – waits for wait-for information from other nodes – combines with local wait-for information Potential – breaks cycles if detected global cycle EX EX A loop Deadlock ! 137

Challenge • There is no standard representation of the wait-for graph. • Not all RMs provide their part of the wait-for graph. 138

Deadlock Resolution • Once a deadlock is found, it must be broken. The standard resolution technique is to roll back the least expensive transactions, e. g. , the one with the shortest logs. 139

Deadlock: Summary • Wait-for graphs have the following characteristics: – sparse (waits are very rare) – Most cycles are very short (two) – Cycles are very rare • Most systems use the following approach: – The system falls back on timeout, plus – a simple local deadlock detector that detects local deadlocks. Address global cycle 140

A Simple Model for Deadlock Analysis • The system consists of R records. • There are n + 1 concurrent transactions. • Each transaction performs r + 1 actions. Each action access a random record from the set of R records. • n·r << R : that is, most of the database is unlocked most of the time. Database size: R records . . . r + 1 actions T. . . T T T n + 1 concurrent transactions 141 T

Probability of Deadlock (1) The probability that a single request waits is PW = ( n · r/2 ) / R = n · r / 2·R The n other transactions Avg. num of locks held by each transaction (assumption) Database size: R records . . . r + 1 actions T. . . T T T n + 1 concurrent transactions 142 T

Probability of Deadlock (1) The probability that a single request waits is PW = ( n · r/2 ) / R = n · r / 2·R The n other transactions Avg. num of locks held by each transaction (assumption) The probability that a particular transaction T, waits in its life time is None of r requests has to wait The probability that a single PW(T) = 1 – (1 – PW)r request does not have to wait. =1–[1– PW(T) = n · r 2 / 2·R ] nr<<R PW<<1 these high-order terms can be dropped 143

Probability of Deadlock (2) The probability that some transaction T is deadlock: PD(T) = PW(T)2 / n + 1 · PW(T)3 + 2 · PW(T)4 +. . . Prob. for waiting for some transaction – PW(T) T 2 T 1 1/n · PW(T) Prob. for waiting for a specific transaction (out of n transactions) In general, the probability of a cycle of length i is proportional to PW(T)i Since we typically have PW(T)<0. 1 These terms can be dropped. 144

Probability of Deadlock (3) The probability that any transaction deadlocks: PD = PD(T 1) + PD(T 2) + … + PD(Tn+1) Multiprogramming level must be kept low Fine-granularity locking, browse mode locking and shared locks are important. They all tend to reduce the transaction collision cross sections. PD n · PD(T) = n 2 · r 4 / 4·R 2 PD(T) = PW(T)2 / n PW(T) = n r 2 / 2 R deadlocks are very rare 145

Transaction’s Cross Section Transaction’s cross section is the time-space product of the amount of data the transaction locks and the lock duration. Transaction’s cross section of T 4 Time T 3 T 2 T 1 T 4 Locks 146

Reducing transaction’s collision cross section Time Collision Cross Section T 2 T 1 T 3 T 4 Locks • T 2 and T 3 occur concurrently. However, they are isolated because they access different data sets. • T 3 and T 4 access the same data. However, they are isolated because they occur during different time spans. • Since T 2 collides with T 1, isolation problems can occur. 147

Reducing collision Reducing the transaction’s collision cross section minimizes lock wait increases concurrency & reduces deadlocks Critic al for ho t spots Time Potential problem T 2 T 1 T 3 T 4 148 Locks

Field Calls • Field calls help to reduce the collision cross section. Need milk • Field call is an action on a specific hotspot record (e. g. , milk inventory in a supermarket). • The action consists of two parts: a predicate, e. g. , having milk in the inventory ? a transform, e. g. , sell some of the milk cartons 149

A Field Call Example Transform quantity_on-hand = quantity_on_hand - : delta Update inventory Set quantity_on_hand = quantity_on_hand - : delta Where item = “milk” and quantity_on_hand : delta; Predicate quantity_on-hand > “delta 150

Idea • After checking the inventory, immediately release the share lock – To increase concurrency • At commit time, check the inventory again before the sale – To be sure there are still enough items in the inventory at this time 151

Field Calls - Details 1. Lock the record corresponding to “Milk Inventory” in short duration shared mode. 2. Test the predicate against the “Milk Inventory” record, and release the shared lock afterward. 3. If the predicate is true, the system generates a REDO log record containing the predicate and the transform. 4. At commit phase 1, 1. All deferred REDO log records are examined. 2. Shared locks are acquired on predicate-only records, 3. Exclusive locks are acquired on records mentioned in transforms. 5. At commit phase 2. 1. If all predicates remain true, apply each transform and free the locks 152 2. Otherwise, rollback the transaction

Field Calls - Illustration T 1 begins predicate time 153

Field calls: Example update inventory Set qoh = qoh + : delta where item = “milk” AND qoh delta; Milk 1000 Original qoh 154

Field calls: Example Checkout Clerk C 1 -5 Stocking Clerk S Checkout Clerk C 2 -10 +300 update inventory Set qoh = qoh + : delta where item = “milk” AND qoh delta; C 2 updates S updates C 1 updates Milk 1000 990 1285 Original qoh time C 1 S C 1 C 2 S C 2 C 2 S S C 1 155

Field calls: Recovery • Field calls never have any transaction undo work. If the transaction aborts, the field calls have not made any changes to the database; thus, they are already “undone”. • Redo of field calls is a little more subtle. Since transformations are deferred to phase 2 of commit, it is safest to delay inserting field call transforms into the REDO log until the end of phase 1 of commit. Reason: The field call transforms will appear in the order that they were applied (rather than the order in which the field calls themselves were issued). 156

Field calls: Recovery Example: time 1. 2. 3. 4. transaction T t(h) t transaction T t (h) commit • Effect (commit order): hotspot record h t transaction T’ log …, t(h), t (h) redo order t(t (h)) • System crashed, the redo logic reconstructed the value of h: t (t(h)). Note: commit order redo order If t (t(h)) t(t (h)) the wrong value is reconstructed. 157

Field Calls – Defer REDO log 1. Lock the record corresponding to “Milk Inventory” in short duration shared mode. 2. Test the predicate against the “Milk Inventory” record, and release the shared lock afterward. 3. If the predicate is true, the system generates a REDO log record containing the predicate and the transform. Defer until 4. At commit phase 1, end of 1. All deferred REDO log records are examined. 2. Shared locks are acquired on predicate-only records, 3. Exclusive locks are acquired on records mentioned in transforms. 5. At commit phase 2. Phase 1 1. If all predicates remain true, apply each transform and free the locks 158 2. Otherwise, rollback the transaction

Refinement of Field Call If we can preserve the truth of the predicate between the time the transaction first makes the field call and the time the predicate is revaluated at phase 1 of commit, then we can prevents transaction predicate failure at commit, and the consequent transaction aborts. T 1 begins predicate time 159

Escrow Locking • Associated with each hotspot field is a range of values. Note: m = n if there is no pending field call. [m, n] The current value (in the database) The new value if all the pending field call transforms are applied. • For each new field call, the predicate is tested against both m and n. 160

Escrow Locking Example Standard Field Call 161

Escrow Locking Example Standard Field Call ” 50” is not enough for T 3 Rollback a younger transaction Rollback an older transaction Note: m = n if there is no pending field call Note: We need to test the predicates against both end values because the operator could be a “ + ” (instead of “ - ”), i. e. , do not want to overstock. 162

Conventional Locking Conventional locking techniques are pessimistic: – They assume a high degree of contention among concurrent transactions. – A certain degree of checking is done before a database operation can be executed. 163

Optimistic Concurrency Control Do not perform any locking at all. A transaction is composed of three phases: If validation is successful then updates are applied to the database else the updates are discarded and the transaction is restarted Read Phase Validation Phase Updates are applied only to local copies of the data items kept in the transaction workspace. Write Phase Checking to ensure the serializability will not be violated if the updates are applied to the database. time 164

Optimistic Concurrency Control - Validation Phase • Each transaction Ti is assigned a timestamp TS(Ti) at the beginning of its validation phase • To validate Tj, one of the following conditions must Test against hold for every Ti such that TS(Ti) < TS(Tj): 1. Ti completes before Tj begins (They are isolated) all older transactions 2. Ti completes before Tj starts its Write phase, and Ti does not write any database object read by Tj Tj may read objects while Ti is still modifying objects. However, there is no conflict 3. Ti completes its Read phase before Tj completes its Read phase, and Ti does not write any database object that is either read or written by Tj Ti and Tj may write objects at the same time. However, their write sets do not overlap 165

Optimistic Concurrency Control: Problem • Advantage: No checks are made while the transaction is running locking is not required. • Disadvantage: – When interference is significant, this approach is worse than conventional locking Better ! • Conventional locking would serialize the transactions, causing them to wait for one another. • Optimistic techniques would let all the concurrent transactions run and then abort all but one of them. – Field calls plus escrow reads is better 166

Timestamp Ordering • A timestamp is a unique identifier created by the DBMS to identify a transaction. – Typically, timestamps are assigned in the order in which the transactions are submitted to the system. • Each database item X has two timestamp values: – read_TS(X): The largest of all the timestamps of transactions that have successfully read item X – write_TS(X): The largest of all the timestamps of transactions that have successfully written item X 167

Timestamp Ordering - Protocol Idea: Using the database according to the order of arrival • Transaction T issues a write_item(X): a) If read_TS(X) > TS(T) or write_TS(X) > TS(T), then abort and roll back T. Some younger transaction has done the write b) If (a) does not occur, then execute the write_item(X) and set “write_TS(X) = TS(T). ” • Transaction T issues a read_item(X): a) If write_TS(X) > TS(T), then abort and rollback T. Too late to read X Transactions can share locks b) If write_TS(X) TS(T): execute the read_item(X) and set “read_TS(X) = max (TS(T), read_TS(X))” 168

Timestamp ordering: Example Concurrent transactions T 1 T 2 T 3 TS(T 1)=200 TS(T 2)=150 TS(T 3)=175 READ B READ A READ C WRITE B WRITE A WRITE C ABORT WRITE A ABORT 169

Timestamp ordering: Example Concurrent transactions Database objects T 1 T 2 T 3 A B C TS(T 1)=200 TS(T 2)=150 TS(T 3)=175 RT=0, WT=0 READ B RT=200, WT=0 READ A RT=150, WT=0 READ C WRITE B o late ! o t s i RT T 2 TS(T 2)< WT=200, RT=200 RT=175, WT=0 RT=150, WT=200 WRITE A WRITE C TS(T 3)<WT(A) T 3 is too late ! ABORT WRITE A ABORT 170

Timestamp ordering • Deadlock does not occur. • However, cyclic restart may occur if a transaction is continually aborted and restarted (starvation). 171

Multiversion Concurrency Control (1) Each time an item is written, we create a new version of the item and give it a write-time equal to the timestamp of the transaction doing the writing. Older version Xi (WT = 10) Xj (WT = 20) TS(T) = 20 WRITE T Latest version 172

Multiversion Concurrency Control (2) When a transaction with timestamp t wishes to read an item X, it finds the version of X with the highest write-time not exceeding t and reads that version. Three versions of X Xi (WT = 10) Xj (WT = 20) Xk (WT = 30) TS(T) = 25 READ T Better than Timestam p Ordering 173

Multiversion Concurrency Control (3) When a transaction T with timestamp t wishes to write X, and we find that there is a version Xi of X with a write-time less than t and a read-time greater than t : ABORT T. Three versions of X Too late ! Xi (WT = 10) No Xj (WT = 20) Xk (WT = 30 READ TS(T 3) = 50 RT = 60) WRITE T 3 T 2 TS(T 2) = 60 174

Multiversion Concurrency Control: Example 1 T 2 A 0 TS(T 1) = 100 TS(T 2) = 200 RT = 0, WT = 0 2 READ A 3 WRITE B 4 B 0 B 1 RT = 0, WT = 0 RT = 100, WT = 0 READ A READ B A 1 RT = 200, WT = 0 WT = 200, RT = 0 RT = 100, WT = 0. 5 At step (4), B 0 is still available to T 1. We do not have to abort T 1. Note: WT(B 1) > TS(T 1) 175

Multiversion Concurrency Control: Problems • Advantage: Update-in-place makes it easy to find out the current value, but one must hunt through the log to reconstruct the history of data items. Multiversion concurrency control does not have this problem. • Disadvantages: – Read-only transactions can actually be update intensive (updating the timestamp) – Long transactions may get aborted continually. – There is nothing resembling a lock granularity scheme. – Frequently updated records (hotspots) are not helped by version-oriented database. 176