Transaction Processing Transaction Concept n A transaction is

Transaction Processing

Transaction Concept n A transaction is a unit of program execution that accesses and possibly updates various data items. n E. g. transaction to transfer $50 from account A to account B: 1. read(A) 2. A : = A – 50 3. write(A) 4. read(B) 5. B : = B + 50 6. write(B) n Two main issues to deal with: § Failures of various kinds, such as hardware failures and system crashes § Concurrent execution of multiple transactions 15. 2

Updates in SQL An example: UPDATE account SET balance = balance - 50 WHERE acct_no = A 102 What takes place: memory … Dntn: A 102: 300 Dntn: A 15: 500 … Transaction: 1. 2. 3. Read(A) A <- A -50 Write(A) Mian: A 142: 300 15. 3 Disk (1) Read (2) update (3) write account

The Threat to Data Integrity Consistent DB Name -------Joe Acct bal ------A-102 300 A-509 100 Inconsistent DB Joe’s total: 400 transaction Name -------Joe Move $50 from acct A-102 to acct A-509 bal -----250 100 Joe’s total: 350 Consistent DB Name -------Joe Acct -----A-102 A-509 What a Xaction should look like to Joe Acct bal ------A-102 250 A-509 150 What actually happens during execution Joe’s total: 400 15. 4

Transactions What? : § A unit of work § Can be executed concurrently Why? : (1) Updates can require multiple reads, writes on a db e. g. , transfer $50 from A-102 to A-509 = read(A) A A -50 write(A) read(B) B B+50 write(B) (2) For performance reasons, db’s permit updates to be executed concurrently. Concern: concurrent access/updates of data can compromise data integrity 15. 5

ACID Properties that a Xaction needs to have: n Atomicity: either all operations in a Xaction take effect, or none n Consistency: operations, taken together preserve db consistency n Isolation: intermediate, inconsistent states must be concealed from other Xactions n Durability. If a Xaction successfully completes (“commits”), changes made to db must persist, even if system crashes 15. 6

Demonstrating ACID Transaction to transfer $50 from account A to account B: 1. 2. 3. 4. 5. 6. read(A) A : = A – 50 write(A) read(B) B : = B + 50 write(B) FAILURE! Consistency: total value A+B, unchanged by Xaction Atomicity: if Xaction fails after 3 and before 6, 3 should not affect db Durability: once user notified of Xaction commit, updates to A, B should not be undone by system failure Isolation: other Xactions should not be able to see A, B between steps 3 -6 15. 7

Threats to ACID 1. Programmer Error e. g. : $50 substracted from A, $30 added to B threatens consistency 2. System Failures e. g. : crash after write(A) and before write(B) threatens atomicity e. g. : crash after write(B) threatens durability 3. Concurrency e. g. : concurrent Xaction reads A, B between steps 3 -6 threatens isolation 15. 8

Isolation Simplest way to guarantee: forbid concurrent Xactions! But, concurrency is desirable: (1) Achieves better throughput (TPS: transactions per second) one Xaction can use CPU while another is waiting for disk to service request (2) Achieves better average response time short Xactions don’t need to get stuck behind long ones Prohibiting concurrency is not an option 15. 9

Isolation n Approach to ensuring Isolation: § Distinguish between “good” and “bad” concurrency § Prevent all “bad” (and sometime some “good”) concurrency from happening OR § Recognize “bad” concurrency when it happens and undo its effects (abort some transactions) § Pessimistic vs Optimistic CC n Both pessimistic and optimistic approaches require distinguishing between good and bad concurrency How: concurrency characterized in terms of possible Xaction “schedules” 15. 10

Schedules n Schedules – sequences that indicate the chronological order in which instructions of concurrent transactions are executed § a schedule for a set of transactions must consist of all instructions of those transactions § must preserve the order in which the instructions appear in each individual transaction T 1 1 2 3 T 2 A B C D T 1 1 T 2 A B one possible schedule: 2 3 C D 15. 11

Example Schedules Transactions: T 1: transfers $50 from A to B T 2: transfers 10% of A to B T 1 read(A) A <- A -50 write(A) read(B) B<-B+50 write(B) Example 1: a “serial” schedule T 2 Constraint: The sum of A+B must be the same Before: 100+50 =150, consistent read(A) tmp <- A*0. 1 A <- A – tmp write(A) read(B) B <- B+ tmp write(B) 15. 12 After: 45+105

