Download Transaction Processing: A Comprehensive Guide and more Slides Introduction to Database Management Systems in PDF only on Docsity!
8-
Transaction
n A transaction is a collection of actions that make
consistent transformations of system states while
preserving system consistency.
l concurrency transparency l failure transparency
Database in a consistent state
Database may be temporarily in an inconsistent state during execution
Begin Transaction
End Transaction
Execution of Transaction
Database in a consistent state
Transaction Example –
A Simple SQL Query
main() { … EXEC SQL UPDATE Project SET Budget = Budget * 1. WHERE Pname = `CAD/CAM’; EXEC SQL COMMIT RELEASE ; return (0); … }
8-
Example Database
Consider an airline reservation example with the
relations:
FLIGHT(FNO, DATE, SRC, DEST, STSOLD, CAP)
CUST(CNAME, ADDR, BAL)
FC(FNO, DATE, CNAME,SPECIAL)
Example Reservation Transaction
… main { … EXEC SQL BEGIN DECLARE SECTION ; char flight_no[6], customer_name[20]; char day; EXEC SQL END DECLARE SECTION ; scanf (flight_no, day, customer_name); EXEC SQL UPDATE FLIGHT SET STSOLD = STSOLD + 1 WHERE FNO = :flight_no AND DATE = :day; EXEC SQL INSERT INTO FC(FNO, DATE, CNAME, SPECIAL); VALUES (:flight_no,:day,:customer_name, null ); printf (“Reservation completed”); EXEC SQL COMMIT RELEASE ; return (0); }
8-
Let l oij ( x ) be some operation oj of transaction Ti operating on data item x , where oj ∈ {read,write} and oj is atomic l OSi = ∪ j oij l Ni ∈ {abort,commit} Transaction Ti is a partial order Ti = {Σ i , < i } where ∂ Σ i = OSi ∪ { Ni } ∑ For any two operations oij , oik ∈ OSi , if oij = R ( x ) and oik = W ( x ) for any data item x , then either oij < ioik or oik < ioij ∏ ∀ oij ∈ OSi , oij < i Ni
Formalization
Consider a transaction T :
Read( x ) Read( y ) x ← x + y Write( x ) Commit
Then
Σ = { R ( x ), R ( y ), W ( x ), C } < = {( R ( x ), W ( x )), ( R ( y ), W ( x )), ( W ( x ), C ), ( R ( x ), C ), ( R ( y ), C )}
Example
8-
Assume
< = {( R ( x ), W ( x )), ( R ( y ), W ( x )), ( R ( x ), C ), ( R ( y ), C ), ( W ( x ), C )}
DAG Representation
R ( x )
C
R ( y )
W ( x )
Properties of Transactions
ATOMICITY
l all or nothing
CONSISTENCY
l no violation of integrity constraints
ISOLATION
l concurrent changes invisible ⇒ serializable
DURABILITY
l committed updates persist
8-
Isolation
n Serializability
l If several transactions are executed concurrently, the results must be the same as if they were executed serially in some order.
n Incomplete results
l An incomplete transaction cannot reveal its results to other transactions before its commitment. l Necessary to avoid cascading aborts.
Isolation Example
n Consider the following two transactions:
T 1 : Read( x ) T 2 : Read( x ) x ← x + 1 x ← x + 1 Write( x ) Write( x ) Commit Commit
n Possible execution sequences:
T 1 : Read( x ) T 1 : Read( x ) T 1 : x ← x + 1 T 1 : x ← x + 1 T 1 : Write( x ) T 2 : Read( x ) T 1 : Commit T 1 : Write( x ) T 2 : Read( x ) T 2 : x ← x + 1 T 2 : x ← x + 1 T 2 : Write( x ) T 2 : Write( x ) T 1 : Commit T 2 : Commit T 2 : Commit
8-
Consistency Degrees
(due to Jim Gray)
n Degree 0
l Transaction T does not overwrite dirty data of other transactions l Dirty data refers to data values that have been updated by a transaction prior to its commitment
n Degree 1
l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT
Consistency Degrees (cont’d)
(due to Jim Gray)
n Degree 2
l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT l T does not read dirty data from other transactions
n Degree 3
l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT l T does not read dirty data from other transactions l Other transactions do not dirty any data read by T before T completes_._
8-
n Once a transaction commits, the system must guarantee that the results of its operations will never be lost, in spite of subsequent failures.
n Database recovery
Durability
Transactions Provide…
n Atomic and reliable execution in the presence of failures
n Correct execution in the presence of multiple user accesses
n Correct management of replicas (if they support it)
8-
Architecture
Scheduling/ Descheduling Requests
Transaction Manager (TM)
Transaction Monitor
Begin_transaction, Read, Write, Commit, Abort
To execution engine
Results
Scheduler (SC)
Transaction Execution
Begin_Transaction, Read, Write, Abort, EOT
Results & User Notifications
Scheduled Operations Results
Results
…
Read, Write, Abort, EOT
User Application
User Application
Transaction Manager (TM)
Scheduler (SC)
Recovery Manager (RM)
