Transaction Concepts 10 Hrs

Transaction Concepts
By Prof. Ashwini Satkar
Agenda
 Transaction Concepts
 Transaction Properties
 Transaction States
 Concurrent execution
 Serializability
Transaction
 A sequence of operations that must be treated as an
undivided unit.
 A transaction is a sequence of one or more SQL
statements that are executed as a single unit of work.
 Operations read and write
read(x)-Access resource x without modifying it.
write(x)-Modify resource x.
 Transaction must be either committed or aborted
 Example
A transfer between account. Money should be transferred or
withdrawn from account.
ACID Properties
Atomicity:
 We mean that either the entire transaction takes place at once
or doesn’t happen at all. There is no midway i.e. transactions
do not occur partially.
 Each transaction is considered as one unit and either runs to
completion or is not executed at all.
 It involves the following two operations.
Abort: If a transaction aborts, changes made to the database are
not visible.
Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Consider the following transaction T consisting of T1 and T2:
Transfer of 100 from account X to account Y.

 If the transaction fails after completion of T1 but before completion

of T2.( say, after write(X) but before write(Y)),
 The amount has been deducted from X but not added to Y. This results in
an inconsistent database state.
 The transaction must be executed in its entirely in order to ensure the
correctness of the database state.
Consistency
 This means that integrity constraints must be maintained so
that the database is consistent before and after the
transaction.
 It refers to the correctness of a database.
 Referring to the example above,
The total amount before and after the transaction must be
maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, the database is consistent.
 Inconsistency occurs in case T1 completes but T2 fails. As a
result, T is incomplete.
Isolation

 This property ensures that multiple transactions can occur

concurrently without leading to the inconsistency of the database
state.
 Transactions occur independently without interference.
 Changes occurring in a particular transaction will not be visible to
any other transaction until that particular change in that
transaction is written to memory or has been committed.
 This property ensures that the execution of transactions
concurrently will result in a state that is equivalent to a state
achieved these were executed serially in some order.
 Let X= 500, Y = 500.
Consider two transactions T and T”.

 Suppose T has been executed till Read (Y) and then T’’ starts.
 As a result, interleaving of operations takes place due to which T’’ reads the
correct value of X but the incorrect value of Y and sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of the transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
 This results in database inconsistency, due to a loss of 50 units. Hence,
transactions must take place in isolation and changes should be visible only after
they have been made to the main memory.
Durability

 This property ensures that once the transaction has

completed execution, the updates and modifications to the
database are stored in and written to disk and they persist
even if a system failure occurs.
 These updates now become permanent and are stored in
non-volatile memory.
 The effects of the transaction, thus are never lost.
 States of Transactions

Active − This is the state in which a transaction is being executed. Thus, it is

like the initial state of any given transaction. If all the ‘read and write’
operations are performed without any error then it goes to the “partially
committed state”; if any instruction fails, it goes to the “failed state”.
Partially Committed − After completion of all the read and write operation
the changes are made in main memory or local buffer. If the changes are made
permanent on the DataBase then the state will change to “committed state” and
in case of failure it will go to the “failed state”.
States of Transactions
 Failed − In case any check made by a database recovery
system fails, then that transaction is in a failed state.
Remember that a failed transaction can not proceed further.
 Aborted − In case any check fails, leading the transaction to a
failed state, the recovery manager then rolls all its write
operations back on the database so that it can bring the DB
(database) back to the original state (the state where it actually
was prior to the transaction execution). The transactions in this
state are known to be aborted. A DB recovery module can actually
select one of these two operations after the abortion of a
transaction –
 Re-start
 Kill the transaction
States of Transactions
 Committed − We can say that a transaction is committed in
case it actually executes all of its operations successfully. In
such a case, all of its effects are now established permanently
on the DB system.
 Terminated State –
If there isn’t any roll-back or the transaction comes from the
“committed state”, then the system is consistent and ready
for new transaction and the old transaction is terminated.
Schedule
 Schedule as the name suggests, is a process of lining the
transactions and executing them one by one.
 The order in which the operations of multiple transactions
appear for execution is called as a schedule
 When there are multiple transactions that are running in a
concurrent manner and the order of operation is needed to
be set so that the operations do not overlap each other.
 Serial Schedules
