Transaction Models and Concurrency Control 5DV120 Database System - PowerPoint PPT Presentation

Transaction Models and Concurrency Control 5DV120 — Database System Principles Ume˚ a University Department of Computing Science Stephen J. Hegner hegner@cs.umu.se http://www.cs.umu.se/~hegner Transaction Models and Concurrency Control 20130903 Slide 1 of 100

The Issue of Concurrency in the DBMS Context • It is often the case that a database system will be accessed by many users simultaneously. • If this access is read-only, then there are no serious integrity problems; only ones of performance. • If the access includes writing the database, then serious problems will arise if the interaction is not regulated. • It is therefore necessary to characterize correct behavior in the context of concurrent transactions. Transaction Models and Concurrency Control 20130903 Slide 2 of 100

The ACID Characterization • The properties which a set of concurrent transactions should exhibit is often expressed via the acronym ACID : Atomicity: For each transaction, either the complete result of its execution is recorded in the database, or else nothing about its results is recorded. Consistency: The execution of any transaction in isolation preserves the integrity of the database. Isolation: The execution of one running transaction must not affect the execution of another concurrently running transaction. Durability: The results of the transactions are permanent in the database. • These slides will focus primarily upon isolation . • A subsequent set of slides will focus upon atomicity and durability . • Consistency is a property of a single transaction and will not be the focus here. Transaction Models and Concurrency Control 20130903 Slide 3 of 100

Example Transactions Example (simplified bank transactions) : Two transactions T 1 and T 2 . • R i and W i are local variables for transaction i with i ∈ { 1 , 2 } . • There are the following operations: R Bal i � a � means that transaction T i reads the balance of account a into a local variable R i : R i ← − Bal � a � . W Bal i � a � means that transaction T i writes the balance of account a from variable W i to the database: Bal � a � ← − W i . Cpd Bal i � X � is a local operation that adds X % interest to R i and places the result in W i : W i ← − R i × (1 + X / 100). Wthd i � X � means that X Euros are subtracted from the local value R i and placed in W i : W i ← − R i − X . T 1 Compound 10% on account 15: R Bal 1 � 15 � ; Cpd Bal 1 � 10 � ; W Bal 1 � 15 � . T 2 Withdraw 2000 from account 15: R Bal 2 � 15 � ; Wthd 2 � 2000 � ; W Bal 2 � 15 � . Transaction Models and Concurrency Control 20130903 Slide 4 of 100

Order of Execution • Shown below are two possibilities for schedules for these transactions. Bal � 15 � Bal � 15 � T 1 T 2 T 1 T 2 R Bal 1 � 15 � R Bal 2 � 15 � 10000 10000 Cpd Bal 1 � 10 � Wthd 2 � 2000 � 10000 10000 W Bal 1 � 15 � W Bal 2 � 15 � 11000 8000 R Bal 2 � 15 � R Bal 1 � 15 � 11000 8000 Wthd 2 � 2000 � Cpd Bal 1 � 10 � 11000 8000 W Bal 2 � 15 � W Bal 1 � 15 � 9000 8800 • Both schedules are serial and both are correct ... • ... even though the results differ. • The order of serial execution does not affect correctness. • The system cannot and should not decide which order is better. Transaction Models and Concurrency Control 20130903 Slide 5 of 100

Lost Updates • If the steps of the transactions are interleaved in certain ways, updates may be lost. Shown below are two possibilities for schedules for these transactions. Bal � 15 � Bal � 15 � T 1 T 2 T 1 T 2 R Bal 1 � 15 � 10000 R Bal 2 � 15 � 10000 Cpd Bal 1 � 10 � Wthd 2 � 2000 � 10000 10000 R Bal 2 � 15 � R Bal 1 � 15 � 10000 10000 Wthd 2 � 2000 � 10000 Cpd Bal 1 � 10 � 10000 W Bal 2 � 15 � W Bal 1 � 15 � 8000 11000 W Bal 1 � 15 � W Bal 2 � 15 � 11000 8000 • In the schedule on the left, the result of T 2 is lost. • In the schedule on the right, the result of T 1 is lost. Transaction Models and Concurrency Control 20130903 Slide 6 of 100