Example Schedule n Another “serial” schedule: T 1 T 2 read(A) tmp <- A*0. 1 A <- A – tmp write(A) read(B) B <- B+ tmp write(B) Before: 100+50 =150, consistent After: 40+110 Consistent but not the same as previous schedule. . Either is OK! read(A) A <- A -50 write(A) read(B) B<-B+50 write(B) 15. 13

Example Schedule (Cont. ) Another “good” schedule: T 1 read(A) A <- A -50 write(A) T 2 Effect: read(A) tmp <- A*0. 1 A <- A – tmp write(A) Before A 100 B 50 After 45 105 Same as one of the serial schedules Serializable! read(B) B<-B+50 write(B) read(B) B <- B+ tmp write(B) 15. 14

Example Schedules (Cont. ) A “bad” schedule T 1 read(A) A <- A -50 T 2 Before: 100+50 = 150 read(A) tmp <- A*0. 1 A <- A – tmp write(A) read(B) After: 50+60 = 110 !! Not consistent write(A) read(B) B<-B+50 write(B) B <- B+ tmp write(B) 15. 15

Serializability How to distinguish good and bad schedules? for previous example, any schedule leaving A+B = 150 is good Q: could we express good schedules in terms of integrity constraints? Ans: No. In general, won’t know A+B, can’t check value of A+B at given time for consistency Alternative: Serializability 15. 16

Serializability Serializable: A schedule is serializable if its effects on the db are equivalent to some serial schedule. Hard to ensure; more conservative approaches are used in practice All schedules SQL serializable Serializable schedules “view serializable” schedules “conflict serializable” schedules 15. 17

Conflict Serializability Conservative approximation of serializability (conflict serializable but doesn’t hold) Idea: we can swap the execution order of consecutive nonconflicting operations w. o. affecting state of db Execute Xactions so as to leave a serial schedule? 15. 18

What operations can be swapped? A. Reads and writes of different data elements e. g. : T 1 T 2 write(A) T 1 read(B) T 2 = read(B) write(A) OK because: value of B unaffected by write of A ( read(B) has same effect ) write of A is not undone by read of B ( write(A) has same effect) Note : T 1 T 2 write(A) T 1 read(A) T 2 = read(A) write(A) Why? In the first, T 1 reads value of A written by T 2. May be different value than previous value of A 15. 19

Conflict Serializability (Cont. ) n If a schedule S can be transformed into a schedule S´ by a series of swaps of non-conflicting instructions, we say that S and S´ are conflict equivalent. n We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule n Ex: T 1 …. read(A) T 2 …. read(A). . . T 1 …. can be rewritten to equivalent schedule T 2 …. read(A). . . 15. 21

Conflict Serializability (Cont. ) Example: T 1 1. Read(A) 2. A A -50 3. Write(A) 4. Read(B) 5. B B + 50 6. Write(B) T 2 Swaps: a. Read(A) b. tmp A * 0. 1 c. A A - tmp d. Write(A) 4 <->d 4<->c 4<->b 4<->a 5<->d 5<->c 5<->b 5<->a 6<->d 6<->c 6<->b 6<->a Conflict serializble T 1, T 2 e. Read(B) f. B B + tmp g. Write(B) 15. 22

Conflict Serializability (Cont. ) The effects of swaps T 1 read(A) A <- A -50 write(A) read(B) B<-B+50 write(B) T 2 Because example schedule could be swapped to this schedule (<T 1, T 2>) example schedule is conflict serializable read(A) tmp <- A*0. 1 A <- A – tmp write(A) read(B) B <- B+ tmp write(B) 15. 23

The Swaps We Made A. Reads and writes of different data elements 4 <-> d 6 <-> a B. Reads of different data elements: 4 <-> a C. Writes of different data elements: 6 <-> d D. Any operation with a local operation OK because local operations don’t go to disk. Therefore, unaffected by other operations: 4 <-> b 5 <-> a. . 4 <-> c To simplify, local operations are omitted from schedules 15. 24