In serial schedules,
 All the transactions execute serially one after the other.
 When one transaction executes, no other transaction is
allowed to execute.
 Schedules in which the transactions are executed non-
interleaved, i.e., a serial schedule is one in which no
transaction starts until a running transaction has ended are
called serial schedules.
Characteristics-
Serial schedules are always-
 Consistent
 Recoverable
 Cascadeless
 Strict
Serial Schedule Examples
Non-Serial Schedules

In non-serial schedules,
 Multiple transactions execute concurrently.
 Operations of all the transactions are inter leaved or mixed with
each other.

Characteristics-
Non-serial schedules are NOT always-
 Consistent
 Recoverable
 Cascadeless
 Strict
Non-Serial Schedule Examples
Concurrent transaction
 Concurrent transaction or execution includes multiple transactions
which are executed concurrently or simultaneously in the system.
 We take ATM machines and we do not use concurrency, then multiple
persons cannot draw money at a time in different places. This is where
we need concurrency.
Advantages
The advantages of the concurrent transactions are as follows :
 Increases throughput which is nothing but number of transactions
completed per unit time.
 It reduces the waiting time.
Disadvantage
 The disadvantage is that the execution of concurrent transactions may
result in inconsistency.
Concurrent transaction
Transaction which is in consistent state.
T1 T2
Read(A)
A=A-50
Write(A)
Read(A)
Temp=A*0.1
A=A-temp
Write(A)
Read(B)
B=B+50
Write(B)
Read(B)
B=B+temp
Write(B)
Concurrent transaction
Transaction which is inconsistent state.
T1 T2
Read(A)
A=A-50
Read(A)
Temp=A*0.1
A=A-temp
Write(A)
Write(B)
Write(A)
Read(B)
B=B+50
Write(B)
B=B+temp
Write(B)
Concurrency Problems
When multiple transactions execute concurrently in an uncontrolled
or unrestricted manner, then it might lead to several problems.
These problems are commonly referred to as concurrency problems
in a database environment.
The five concurrency problems that can occur in the database are:
 Temporary Update Problem
 Incorrect Summary Problem
 Lost Update Problem
 Unrepeatable Read Problem
 Phantom Read Problem
 Temporary Update Problem

 Temporary update or dirty

read problem occurs when one
transaction updates an item and
fails.
 But the updated item is used by
another transaction before the item
is changed or reverted back to its
last value.
 If transaction T1 fails for some
reason then A will revert back to its
previous value. But transaction T2
has already read the incorrect value
of A.
 T2 reads the update value of X made by T1, but T1 fails and
rolls back.
 So, T2 reads an incorrect value of X.
 Incorrect Summary Problem

 Consider a situation, where one transaction is applying the

aggregate function on some records while another transaction is
updating these records.
 The aggregate function may calculate some values before the
values have been updated and others after they are updated.
 Transaction T2 is calculating the sum of some records while
transaction T1 is updating them.
 Therefore the aggregate function may calculate some values
before they have been updated and others after they have been
updated.
 Lost Update Problem
 In the lost update problem, an update done to a data item by a
transaction is lost as it is overwritten by the update done by
another transaction.
 A lost update problem occurs due to the update of the same
record by two different transactions at the same time. In
simple words, when two transactions are updating the same record
at the same time in a DBMS then a lost update problem occurs.
 Unrepeatable Read Problem

 This problem occurs when a transaction gets to read unrepeated

i.e. different values of the same variable in its different read
operations even when it has not updated its value.
Unrepeatable Read Problem

 T1 reads the value of X (= 10 say).

 T2 reads the value of X (= 10).
 T1 updates the value of X (from 10 to 15 say) in the buffer.
 T2 again reads the value of X (but = 15).

T2 gets to read a different value of X in its second reading.

T2 wonders how the value of X got changed because according
to it, it is running in isolation.
 Phantom Read Problem

 The phantom read problem occurs

when a transaction reads a variable
once but when it tries to read that
same variable again, an error occurs
saying that the variable does not exist.
 Once transaction T2 reads the variable
X, transaction T1 deletes the variable
X without transaction T2’s knowledge.
Thus, when transaction T2 tries to
read X, it is not able to do it.
Serializability

 A system is serializable if its result is the same as if the operations were

