chapter 15: transactions
DESCRIPTION
Chapter 15: Transactions. Transaction Concept Concurrent Executions Serializability Testing for Serializability. Transaction Concept. A transaction is a unit of program execution that accesses and possibly updates various data items. Two main issues to deal with: - PowerPoint PPT PresentationTRANSCRIPT
Chapter 15: TransactionsChapter 15: Transactions
Transaction ConceptTransaction Concept Concurrent ExecutionsConcurrent Executions SerializabilitySerializability Testing for SerializabilityTesting for Serializability
Transaction ConceptTransaction Concept A A transactiontransaction is a is a unit unit of program execution that of program execution that
accesses and possibly updates various data accesses and possibly updates various data items.items.
Two main issues to deal with:Two main issues to deal with: Failures of various kinds, such as hardware Failures of various kinds, such as hardware
failures and system crashesfailures and system crashes Concurrent execution of multiple transactionsConcurrent execution of multiple transactions
a1, a2, a3, a4, …, an, commit
Database may be inconsistentconsistent consistent
c1, c2, c3, c4, …, cl, commit
b1, b2, b3, b4, …, bm, commit
a1, a2, a3, a4, …, an, commit
ACID PropertiesACID Properties
AtomicityAtomicity ConsistencyConsistency IsolationIsolation DurabilityDurability
To preserve integrity of data, the database system must ensure:
Example of Fund TransferExample of Fund Transfer
Transaction to transfer $50 from account Transaction to transfer $50 from account AA to account to account BB::1.1.readread((AA))2.2.AA := := A – A – 50503.3.writewrite((AA))4.4.readread((BB))5.5.BB := := B + B + 50506.6.writewrite((B)B)7.7.commitcommit
AtomicityAtomicity Either all operations of the transaction are properly Either all operations of the transaction are properly
reflectedreflected
Or none areOr none are
(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit
(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50
ConsistencyConsistency A+B = TOT where TOT is a constant valueA+B = TOT where TOT is a constant value
Consistency: DB satisfies all integrity and constraintsConsistency: DB satisfies all integrity and constraints Examples:Examples:
- x is key of relation R- x is key of relation R - x - x y holds in R y holds in R - Domain(x) = {Red, Blue, Green}- Domain(x) = {Red, Blue, Green} is valid index for attribute x of Ris valid index for attribute x of R no employee should make more than twice the average salaryno employee should make more than twice the average salary A+B = TOTA+B = TOT
A+B may not equal to TOTA+B= TOT A+B= TOT
(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit
IsolationIsolation Intermediate transaction Intermediate transaction
results must be hidden from results must be hidden from other concurrently executed other concurrently executed transactions. transactions.
A+B may not equal to TOTA+B= TOT A+B= TOT
T2
A+B ≠ TOT?!
(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit
DurabilityDurability After a transaction completes successfully, the changes it After a transaction completes successfully, the changes it
has made to the database persist, even if there are system has made to the database persist, even if there are system failures. failures.
After this point, A and B are permanently updated
(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit
Transaction StateTransaction State
ActiveActive Partially committedPartially committed CommittedCommitted FailedFailed AbortedAborted
Transaction State (Cont.)Transaction State (Cont.)
a1, a2, a3, a4, …, an, commit
Implementation of Atomicity and Implementation of Atomicity and DurabilityDurability
Assumes one transaction at a timeAssumes one transaction at a time Useful for text editors, but extremely inefficient for Useful for text editors, but extremely inefficient for
large databases: executing a single transaction large databases: executing a single transaction requires copying the requires copying the entire entire database. database.
The shadow-database scheme:
Storage HierarchyStorage Hierarchy
Read(x) read x from memory, if it is not in memory yet, Read(x) read x from memory, if it is not in memory yet, read from disk firstread from disk first
Write(x) writes x to memory and Write(x) writes x to memory and possiblypossibly to disk to disk
Memory Disk
x x1.1.readread((AA))2.2.AA := := A – A – 50503.3.writewrite((AA))4.4.readread((BB))5.5.BB := := B + B + 50506.6.writewrite((B)B)7.7.commitcommit
SchedulesSchedulesT1
Read(A)
A:=A-50
Write(A)
Read(B)
B:=B+50
Write(B)
T2
Read(A)
Temp:=A*0.1
A:=A-temp
Write(A)
Read(B)
B:=B+temp
Write(B)
Schedule 1
Read(A)A:=A-50Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)Write(A)Read(B)B:=B+50Write(B)
B:=B+tempWrite(B)
T1 transfer $50 from A to B
T2 transfer 10% of the balance from A to B
SchedulesSchedules
SchedulesSchedules – sequences that indicate the chronological order – sequences that indicate the chronological order in which instructions of concurrent transactions are in which instructions of concurrent transactions are executedexecuted a schedule for a set of transactions must consist of all a schedule for a set of transactions must consist of all
instructions of those transactionsinstructions of those transactions must preserve the order in which the instructions appear must preserve the order in which the instructions appear
in each individual transaction.in each individual transaction.
Concurrent ExecutionsConcurrent Executions
Multiple transactions are allowed to run concurrently in Multiple transactions are allowed to run concurrently in the systemthe system
Concurrency control schemesConcurrency control schemes – mechanisms to – mechanisms to achieve isolation, i.e., to control the interaction among achieve isolation, i.e., to control the interaction among the concurrent transactions in order to prevent them the concurrent transactions in order to prevent them from destroying the consistency of the databasefrom destroying the consistency of the database
Serial ScheduleSerial Schedule TT11 is followed by is followed by TT22..
A = 100, B = 100 originally
A = ? and B = ?
Schedule 2
Read(A)A:=A-50Write(A)Read(B)B:=B+50Write(B)Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)
B:=B+tempWrite(B)
Example Schedule (Cont.)Example Schedule (Cont.) Schedule 3Schedule 3 is equivalent is equivalent to Schedule 1. to Schedule 1.
In both Schedule 2 and 3, the sum A + B is preserved.
A = 100, B = 100 originally
A = ? and B = ?
Schedule 3
Read(A)A:=A-50Write(A)Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)B:=B+50Write(B)Read(B)
B:=B+tempWrite(B)
Example Schedules (Cont.)Example Schedules (Cont.)
A = 100, B = 100 originally
A = ? and B = ?
Schedule 4 does not preserve the sum A + B
Schedule 4
Read(A)A:=A-50Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)Write(A)Read(B)B:=B+50Write(B)
B:=B+tempWrite(B)
Where is the mystery?Where is the mystery?
How to preserve database How to preserve database consistency?consistency?
Serializability!
SerializabilitySerializability
A (possibly concurrent) schedule is serializable if it is A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule. equivalent to a serial schedule.
Conflict SerializabilityConflict Serializability
Transactions TTransactions T11 and T and T22 Two operations on the same Two operations on the same
item Q, item Q,
Intuitively, a conflict between Intuitively, a conflict between TT11 and T and T22 forces a (logical) forces a (logical) temporal order between Ttemporal order between T11 and Tand T22 . .
Two consecutive non-conflict Two consecutive non-conflict operations in a schedule can operations in a schedule can been interchangedbeen interchanged
Read(Q)Read(Q) Write(Q)Write(Q)Read(Q)Read(Q)Write(Q)Write(Q)
T1T2
Conflict?
Conflict Serializability (Cont.)Conflict Serializability (Cont.)
If a schedule If a schedule SS can be transformed into a schedule can be transformed into a schedule SS´ ´ by a series of swaps of non-conflicting by a series of swaps of non-conflicting instructions, we say that instructions, we say that SS and and S´ S´ are are conflict conflict equivalentequivalent..
NoteNote
Only read and write operations will cause conflictOnly read and write operations will cause conflict Other operations (A:=A+10) are on local copy variables and Other operations (A:=A+10) are on local copy variables and
do not interface with database do not interface with database
Simplified SchedulesSimplified SchedulesSchedule 3
Read(A)A:=A-50Write(A)Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)B:=B+50Write(B)Read(B)
B:=B+tempWrite(B)
Schedule 3
Read(A)Write(A)Read(A)Write(A)Read(B)Write(B)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 2
Read(A)A:=A-50Write(A)Read(B)B:=B+50Write(B)Read(A)
Temp:=A*0.1A:=A-tempWrite(A)Read(B)
B:=B+tempWrite(B)
Schedule 3 and Schedule 2 are Schedule 3 and Schedule 2 are conflict equivalentconflict equivalentSchedule 3
Read(A)Write(A)Read(A)Write(A)Read(B)Write(B)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 3 and Schedule 2 are Schedule 3 and Schedule 2 are conflict equivalentconflict equivalentSchedule 3
Read(A)Write(A)Read(A)Read(B)Write(A)Write(B)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 3 and Schedule 2 are Schedule 3 and Schedule 2 are conflict equivalentconflict equivalentSchedule 3
Read(A)Write(A)Read(A)Read(B)Write(B)Write(A)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 3 and Schedule 2 are Schedule 3 and Schedule 2 are conflict equivalentconflict equivalentSchedule 3
Read(A)Write(A)Read(B)Read(A)Write(B)Write(A)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 3 and Schedule 2 are Schedule 3 and Schedule 2 are conflict equivalentconflict equivalentSchedule 3
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Schedule 2
Read(A)Write(A)Read(B)Write(B)Read(A)Write(A)Read(B)Write(B)
Conflict Serializability (Cont.)Conflict Serializability (Cont.)
We say that a schedule We say that a schedule SS is is conflict serializableconflict serializable if if it is conflict equivalent to a serial scheduleit is conflict equivalent to a serial schedule
Schedule 3 is Schedule 3 is conflict serializableconflict serializable
Conflict Serializability (Cont.)Conflict Serializability (Cont.)
Example of a schedule that is not conflict Example of a schedule that is not conflict serializable:serializable:
TT33 TT44readread((QQ))
writewrite((QQ))writewrite((QQ))
We are unable to swap instructions in the above We are unable to swap instructions in the above schedule to obtain either the serial schedule < schedule to obtain either the serial schedule < TT33, , TT44 >, or the serial schedule < >, or the serial schedule < TT44, , TT33 >. >.
Testing for SerializabilityTesting for Serializability Consider some schedule of a set of transactions Consider some schedule of a set of transactions TT11, ,
TT22, ..., , ..., TTnn Precedence graphPrecedence graph — a direct graph where the — a direct graph where the
vertices are the transactions (names).vertices are the transactions (names). We draw an arc from We draw an arc from TTii to to TTjj if the two transaction if the two transaction
conflict, and conflict, and TTii accessed the data item on which the accessed the data item on which the conflict arose earlier.conflict arose earlier.
We may label the arc by the item that was accessed.We may label the arc by the item that was accessed. Example 1Example 1
x
y
Example Schedule (Schedule A)Example Schedule (Schedule A)TT11 TT22 TT33 TT44 TT55
read(X)read(X)read(Y)read(Y)read(Z)read(Z)read(V)read(V)read(W)read(W)read(W)read(W)read(Y)read(Y)write(Y)write(Y)write(Z)write(Z)read(U)read(U)read(Y)read(Y)write(Y)write(Y)read(Z)read(Z)write(Z)write(Z)read(U)read(U)write(U)write(U)
Precedence Graph for Schedule APrecedence Graph for Schedule A
T3T4
T1 T2
T5
Test for Conflict SerializabilityTest for Conflict Serializability
A schedule is conflict serializable if and only if its A schedule is conflict serializable if and only if its precedence graph is acyclic.precedence graph is acyclic.
Cycle-detection algorithms exist which take order Cycle-detection algorithms exist which take order nn22 time, time, where where n n is the number of vertices in the graph. (Better is the number of vertices in the graph. (Better algorithms take order algorithms take order nn + + ee where where ee is the number of is the number of edges.)edges.)
If precedence graph is acyclic, the serializability order can If precedence graph is acyclic, the serializability order can be obtained by a be obtained by a topological sortingtopological sorting of the graph. This is a of the graph. This is a linear order consistent with the partial order of the graph.linear order consistent with the partial order of the graph.For example, a serializability order for Schedule A would beFor example, a serializability order for Schedule A would beTT55 TT11 TT33 TT22 TT44 . .
Concurrency Control vs. Serializability Concurrency Control vs. Serializability TestsTests
Testing a schedule for serializability Testing a schedule for serializability afterafter it has executed is it has executed is a little too late!a little too late!
Goal – to develop concurrency control protocols that will Goal – to develop concurrency control protocols that will assure serializability. They will generally not examine the assure serializability. They will generally not examine the precedence graph as it is being created; instead a protocol precedence graph as it is being created; instead a protocol will impose a discipline that avoids nonseralizable will impose a discipline that avoids nonseralizable schedules.schedules.
Tests for serializability help understand why a concurrency Tests for serializability help understand why a concurrency control protocol is correct. control protocol is correct.