Database Management Isolation Systems Serial execution: Since - PowerPoint PPT Presentation

Lecture 7 Database Management Isolation Systems • Serial execution: – Since each transaction is consistent and isolated from all Winter 2004 others, schedule is guaranteed to be correct for all applications CMPUT 391: Implementing Isolation – Inadequate performance • Since system has multiple asynchronous resources and transaction uses only one at a time Dr. Osmar R. Zaïane • Concurrent execution: – Improved performance (multiprogramming) – Some interleavings produce incorrect result – We are interested in concurrent schedules that are Chapter 23 University of Alberta equivalent to serial schedules. These are referred to as of Textbook serializable schedules. Based on slides by Lewis, Bernstein and Kifer. 1 2 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Transaction Schedule T1: begin_transaction(); Schedule …. Transaction schedule p 1,1 ; p 1,3 p 1,2 p 1,1 …. Arriving schedule p 1,2; Schedule in which To db (merge of transaction …. requests are serviced server T1 schedules) p 1,3 ; local Concurrency commit(); variables T2 To database Control • Consistent - performs correctly when executed in T3 isolation starting in a consistent database state transaction Database server – Preserves database consistency schedules – Moves database to a new state that corresponds to new real-world state Dr. Osmar Zaïane, 2004 University of Alberta 3 Dr. Osmar Zaïane, 2004 University of Alberta 4 CMPUT 391 – Database Management Systems CMPUT 391 – Database Management Systems

Schedule Concurrency Control • Transforms arriving schedule into a correct • Representation 1: interleaved schedule to be submitted to the T 1 : p 1 p 2 p 3 p 4 DBMS T 2 : p 1 p 2 – Delays servicing a request (reordering) - causes time → a transaction to wait – Refuses to service a request - causes transaction • Representation 2: to abort p 1,1 p 1,2 p 2,1 p 1,3 p 2,2 p 1,4 • Actions taken by concurrency control have time → performance costs – Goal is to avoid delaying servicing a request 5 6 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta The Inconsistent Analysis Problem Correct Schedules • Occurs when a transaction reads several values from a database while a second transaction • Interleaved schedules equivalent to serial updates some of them. schedules are the only ones guaranteed to be correct for all applications T1 T2 A B C sum sum=0 $100 $50 $25 0 • Equivalence based on commutativity of operations R(A) R(A) $100 $50 $25 0 • Definition: Database operations p 1 and p 2 $100 $50 $25 100 sum=sum+A A=A-10 commute if, for all initial database states, they $90 $50 $25 100 R(B) W(A) $90 $50 $25 150 sum=sum+B R(C) return the same results and leave the database in $90 $50 $25 150 C=C+10 the same final state when executed in either order. $90 $50 $35 150 W(C) Should be $90 $50 $35 150 R(C) 175 sum=sum+C $90 $50 $35 185 Dr. Osmar Zaïane, 2004 University of Alberta 7 Dr. Osmar Zaïane, 2004 University of Alberta 8 CMPUT 391 – Database Management Systems CMPUT 391 – Database Management Systems

Commutativity of Read and Commutativity of Conventional Write Operations Operations • Read • p 1 commutes with p 2 if – r(x, X) - copy the value of database variable x to – They operate on different data items local variable X • w 1 (x) commutes with w 2 (y) and r 2 (y) – Both are reads • Write • r 1 (x) commutes with r 2 (x) – w(x, X) - copy the value of local variable X to • Operations that do not commute conflict database variable x • w 1 (x) conflicts with w 2 (x) • We use r 1 (x) and w 1 (x) to mean a read or Read(x) Write(x) • w 1 (x) conflicts with r 2 (x) write of x by transaction T 1 Read(x) No Yes Write(x) Yes Yes 9 10 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Example of Equivalence conflict Equivalence of Schedules S 1 : r 1 (x) r 2 (x) w 2 (x) r 1 (y) w 1 (y) S 2 : r 1 (x) r 2 (x) r 1 (y) w 2 (x) w 1 (y) • An interchange of adjacent operations of different transactions in a schedule creates an S 3 : r 1 (x) r 1 (y) r 2 (x) w 2 (x) w 1 (y) equivalent schedule if the operations commute S 1 : S 1,1 , p i,j , p k,l , S 1,2 where i ≠ k S 4 : r 1 (x) r 1 (y) r 2 (x) w 1 (y) w 2 (x) S 2 : S 1,1 , p k,l , p i,j , S 1,2 S 5 : r 1 (x) r 1 (y) w 1 (y) r 2 (x) w 2 (x) • Equivalence is transitive: If S 1 is equivalent to conflicting operations S 1 is equivalent to S 5 S 2 (by a series of such interchanges), and S 2 is ordered in same way S 5 is the serial schedule T 1 , T 2 equivalent to S 3 , then S 1 is equivalent to S 3 S 1 is serializable S 1 is not equivalent to the serial schedule T 2 , T 1 Dr. Osmar Zaïane, 2004 University of Alberta 11 Dr. Osmar Zaïane, 2004 University of Alberta 12 CMPUT 391 – Database Management Systems CMPUT 391 – Database Management Systems