Basic Steps and Transactions • To study the issues surrounding concurrency systematically, some formal notions are necessary. Basic steps: A basic step for a transaction T is either a read r � x � or a write w � x � of a data object x . • The actual values of x which are read and written are not important to the model. • The internal steps ( e.g. , R Bal i � x � , R Bal i � x � , Wthd i � n � , Cpd Bal i � n � ) are not represented. • Only the fact that T read or wrote that object is important. • For T i , these are usually written r i � x � and w i � x � , respectively. Transaction: A transaction T = � t 1 , t 2 , . . . , t n � is a finite sequence of steps, with each t i a basic step for T . Example: T 1 = r 1 � x � r 1 � y � w 1 � y � w 1 � z � is a transaction. • Steps � T � denotes the set of basic steps of T . Example: Steps � T 1 � = { r 1 � x � , r 1 � y � , w 1 � y � , w 1 � z �} . Transaction Models and Concurrency Control 20130903 Slide 7 of 100

Schedules • A schedule for a set of transactions is a specification of the order in which the basic steps will be executed. • Formally, let T = { T 1 , T 2 , . . . , T m } be a set of transactions, with T i = � t i 1 , t i 2 , . . . , t in i � for 1 ≤ i ≤ m . The steps of a schedule: Define Steps � T � = � m i =1 Steps � T i � . Schedule: A schedule S for T is any total ordering ≤ S of the set Steps � T � with the property that t ij ≤ S t ik whenever j ≤ S k . • In other words, the order of elements within each T i is preserved. Transaction Models and Concurrency Control 20130903 Slide 8 of 100

Serial Schedules Serial schedules: A schedule S for the set T = { T 1 , T 2 , . . . , T m } of transactions is serial if there is a total ordering ≤ of T with the property that if T i < T j , then all elements of T i occur before any element of T j in the ordering ≤ S . T 1 =r 1 � x � r 1 � y � w 1 � x � w 1 � y � Examples: Let T 2 =r 2 � z � w 2 � z � w 2 � y � T 3 =r 3 � z � w 3 � z � r 3 � x � w 3 � x � • Then r 2 � z � w 2 � z � w 2 � y � r 1 � x � r 1 � y � w 1 � x � w 1 � y � r 3 � z � w 3 � z � r 3 � x � w 3 � x � is the schedule corresponding to T 2 < T 1 < T 3 , while r 1 � x � r 1 � y � r 3 � z � w 3 � z � r 2 � z � w 1 � x � w 1 � y � w 2 � z � w 2 � y � r 3 � x � w 3 � x � is not a serial schedule. Transaction Models and Concurrency Control 20130903 Slide 9 of 100

Serializability • A serial schedule exhibits a correct semantics of concurrency, as there is no undesirable intertwining of actions of different transactions. • Allowing only serial schedules is too restrictive. • It prohibits any form or concurrency whatever. • Performance would be compromised greatly in many situations. • The solution is to allow serializable schedules – ones which are equivalent to serial schedules. • Parallelism is allowed. • The correctness of transactions is not compromised. Question: How is serializability defined? • It turns out that there are (at least) three reasonable definitions. Transaction Models and Concurrency Control 20130903 Slide 10 of 100

Three Notions of Serializability View serializability: In view serializability, it is ensured that the reads and subsequent writes of each data object occur in the same order as in some serial schedule. • This is the most important theoretical notion of serializability. • It is the “correct” theoretical notion of serializability. • Testing a schedule for view serializability is NP-complete. Final-state serializability: In final-state serializability, it is ensured that the final result ( i.e. , the final values of the data objects) is the same as in some serial schedule. • This form of serializability is strictly weaker than view serializability and not widely used. • It will not be considered further in this course. Conflict serializability: In conflict serializability, specific forms of conflict are ruled out. • Conflict serializability is strictly stronger than view serializability. • It is of interest because there exist efficient algorithms for testing conflict serializability. Transaction Models and Concurrency Control 20130903 Slide 11 of 100

The Three Conditions Surrounding View Equivalence • Let T = { T 1 , T 2 , . . . , T m } be a set of transactions, and let S be a schedule for T . • Let r i � x � ∈ Steps � T i � and w j � x � ∈ Steps � T j � . Read from: r i � x � reads from w j � x � in S if w j � x � ≤ S r i � x � and there is no k � = j for which w j � x � ≤ S w k � x � ≤ S r i � x � . Initial read: r i � x � is an initial read in S if there is no k for which w k � x � ≤ S r i � x � . Final write: w j � x � is a final write in S if there is no k � = j for which w j � x � ≤ S w k � x � . Example: In r 1 � x � r 1 � y � r 3 � z � w 3 � z � r 2 � z � w 1 � x � w 1 � y � w 2 � z � w 2 � y � r 3 � x � w 3 � x � • r 2 � z � reads from w 3 � z � . • r 3 � x � reads from w 1 � x � . • r 1 � x � , r 1 � y � , and r 3 � z � are initial reads. • w 2 � z � , w 2 � y � , and w 3 � x � are final writes. Transaction Models and Concurrency Control 20130903 Slide 12 of 100