Conflict Serializability (Cont. ) Previous example w. o. local operations: T 1 1. Read(A) 2. Write(A) T 2 Swaps: a. Read(A) b. Write(A) 3 <->b 3<->a 4<->b 4<->a 3. Read(B) 4. Write(B) c. Read(B) d. Write(B) 15. 25 T 1, T 2

Swappable Operations n Swappable operations: 1. Any operation on different data elements 2. Reads of the same data (Read(A)) (regardless of order of reads, the same value for A is read) n Conflicts: T 2: Read(A) T 2: Write(A) T 1: Read (A) OK R/W Conflict T 1: Write (A) W/R Conflict W/W Conflict 15. 26

Conflicts (1) READ/WRITE conflicts: conflict because value read depends on whether write has occured (2) WRITE/WRITE conflicts: conflict because value left in db depends on which write occurred last (3) READ/READ : no conflict 15. 27

Conflict Serializability Q: Is the following schedule conflict serializable? If so, what’s its equivalent serial schedule? If not, why? T 1 T 2 (1) read(Q) write(Q) (a) (2) write(Q) Ans: No. Swapping (a) with (1) is a R/W conflict, and swapping (a) with (2) is a W/W conflict. Not equivalent to <T 1, T 2> or <T 2, T 1> 15. 28

Conflict Serializability Q: Is the following schedule conflict serializable? If so, what’s its equivalent serial schedule? If not, why? T 1 T 2 T 3 Ans. : NO. All possible serial schedules are not conflict equivalent. (1) Read(A) (a) Write(A) (b) Read(B) <T 1, T 2, T 3> <T 1, T 3, T 2> <T 2, T 1, T 3>. . . (x) Write(B) (y) Read(S) (2) Write(S) 15. 29

Conflict Serializability Testing: too expensive to test a schedule by swapping operations (usually schedules are big!) Alternative: “Precedence Graphs” * vertices = Xactions * edges = conflicts between Xactions E. g. : Ti Tj if: (1) Ti, Tj have a conflicting operation, and (2) Ti executed its conflicting operation first 15. 30