Example of Equivalence Serializable Schedules T 1 : begin transaction T 2 : begin transaction read (x, X); read (x,Y); X = X+4; write (y,Y); • S is serializable if it is equivalent to a serial write (x, X); commit; schedule commit; • Transactions are totally isolated in a serializable r 1 (x) r 2 (x) w 2 (y) w 1 (x) x=1, y=3 x=5, y=1 Interchange schedule x=5, y=1 commuting operations r 2 (x) w 2 (y) r 1 (x) w 1 (x) • A schedule is correct for any application if it is a T 2 T 1 serializable schedule of consistent transactions r 1 (x) r 2 (x) w 2 (y) w 1 (x) • The schedule : x=1, y=3 x=5, y=1 Interchange r 1 (x) r 2 (y) w 2 (x) w 1 (y) x=5, y=5 conflicting operations is not serializable r 1 (x) w 1 (x) r 2 (x) w 2 (y) T 1 T 2 13 14 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Isolation Levels Serializable • Serializability provides a conservative definition of • Theorem - Schedule S 1 can be derived from S 2 correctness by a sequence of commutative interchanges if – For a particular application there might be many and only if conflicting operations in S 1 and S 2 acceptable non -serializable schedules are ordered in the same way – Requiring serializability might degrade performance If: A sequence of commutative interchanges can be • DBMSs offer a variety of isolation levels: determined that takes S 1 to S 2 since conflicting – SERIALIZABLE is the most stringent operations do not have to be reordered – Lower levels of isolation give better performance Only if: Commutative interchanges do not reorder • Might allow incorrect schedules conflicting operations • Might be adequate for some applications Dr. Osmar Zaïane, 2004 University of Alberta 15 Dr. Osmar Zaïane, 2004 University of Alberta 16 CMPUT 391 – Database Management Systems CMPUT 391 – Database Management Systems

Conflict Equivalence Conflict Equivalence • Result - A schedule is serializable if it is conflict equivalent to a serial schedule • Definition - Two schedules, S 1 and S 2 , of the r 1 (x) w 2 (x) w 1 (y) r 2 (y) → r 1 (x) w 1 (y) w 2 (x) r 2 (y) same set of operations are conflict equivalent if conflicting operations are ordered in the same conflict conflict way in both • If in S transactions T 1 and T 2 have several pairs of – Or (using theorem) if one can be obtained from the conflicting operations (p 1,1 conflicts with p 2,1 and other by a series of commutative interchanges p 1,2 conflicts with p 2,2 ) then p 1,1 must precede p 2,1 and p 1,2 must precede p 2,2 (or vice versa) in order for S to be serializable. 17 18 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Conflict Equivalence and Serialization Graph of a Serializability Schedule, S • Nodes represent transactions • Serializability is a conservative notion of correctness and conflict equivalence • There is a directed edge from node T i to node provides a conservative technique for T j if T i has an operation p i,k that conflicts with determining serializability an operation p j,r of T j and p i,k precedes p j,r in S • However, a concurrency control that • Theorem - A schedule is conflict serializable guarantees conflict equivalence to serial if and only if its serialization graph has no schedules ensures correctness and is easily cycles implemented Dr. Osmar Zaïane, 2004 University of Alberta 19 Dr. Osmar Zaïane, 2004 University of Alberta 20 CMPUT 391 – Database Management Systems CMPUT 391 – Database Management Systems