executed in some sequential order, meaning there is no overlap in
execution.
 A database management system (DBMS) can be accomplished by locking
data so that no other process can access it while it is being read or
written.
 It must be equivalent to some serial schedule of the same transactions.
 Serializability can also be checked using the precedence graph algorithm,
which checks for potential cycles between transactions' precedence
relationships.
 If there are no cycles then schedule is conflict serializable
 If there are cycles, the schedule may or may not be conflict
serializable.
 Serializable or not ?
T1 T2 T1 T2
T1 T2
read(X)
read(X)
read(Y)
read(X) read(X)
read(X)
read(Y) write(X)
read(Y)
write(X) write(Y)
read(Y)
write(Y)
read(X) read(Y)
write(X)
read(Y)
write(Y)
Not Serializable
Serializable
Serializable
 Types of serializability
View serializability
A schedule is view-serializable if it is view equivalent to a serial schedule.
 The rules it follows are given below −
 T1 is reading the initial value of A, and then T2 also reads the initial value of A.
 T1 is the reading value written by T2, and then T2 also reads the value written by T1.
 T1 is writing the final value, and then T2 also has the write operation as the final value.
Conflict serializability
It orders any conflicting operations in the same way as some serial execution. A pair of
operations is said to conflict if they operate on the same data item and one of them is a
write operation.
For example, consider a database with two tables: Customers and Orders. A customer can
have multiple orders, but each order can only be associated with one customer.
 Readi(x) readj(x) - non conflict read-read operation
 Readi(x) writej(x) - conflict read-write operation.
 Writei(x) readj(x) - conflict write-read operation.
 Writei(x) writej(x) - conflict write-write operation.
 Chk whether given schedule is conflict serializable
 S1: r1(x); r1(y); r2(x); r2(y); w2(y); w1(x);
 S2: r1(x); r2(x); r2(y); w2(y); r1(y); w1(x);

Cycle exists so schedule S1 is not Conflict Serializable

Cycle does not exists so schedule S2 is
Conflict Serializable
Which of the following schedules are conflict serializable?
S1:r1(X), r2(X),w1(X), r3(X),w2(X)
S2:r2(X), r1(X),w2(X), r3(X),w1(X)
S3:r3(X), r2(X), r1(X), w2(X), w1(X)
S4: r2(X), w2(X), r3(X), r1(X),w1(X)
Which of the following schedules are conflict serializable?
Recoverable schedule

Let us see an example of a recoverable schedule.

T1 T2 Here, transaction T2 is reading

value written by transaction
R(X) T1 and the commit of T2
W(X) occurs after the commit of T1.
W(X) Hence, it is a recoverable
schedule.
R(X)
Recoverable schedule is
commit divided into
commit Cascading
Cascade less
Strict schedule.
Cascading Schedule

 A cascading schedule is classified as a recoverable schedule.

 A recoverable schedule is basically a schedule in which the
commit operation of a particular transaction that performs
read operation is delayed until the uncommitted transaction
either commits or roll backs.
 A cascading rollback is a type of rollback in which if one
transaction fails, then it will cause rollback of other
dependent transactions.
 The main disadvantage of cascading rollback is that it can
cause CPU time wastage.
Cascading Schedule Example
T1 T2 T3 T4
Read(A)
Write(A)
Read (A)
Write(A)
Read(A)
Write(A)
Read(A)
Write(A)
Faliure

The above transaction is cascading rollback because of T1 failure,

T2 is rollback and rollback of T2 causes T3 to rollback and
rollback T3 causes the T4 to rollback.
Cascade less schedule
 When a transaction is not allowed to read data until the last transaction
which has written it is committed or aborted, these types of schedules
are called cascade less schedules.

T1 T2
R(X)
W(X)
W(X)
commit
R(X)
Commit

 Here, the updated value of X is read by transaction T2 only after the

commit of transaction T1. Hence, the schedule is cascade less schedule.
Strict schedule
 Given below is an example of a strict schedule −
T1 T2
R(X)
R(X)
W(X)
commit
T1
W(X)
R(X)
commit

 Here, transaction T2 reads and writes the updated or written

value of transaction T1 only after the transaction T1 commits.
Hence, the schedule is strict schedule.
Hierarchy of Schedules