Precedence Graph An example of a “Precedence Graph”: T 1 T 2 T 3 Read(A) T 1 R/W Write(A) Read(B) (A) B) T 2 ( /W R Write(B) Read(S) T 3 Q: When is a schedule not conflict serializable? 15. 31

Precedence Graph Another example: T 1 T 2 T 3 Read(A) T 1 (S) R/W Write(A) Read(B) Write(B) Read(S) Write(S) R/W (A) B) T 2 ( /W R T 3 Not conflict serializable!! Because there is a cycle in the PG, the cycle creates contradiction 15. 32

Example Schedule (Schedule A) T 1 T 2 T 3 T 4 T 5 read(X) read(Y) read(Z) read(V) read(W) read(Y) write(Z) read(U) read(Y) write(Y) read(Z) write(Z) read(U) write(U) 15. 33

Precedence Graph for Schedule A R/W (Y) T 1 R/ T 2 W R/W(Z) (y ), R/ W (Z R/W(Y) ) T 3 R/W(Z) , W/W(Z) T 5 15. 34 T 4

Test for Conflict Serializability n A schedule is conflict serializable if and only if its precedence graph is acyclic. n Cycle-detection algorithms exist which take order n 2 time, where n is the number of vertices in the graph. (Better algorithms take order n + e where e is the number of edges. ) n If precedence graph is acyclic, the serializability order can be obtained by a topological sorting of the graph. For example, a serializability order for Schedule A would be T 5 T 1 T 3 T 2 T 4. 15. 35

View Serializability n “View Equivalence”: S and S´ are view equivalent if the following three conditions are met: 1. For each data item Q, if transaction Ti reads the initial value of Q in schedule S, then transaction Ti must, in schedule S´, also read the initial value of Q. 2. For each data item Q, if transaction Ti reads the value of Q written by Tj in S, it also does in S’ 3. For each data item Q, the transaction (if any) that performs the final write(Q) operation in schedule S must perform the final write(Q) operation in schedule S´. As can be seen, view equivalence is also based purely on reads and writes alone. 15. 36

View Serializability (Cont. ) n A schedule S is view serializable if it is view equivalent to a serial schedule. n Example: T 1 T 3 T 2 Read(A) Write(A) Is this schedule view serializable? conflict serializable? Write(A) VS: Yes. Equivalent to <T 1, T 2, T 3> CS: No. PG has a cycle. Every view serializable schedule that is not conflict serializable has blind writes. 15. 37

Other Notions of Serializability Equivalent to the serial schedule < T 1, T 2 >, yet is not conflict equivalent or view equivalent to it. T 1 T 2 Read(A) A A -50 Write(A) Determining such equivalence requires analysis of operations Read(B) B B - 10 Write(B) other than read and write. Read(B) B B + 50 Write(B) Addition and subtraction are commutative. Read(A) A A + 10 Write(A) 15. 38

Recoverable Schedules Need to address the effect of transaction failures on concurrently running transactions. n Recoverable schedule — if a transaction Tj reads a data item previously written by a transaction Ti , then the commit operation of Ti appears before the commit operation of Tj. n The following schedule is not recoverable if T 9 commits immediately after the read n If T 8 should abort, T 9 would have read (and possibly shown to the user) an inconsistent database state. Hence, database must ensure that schedules are recoverable. 15. 39

Cascading Rollbacks n Cascading rollback – a single transaction failure leads to a series of transaction rollbacks. Consider the following schedule where none of the transactions has yet committed (so the schedule is recoverable) If T 10 fails, T 11 and T 12 must also be rolled back. n Can lead to the undoing of a significant amount of work 15. 40

Cascadeless Schedules n Cascadeless schedules — cascading rollbacks cannot occur if for each pair of transactions Ti and Tj such that Tj reads a data item previously written by Ti, the commit operation of Ti appears before the read operation of Tj. n Every cascadeless schedule is also recoverable n It is desirable to restrict the schedules to those that are cascadeless 15. 41

Concurrency Control n A database must provide a mechanism that will ensure that all possible schedules are § either conflict or view serializable, and § are recoverable and preferably cascadeless n A policy in which only one transaction can execute at a time generates serial schedules, but provides a poor degree of concurrency § Are serial schedules recoverable/cascadeless? n Testing a schedule for serializability after it has executed is a little too late! n Goal – to develop concurrency control protocols that will assure serializability. 15. 42

Concurrency Control vs. Serializability Tests n Concurrency-control protocols allow concurrent schedules, but ensure that the schedules are conflict/view serializable, and are recoverable and cascadeless. n Concurrency control protocols generally do not examine the precedence graph as it is being created § Instead a protocol imposes a discipline that avoids nonseralizable schedules. § We study such protocols next. n Different concurrency control protocols provide different tradeoffs between the amount of concurrency they allow and the amount of overhead that they incur. n Tests for serializability help us understand why a concurrency control protocol is correct. 15. 43

Weak Levels of Consistency n Some applications are willing to live with weak levels of consistency, allowing schedules that are not serializable § E. g. a read-only transaction that wants to get an approximate total balance of all accounts § E. g. database statistics computed for query optimization can be approximate. § Such transactions need not be serializable with respect to other transactions n Tradeoff accuracy for performance 15. 44

Concurrency Control n Concurrency Control § Ensures interleaving of operations amongst concurrent transactions result in serializable schedules n How? § transaction operations interleaved following a protocol 15. 47

How to enforce serializable schedules? Prevent P(S) cycles from occurring using a concurrency control manager: ensures interleaving of operations amongst concurrent transactions only result in serializable schedules. T 1 T 2 …. . Tn CC Scheduler DB 15. 48

Concurrency Via Locks n Idea: § Data items modified by one transaction at a time n Locks § Control access to a resource § Can block a transaction until lock granted § Two modes: § Shared (read only) § e. Xclusive (read & write) 15. 49

Granting Locks n Requesting locks § Must request before accessing a data item n Granting Locks § No lock on data item? Grant § Existing lock on data item? § Check compatibility: § Compatible? Grant § Not? Block transaction 15. 50 shared exclusive shared Yes No exclusive No No

Lock instructions n New instructions - lock-S: shared lock request - lock-X: exclusive lock request - unlock: release previously held lock Example: T 1 lock-X(B) read(B) B B-50 write(B) unlock(B) lock-X(A) read(A) A A + 50 write(A) unlock(A) T 2 lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B) 15. 51

