Download Database Systems: Understanding Transactions and Serializability and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Lecture 35 of 42
Wednesday, 15 November 2006
William H. Hsu Department of Computing and Information Sciences, KSU
KSOL course page: http://snipurl.com/va Course web site: http://www.kddresearch.org/Courses/Fall-2006/CIS Instructor home page: http://www.cis.ksu.edu/~bhsu
Reading for Next Class: First half of Chapter 15, Silberschatz et al. , 5th^ edition
Transactions: ACID Properties
Discussion: ACID Definitions, MP6-
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Chapter 15: Transactions Chapter 15: Transactions
z Transaction Concept z Transaction State z Concurrent Executions z Serializability z Recoverability z Implementation of Isolation z Transaction Definition in SQL z Testing for Serializability.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Transaction Concept Transaction Concept
z A transaction is a unit of program execution that accesses and possibly updates various data items. z A transaction must see a consistent database. z During transaction execution the database may be temporarily inconsistent. z When the transaction completes successfully (is committed), the database must be consistent. z After a transaction commits, the changes it has made to the database persist, even if there are system failures. z Multiple transactions can execute in parallel. z Two main issues to deal with: Failures of various kinds, such as hardware failures and system crashes Concurrent execution of multiple transactions
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
ACID Properties ACID Properties
z Atomicity. Either all operations of the transaction are properly reflected in the database or none are. z Consistency. Execution of a transaction in isolation preserves the consistency of the database. z Isolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. That is, for every pair of transactions T (^) i and T (^) j, it appears to T (^) i that either T (^) j, finished execution before T (^) i started, or T (^) j started execution after T (^) i finished. z Durability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
A transaction is a unit of program execution that accesses and possibly updates various data items.To preserve the integrity of data the database system must ensure:
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Transaction State Transaction State
z Active – the initial state; the transaction stays in this state while it is executing z Partially committed – after the final statement has been executed. z Failed -- after the discovery that normal execution can no longer proceed. z Aborted – after the transaction has been rolled back and the database restored to its state prior to the start of the transaction. Two options after it has been aborted: restart the transaction; can be done only if no internal logical error kill the transaction z Committed – after successful completion.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Transaction State (Cont.)Transaction State (Cont.)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Implementation of Atomicity and Durability Implementation of Atomicity and Durability
z The recovery-management component of a database system implements the support for atomicity and durability. z The shadow-database scheme: assume that only one transaction is active at a time. a pointer called db_pointer always points to the current consistent copy of the database. all updates are made on a shadow copy of the database, and db_pointer is made to point to the updated shadow copy only after the transaction reaches partial commit and all updated pages have been flushed to disk. in case transaction fails, old consistent copy pointed to by db_pointer can be used, and the shadow copy can be deleted.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Implementation of Atomicity and Durability (Cont.) Implementation of Atomicity and Durability (Cont.)
z Assumes disks do not fail
z Useful for text editors, but
extremely inefficient for large databases (why?) Does not handle concurrent transactions
z Will study better schemes in Chapter 17.
The shadow-database scheme :
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Schedule 1 Schedule 1
z Let T 1 transfer $50 from A to B , and T 2 transfer 10%
of the balance from A to B.
z A serial schedule in which T 1 is followed by T 2 :
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Schedule 2 Schedule 2
- A serial schedule where T 2 is followed by T 1
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Schedule 3 Schedule 3
z Let T 1 and T 2 be the transactions defined previously_._ The following schedule is not a serial schedule, but it is equivalent to Schedule 1.
In Schedules 1, 2 and 3, the sum A + B is preserved.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Schedule 4 Schedule 4
z The following concurrent schedule does not preserve the value of ( A + B).
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Conflict Serializability Conflict Serializability
z 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. z We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Conflict Serializability (Cont.)Conflict Serializability (Cont.)
z Schedule 3 can be transformed into Schedule 6, a
serial schedule where T 2 follows T 1 , by series of
swaps of non-conflicting instructions.
Therefore Schedule 3 is conflict serializable.
Schedule 3 Schedule 6
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Conflict Serializability (Cont.)Conflict Serializability (Cont.)
z Example of a schedule that is not conflict serializable:
z We are unable to swap instructions in the above schedule to obtain either the serial schedule < T 3 , T 4 >, or the serial schedule < T 4 , T 3 >.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
View SerializabilityView Serializability
z Let S and S ´ be two schedules with the same set of
transactions. S and S ´ are view equivalent if the following
three conditions are met:
- For each data item Q, if transaction T (^) i reads the initial value of Q in schedule S, then transaction T (^) i must, in schedule S ´, also read the initial value of Q. 2. For each data item Q if transaction T (^) i executes read ( Q) in schedule S , and that value was produced by transaction T (^) j (if any), then transaction T (^) i must in schedule S ´ also read the value of Q that was produced by transaction T (^) j.
- 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.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 15 Nov 2006 Kansas State University
Testing for Serializability Testing for Serializability
z Consider some schedule of a set of transactions T 1 , T 2 , ..., Tn z Precedence graph — a direct graph where the vertices are the transactions (names). z We draw an arc from Ti to Tj if the two transaction conflict, and Ti accessed the data item on which the conflict arose earlier. z We may label the arc by the item that was accessed. z Example 1 x
y