Locking Issues n Starvation § T 1 holds shared lock on Q § T 2 requests exclusive lock on Q: blocks § T 3, T 4, . . . , Tn request shared locks: granted § T 2 is starved! n Solution? Do not grant locks if older transaction is waiting 15. 52

Locking Issues n No transaction proceeds: T 1 Deadlock T 2 lock-X(B) - T 1 waits for T 2 to unlock A read(B) - T 2 waits for T 1 to unlock B B B-50 write(B) lock-S(A) read(A) Rollback transactions Can be costly. . . lock-S(B) lock-X(A) 15. 53

Locking Issues n Locks do not ensure serializability by themselves: T 1 lock-X(B) read(B) B B-50 write(B) unlock(B) lock-X(A) read(A) A A + 50 write(A) unlock(A) T 2 lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlock(B) display(A+B) 15. 54 T 2 displays 50 less!!

The Two-Phase Locking Protocol n This is a protocol which ensures conflict-serializable schedules. n Phase 1: Growing Phase § transaction may obtain locks § transaction may not release locks n Phase 2: Shrinking Phase § transaction may release locks § transaction may not obtain locks n The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points (i. e. the point where a transaction acquired its final lock). Locks can be either X, or S/X. 15. 55

2 PL n Example: T 1 in 2 PL T 1 lock-X(B) read(B) B B - 50 Growing phase write(B) lock-X(A) read(A) A A - 50 write(A) Shrinking phase unlock(B) unlock(A) 15. 56

2 PL & Serializability n Recall: Precedence Graph T 1 T 2 T 3 read(Q) write(Q) read(R) write(R) read(S) T 1 R/W(Q) T 2 ) T 3 (R W R/ 15. 57

2 PL & Serializability n Recall: Precedence Graph T 1 T 2 T 3 read(Q) write(Q) read(R) write(R) read(S) write(S) R/W(Q) T 1 Cycle Non-serializable ) (S W R/ T 2 T 3 ) (R W R/ 15. 58

2 PL & Serializability Relation between Growing & Shrinking phase: T 1 G < T 1 S T 1 T 2 G < T 2 S T 2 T 3 G < T 3 S T 1 must release locks for other to proceed T 3 T 1 S < T 2 G T 2 S < T 3 G T 3 S < T 1 G < T 1 S < T 2 G < T 2 S < T 3 G < T 3 S < T 1 G Not Possible under 2 PL! It can be generalized for any set of transactions. . . 15. 59

2 PL Issues n As observed earlier, T 1 2 PL does not prevent deadlock T 2 lock-X(B) read(B) n > 2 transactions involved? B B-50 - Rollbacks expensive. write(B) lock-S(A) n We will revisit later. read(A) lock-S(B) lock-X(A) 15. 60

2 PL Variants Strict two phase locking § Exclusive locks must be held until transaction commits § Ensures data written by transaction can’t be read by others § Prevents cascading rollbacks 15. 61

Strict 2 PL T 1 T 2 T 3 lock-X(A) read(A) lock-S(B) read(B) write(A) unlock(A) lock-X(A) read(A) write(A) unlock(A) Strict 2 PL will not allow that lock-S(A) read(A) <xaction fails> 15. 62

Strict 2 PL & Cascading Rollbacks n Ensures any data written by uncommited transaction not read by another n Strict 2 PL would prevent T 2 and T 3 from reading A § T 2 & T 3 wouldn’t rollback if T 1 does 15. 63

Deadlock Handling n Consider the following two transactions: T 1 : write (X) T 2 : write(Y) write(X) n Schedule with deadlock 15. 64

Deadlock Handling n System is deadlocked if there is a set of transactions such that every transaction in the set is waiting for another transaction in the set. n Deadlock prevention protocols ensure that the system will never enter into a deadlock state. Some prevention strategies : § Require that each transaction locks all its data items before it begins execution (predeclaration). § Impose partial ordering of all data items and require that a transaction can lock data items only in the order specified by the partial order (graph-based protocol). 15. 65

Dealing with Deadlocks n How do you detect a deadlock? § Wait-for graph § Directed edge from Ti to Tj T 2 § If Ti waiting for Tj T 4 T 1 T 2 T 3 T 4 T 3 X(Z) X(V) X(W) Suppose T 4 requests lock-S(Z). . S(V) S(W) S(V) 15. 68

Detecting Deadlocks n Wait-for graph has a cycle deadlock T 2, T 3, T 4 are deadlocked T 4 T 1 • Build wait-for graph, check for cycle T 3 • How often? - Tunable IF expect many deadlocks or many transactions involved run often to reduce aborts ELSE run less often to reduce overhead 15. 69

Recovering from Deadlocks n Rollback one or more transaction § Which one? § Rollback the cheapest ones § Cheapest ill-defined § Was it almost done? § How much will it have to redo? § Will it cause other rollbacks? § How far? § May only need a partial rollback § Avoid starvation § Ensure same xction not always chosen to break deadlock 15. 70

Timestamp-Based Protocols n Idea: ¬ Decide in advance ordering of transactions. ¬ Ensure concurrent schedule serializes to that serial order. q Timestamps 1. TS(Ti) is time Ti entered the system 2. Data item timestamps: 1. W-TS(Q): Largest timestamp of any xction that wrote Q 2. R-TS(Q): Largest timestamp of any xction that read Q q Timestamps -> serializability order 15. 71

Timestamp CC Idea: If action pi of Xact Ti conflicts with action qj of Xact Tj, and TS(Ti) < TS(Tj), then pi must occur before qj. Otherwise, restart violating Xact. 15. 72

When Xact T wants to read Object O n If TS(T) < W-TS(O), this violates timestamp order of T w. r. t. writer of O. § So, abort T and restart it with a new, larger TS. (If restarted with same TS, T will fail again!) n If TS(T) > W-TS(O): § Allow T to read O. § Reset R-TS(O) to max(R-TS(O), TS(T)) n Change to R-TS(O) on reads must be written to disk! This and restarts represent overhead. U writes O T reads O T start U start 15. 73

When Xact T wants to Write Object O n If TS(T) < R-TS(O), then the value of O that T is producing was needed previously, and the system assumed that value would never be produced. write rejected, T is rolled back. n If TS(T) < W-TS(O), then T is attempting to write an obsolete value of O. Hence, this write operation is rejected, and T is rolled back. n Otherwise, the write operation is executed, and W-TS(O) is set to TS(T). U reads O T writes O T start U start 15. 74

Timestamp-Ordering Protocol n Rollbacks still present § On rollback, new timestamp & restart T 1 rollback since TS(T 1) < W-TS(O)=TS(T 2) T 1 T 2 Read(O) Write(O) Can reduce one rollback situation When transaction writes an obsolete value, ignore it: Thomas’ write-rule does not rollback T 1 15. 75

Example Use of the Protocol A partial schedule for several data items for transactions with initial timestamps 1, 2, 3, 4, 5 T 1 read(Y) read(Q) T 2 T 3 T 4 read(Y) write(Z) T 5 write(X) T 6 read(Z) read(X) abort read(Y) write(Z) abort write(Y) write(Z) 15. 76 read(X) T 7

Correctness of Timestamp-Ordering Protocol n The timestamp-ordering protocol guarantees serializability since all the arcs in the precedence graph are of the form: transaction with smaller timestamp transaction with larger timestamp Thus, there will be no cycles in the precedence graph n Timestamp protocol ensures freedom from deadlock as no transaction ever waits. 15. 77

Multiversion Schemes n Multiversion schemes keep old versions of data item to increase concurrency. § Multiversion Timestamp Ordering § Multiversion Two-Phase Locking n Each successful write results in the creation of a new version of the data item written. n Use timestamps to label versions. n When a read(Q) operation is issued, select an appropriate version of Q based on the timestamp of the transaction, and return the value of the selected version. n reads never have to wait as an appropriate version is returned immediately. 15. 78

Multiversion Timestamp Ordering n Each data item Q has a sequence of versions <Q 1, Q 2, . . , Qm>. Each version Qk contains three data fields: § Content -- the value of version Qk. § W-timestamp(Qk) -- timestamp of the transaction that created (wrote) version Qk § R-timestamp(Qk) -- largest timestamp of a transaction that successfully read version Qk n when a transaction Ti creates a new version Qk of Q, Qk's W- timestamp and R-timestamp are initialized to TS(Ti). n R-timestamp of Qk is updated whenever a transaction Tj reads Qk, and TS(Tj) > R-timestamp(Qk). 15. 79

Multiversion Timestamp Ordering (Cont) Suppose that transaction Ti issues a read(Q) or write(Q) operation. Let Qk denote the version of Q whose write timestamp is the largest write timestamp less than or equal to TS(Ti). § If transaction Ti issues a read(Q), then the value returned is the content of version Qk. § If transaction Ti issues a write(Q) § if TS(Ti) < R-timestamp(Qk), then transaction Ti is rolled back. § if TS(Ti) = W-timestamp(Qk), the contents of Qk are overwritten § else a new version of Q is created. Ti (write Q) X X Qk-1 X Qk TTi i(read (write. Q)Q) X 15. 80 TS(Ti) < R-timestamp(Qk) TS(Ti) > R-timestamp(Qk)

Multiversion Timestamp Ordering (Cont). n Observe that § Reads always succeed § A write by Ti is rejected if some other transaction Tj that (in the serialization order defined by the timestamp values) should read Ti's write, has already read a version created by a transaction older than Ti. n Protocol guarantees serializability 15. 81

MVCC: Implementation Issues n Creation of multiple versions increases storage overhead § Extra tuples § Extra space in each tuple for storing version information n Versions can, however, be garbage collected § E. g. if Q has two versions Q 5 and Q 9, and the oldest active transaction has timestamp > 9, than Q 5 will never be required again 15. 82

Multiversion Two-Phase Locking n Differentiates between read-only transactions and update transactions n Update transactions acquire read and write locks, and hold all locks up to the end of the transaction. That is, update transactions follow rigorous two-phase locking. § Each successful write results in the creation of a new version of the data item written. § each version of a data item has a single timestamp whose value is obtained from a counter ts-counter that is incremented during commit processing. n Read-only transactions are assigned a timestamp by reading the current value of ts-counter before they start execution; they follow the multiversion timestamp-ordering protocol for performing reads. 15. 83

Multiversion Two-Phase Locking (Cont. ) n When an update transaction wants to read a data item: § it obtains a shared lock on it, and reads the latest version. n When it wants to write an item § it obtains X lock on; it then creates a new version of the item and sets this version's timestamp to . n When update transaction Ti completes, commit processing occurs: § Ti sets timestamp on the versions it has created to ts-counter + 1 § Ti increments ts-counter by 1 n Read-only transactions that start after Ti increments ts-counter will see the values updated by Ti. n Read-only transactions that start before Ti increments the ts-counter will see the value before the updates by Ti. n Only serializable schedules are produced. 15. 84

Snapshot Isolation n Motivation: Decision support queries that read large amounts of data will conflict with OLTP transactions that update a few rows § Poor performance results n Every transaction appears to be given its own snapshot of the database when it begins. § All reads are local to this snapshot § Updates are kept local until commit time. § Thus, a transaction is isolated from updates of other transactions. § Subsequent update attempts by other transactions need not wait. 15. 85

Snapshot Isolation (commit) n Must test to see if any transaction that was concurrent with T has already written an update to the DB. n Commit processing for T (First Committer Wins): § Test if any transaction that was concurrent with T has already written an update for some data item that T intends to write. § If found, ABORT T. § Otherwise, T commits and its updates are written to DB. n Lost update problem: § Suppose T and T’ execute concurrently. § When done, either T should see all updates from T’ or T’ should see all the updates from T. (serial execution) § If T reads some data item that T’ updates and T’ reads some data that T updates then both transactions will fail to read the update made by the other. 15. 86

Snapshot Isolation T 1 T 2 T 3 W(Y : = 1) Commit Start R(X) 0 R(Y) 1 W(Y: =2) W(X: =2) W(Z: =3) Concurrent updates not visible Own updates are visible Not first-committer of X Serialization error, T 2 is rolled back Commit R(Z) 0 R(Y) 2 W(X: =3) Commit-Req Abort 15. 87

Skew Write n TI : r(A); r(B): w(B) n Tj : r(A); r(B); w(A) n both transactions will be allowed to commit. n Consider the precedence graph: Ti Ti reads A before Tj writes A Tj Tj reads B before Ti writes B Non-serializable! Happens when a pair of transaction read data that is written by the other, but there is no data that is written by both. 15. 88

Example n Constraint: Checking + Savings can never go negative. n Start: checking : = $200 and savings : = $100 n T 1: checking : = checking - $200 n T 2: savings : = savings - $200 n Both transactions can commit because they do not update the same data items. n But, constraint is violated! (checking + savings = -$100) n => test integrity constraints at commit. 15. 89

Concurrency in Index Structures n Indices are unlike other database items in that their only job is to help in accessing data. n Index-structures are typically accessed very often, much more than other database items. § Treating index-structures like other database items, e. g. by 2 -phase locking of index nodes can lead to low concurrency. § It is acceptable to have non-serializable concurrent access to an index as long as the accuracy of the index is maintained. § In particular, the exact values read in an internal node of a B+-tree are irrelevant so long as we end up in the correct leaf node. n There are several index concurrency protocols where locks on internal nodes are released early, and not in a two-phase fashion. 15. 90

Concurrency in Index Structures (Cont. ) n Example of index concurrency protocol: n Use crabbing instead of two-phase locking on the nodes of the B+-tree, as follows. During search/insertion/deletion: § First lock the root node in shared mode. § After locking all required children of a node in shared mode, release the lock on the parent. § During insertion/deletion, upgrade leaf node locks to exclusive mode. § When splitting or coalescing requires changes to a parent, lock the parent in exclusive mode. n Above protocol can cause excessive deadlocks § Searches coming down the tree deadlock with updates going up the tree § Can abort and restart search, without affecting transaction n Better protocols are available; e. g. , the B-link tree protocol 15. 91

High-Performance Transaction Systems n High-performance hardware and parallelism help improve the rate of transaction processing, but are insufficient to obtain high performance: § Disk I/O is a bottleneck — I/O time (10 milliseconds) has no decreased at a rate comparable to the increase in processor speeds. § Parallel transactions may attempt to read or write the same data item, resulting in data conflicts that reduce effective parallelism n We can reduce the degree to which a database system is disk bound by increasing the size of the database buffer. 15. 92

Main-Memory Database n Commercial 64 -bit systems can support main memories of hundreds of gigabytes. n Memory resident data allows faster processing of transactions. n Disk-related limitations: § Logging is a bottleneck when transaction rate is high. § Use group-commit to reduce number of output operations (Will study two slides ahead. ) § If the update rate for modified buffer blocks is high, the disk data-transfer rate could become a bottleneck. § If the system crashes, all of main memory is lost. 15. 93

Main-Memory Database Optimizations n To reduce space overheads, main-memory databases can use structures with pointers crossing multiple pages. In disk databases, the I/O cost to traverse multiple pages would be excessively high. n No need to pin buffer pages in memory before data are accessed, since buffer pages will never be replaced. n Design query-processing techniques to minimize space overhead - avoid exceeding main memory limits during query evaluation. n Improve implementation of operations such as locking and latching, so they do not become bottlenecks. n Optimize recovery algorithms, since pages rarely need to be written out to make space for other pages. 15. 94

Group Commit n Idea: Instead of performing output of log records to stable storage as soon as a transaction is ready to commit, wait until § log buffer block is full, or § a transaction has been waiting sufficiently long after being ready to commit n Results in fewer output operations per committed transaction, and correspondingly a higher throughput. n However, commits are delayed until a sufficiently large group of transactions are ready to commit, or a transaction has been waiting long enough-leads to slightly increased response time. n Above delay acceptable in high-performance transaction systems since log buffer blocks will fill up quickly. 15. 95

Real-Time Transaction Systems n In systems with real-time constraints, correctness of execution involves both database consistency and the satisfaction of deadlines. § Hard deadline – Serious problems may occur if task is not completed within deadline § Firm deadline - The task has zero value if it completed after the deadline. § Soft deadline - The task has diminishing value if it is completed after the deadline. n The wide variance of execution times for read and write operations on disks complicates the transaction management problem for time-constrained systems § main-memory databases are thus often used § Waits for locks, transaction aborts, contention for resources remain as problems even if data is in main memory n Design of a real-time system involves ensuring that enough processing power exists to meet deadline without requiring excessive hardware resources. 15. 96