UNIT-III

TRANSACTIONS
 Transaction Concepts
• A transaction can be defined as a group of tasks that form a
single logical unit
• Collection of operations that form a single logical unit of work.
Eg: Withdraw Rs. 100 from an account then the following
operations are:
1. Check account balance
2. If sufficient balance is present request for withdrawal
3. Get the money
4. Calculate Balance = Balance – 100
5. Update account with new balance
• The transaction consists of all operations executed between the
begin transaction and end transaction
• In database, each transaction should maintain ACID property
to meet the consistency and integrity of the database
• Two main issues to deal with:
– Failures of various kinds, such as hardware and system
crashes
– Concurrent execution of multiple transactions
Transaction processing system:
– The system with large database and hundreds of concurrent users
that are executing database transaction.
– Eg :reservation system , banking system etc
Concurrent access
• Multiple user accessing a system at the same time
Single user - one user at a time can use a system
Multi user - many user use the system at a time. It can be achieved by
multiprogramming:
• Parallel - multi-users access different resources at the same time.
• Interleaved - Multiple users access a single resource based on time.
Transaction access data using two operations
Read(x)
• It transfer the data item x from the database to a local buffer belonging to
the transaction that executed the read operation.
Write(x)
• It transfer the data item x from the local buffer of the transaction to the
database i.e. it write back to the database.
 ACID Properties
• To ensure the integrity of data during a transaction, the database system maintains
the following properties.
• These properties are widely known as ACID properties: A – Atomicity
C – Consistency
I – Isolation
D - Durability
 1. Atomicity
• This property states that each transaction must be considered
as a single unit and must be completed fully or not
completed at all
• No transaction in the database is left half completed
• Database should be in a state either before the transaction
execution or after the transaction execution. It should not be
in a state ‘executing’
• Maintained by Transaction Management Component
 2. Consistency
• Consistency stands for correctness
• This property ensures that any transaction will bring the database from valid state to
another
• The database must remain in consistent state after performing any transaction
• Responsibility of Application Programmer
 3. Isolation
• More than one transaction are being executed simultaneously and in parallel
• No transaction will affect the existence of any other transaction
• Ensures that concurrent execution results in a system state that would be obtained if
transaction would be executed serially
• Managed by Concurrency Control Manager
 4. Durability
• The database should be strong enough to handle any system failure
• Changes should be permanent
• The changes must NOT be lost due to some database failure
• If there is any set of insert/update, then it should be able to handle and commit to the
database
• If there is any failure, the database should be able to recover it to consistent state
• Responsibility of Recovery Manager
Transaction States
 Contd…
• A transaction must be in one of the following states:

 Active: the initial state

 Partially Committed: after the final statement has been executed
 Failed: after the discovery that normal execution can no longer proceed
 Aborted: after the transaction has been rolled back and the database has been restored to
its state prior to the state of the transaction
 Committed: after successful completion
 Example
1. A read only transaction
T1
Read(A)
Read(B)
Display(A-B)

2. A read write transaction

T1
Read(A)
A = A + 100
Write(A)
 Contd…
3. An aborted transaction
T1 T2
Read(A) Assume A=100
A=+50 A=150
Write(A)
Read(A) A=150
A=A+100 A=250

RollBack A=100 (restore back to original value

which is before Transaction T1)
Write(A)
 Implementation of Atomicity and Durability
• The recovery management component of a database system implements the support for
atomicity and durability
• The shadow-database scheme:
– Assume that only one transaction is active at a time
– A pointer called db_pointer always point to the current consistent copy of the
database
– In this method, all changes of transactions are updated in the shadow copy (duplicate
copy) of the database
– Once all the transaction is complete, the db_pointer is made to point to this shadow
database, making this as the new copy of the DB
– The old copy of the database is then deleted
 Contd…
• If there is any failure during the transaction, the pointer will still
pointing to old copy of database, and the shadow database will be
deleted
• If the transactions are complete, then the pointer is changed to
point to shadow DB, and old DB is deleted
 Concurrent Executions
• Multiple transactions are allowed to execute/run concurrently in the system
• Any number of users can use the same database at the same time.
• Needed in order to avoid inconsistencies in the database

• Advantages:
– Increased processor and disk utilization
– Reduced average response time
 Schedules
• Schedule is an order of multiple transactions executing in concurrent environment
• Sequences that indicate the chronological order in which instructions of concurrent
transactions are executed
– A schedule for a set of transactions must consist of all instructions of those
transactions
– Must preserve the order in which the instructions appear in each individual
transaction
Types of Schedule

Schedule

Serial Non Serial

Serializable Non Serializable

Conflict View
 Contd…
• Serial Schedule: The schedule in which the transactions
execute one after the other. It is consistent in nature.
Eg: T1 T2

R(A)
W(A)
R(B)
W(B)
R(A)
W(A)
R(B)
W(B)
• Non Serial Schedule: The schedule in which operations present
within the transaction are intermixed. This may lead to conflicts
in the result or inconsistency in the resultant data
Eg:
T1 T2

R(A)
W(A)
R(A)
W(B)
R(A)
W(B)
R(B)
W(B)
Schedule 1 (T1 is followed by T2)
Schedule 2 (T2 is followed by T1)
Schedule 3 (Equivalent to Schedule T1)

Not a serial schedule

Schedule 4 (Concurrent Schedule)
 Serializability
• When multiple transactions are being executed by the operating system in a
multiprogramming environment, there are possibilities that instructions of one
transactions are interleaved with some other transaction
• A set of transactions is serializable if and only if it is guaranteed to produce the same
result as when each transaction is completed prior to the following one being started
Serializable schedule:
If a schedule is equivalent to some serial schedule then that
schedule is called Serializable schedule.
Let us consider a schedule S. What the schedule S says ?
Read A after updation.
Read B before updation.
 Contd…
When are 2 schedules equivalent?
3 types of equivalence of schedules:
 Result Equivalence
 Conflict Equivalence
 View Equivalence
Based on the types of equivalence, we define the types of
Serializability. There are accordingly three types of Serializability
which are:
• Conflict Serializable
• View Serializable
 Result Equivalence
• In results equivalence, the end result of schedules heavily depend on input of schedules.
• The final results are calculated from both schedules (given and serial) and check whether
they are equal.
 Conflict
What is Conflict?
• A pair of operations in a schedule such that if their order is interchanged then the
behavior at least one of the transactions may change
• Operations are conflict, if they satisfy all three of the following conditions:
– They belong to different transactions
– They access the same data item
– At least one of the operation is a write operation
 Conflict Equivalence
• Schedules are conflict equivalent if they can be transformed one into other by a sequence
of non conflicting interchanges adjacent actions
Conflict Serializable Schedule
 Exercise Problem 1

Check whether the schedule is conflict serializable or not?

S: R1(A), W1(A), R2(A), R1(B), W1(B), R2(B)
Problem 1 Solution
 Exercise Problem 2

Check whether the schedule is conflict serializable or not?

S: R1(A), W1(A), R2(A), R2(B), R1(B), W1(B)
Problem 2 Solution
 Exercise Problem 1

Check whether the schedule is conflict serializable or not?

S: R1(A), R2(A), R1(B), R2(B), R3(B), W1(A), W2(B)
Problem 1 Solution
 Exercise Problem 2

Check whether the schedule is conflict serializable or not?

S: R1(A), R2(A), R3(B), W1(A), R2(C), R2(B), W2(B)
Problem 2 Solution
 View Serializable
S: R2(B) W2(A) R1(A) R3(A) W1(B) W2(B) W3(B)
 Concurrency Control
• Process of managing simultaneous execution of transactions in
a shared database, to ensure the serializability of transactions, is
known as concurrency control.
• Process of managing simultaneous operations on the database
without having them interfere with one another.
• Prevents interference when two or more users are accessing
database simultaneously and at least one is updating data.
• Although two transactions may be correct in
themselves, interleaving of operations may produce
an incorrect result.
• Simultaneous execution of transactions over a shared
database can create several data integrity and
consistency problems.

• Lost updated problem

• Temporary updated problem / Dirty Read
• Incorrect summery problem
• Unrepeatable Read Problem
• Phantom Read Problem
Dirty Read Problem
Unrepeatable Read Problem
Lost Update Problem
Phantom Read Problem
Incorrect Summary Problem
 Lock-Based Protocol

• In this type of protocol, any transaction cannot read or write

data until it acquires an appropriate lock on it.
• There are two types of lock:
• 1. Shared lock
• 2. Exclusive lock
1. Shared lock:
• It is also known as a Read-only lock. In a shared
lock, the data item can only read by the transaction.
• It can be shared between the transactions because
when the transaction holds a lock, then it can't
update the data on the data item.
2. Exclusive lock:
• In the exclusive lock, the data item can be both
reads as well as written by the transaction.
• This lock is exclusive, and in this lock, multiple
transactions do not modify the same data
simultaneously.
when two transactions are involved, and both attempt
to only read a given data item, it is permitted and no
conflict arises, but when one transaction attempts to
write the data item and another one tries to read or
write at the same time, conflict occurs resulting in a
denied interaction.
• There are four types of lock protocols
available:
1. Simplistic lock protocol
2. Pre-claiming Lock Protocol
3. Two-phase locking (2PL)
4. Strict Two-phase locking (Strict-2PL)
1. Simplistic lock protocol
• It is the simplest way of locking the data while
transaction.
• Simplistic lock-based protocols allow all the
transactions to get the lock on the data before
insert or delete or update on it.
• It will unlock the data item after completing
the transaction.
2. Pre-claiming Lock Protocol
• Pre-claiming Lock Protocols evaluate the transaction
to list all the data items on which they need locks.
• Before initiating an execution of the transaction, it
requests DBMS for all the lock on all those data
items.
• If all the locks are granted then this protocol allows
the transaction to begin. When the transaction is
completed then it releases all the lock.
• If all the locks are not granted then this protocol
allows the transaction to rolls back and waits until all
the locks are granted.
 3. Two-phase locking (2PL)

• The two-phase locking protocol divides the

execution phase of the transaction into three parts.
• In the first part, when the execution of the
transaction starts, it seeks permission for the lock it
requires.
• In the second part, the transaction acquires all the
locks. The third phase is started as soon as the
transaction releases its first lock.
• In the third phase, the transaction cannot demand
any new locks. It only releases the acquired locks.
• There are two phases of 2PL:
• Growing phase: In the growing phase, a new
lock on the data item may be acquired by the
transaction, but none can be released.
• Shrinking phase: In the shrinking phase,
existing lock held by the transaction may be
released, but no new locks can be acquired.
 4. Strict Two-phase locking (Strict-2PL)

• The first phase of Strict-2PL is similar to 2PL. In the

first phase, after acquiring all the locks, the
transaction continues to execute normally.
• The only difference between 2PL and strict 2PL is
that Strict-2PL does not release a lock after using it.
• Strict-2PL waits until the whole transaction to
commit, and then it releases all the locks at a time.
• Strict-2PL protocol does not have shrinking phase of
lock release.
• Conversion between the locks is possible by
the two methods listed below:
Upgrading
• conversion from a read lock to write lock
• Take place in only the growing phase
Downgrading 
• conversion from a write lock to read lock
• Take place in only the shrinking phase
 Timestamp-based Protocols

• The most commonly used concurrency protocol is the timestamp based

protocol. This protocol uses either system time or logical counter as a
timestamp.
• Lock-based protocols manage the order between the conflicting pairs
among transactions at the time of execution, whereas timestamp-based
protocols start working as soon as a transaction is created.
• Every transaction has a timestamp associated with it, and the ordering
is determined by the age of the transaction. A transaction created at
0002 clock time would be older than all other transactions that come
after it. For example, any transaction 'y' entering the system at 0004 is
two seconds younger and the priority would be given to the older one.
• In addition, every data item is given the latest read and write-
timestamp. This lets the system know when the last ‘read and write’
operation was performed on the data item.
 Timestamp Ordering Protocol

• The timestamp-ordering protocol ensures

serializability among transactions in their conflicting
read and write operations. This is the responsibility
of the protocol system that the conflicting pair of
tasks should be executed according to the timestamp
values of the transactions.
• The timestamp of transaction Ti is denoted as TS(Ti).
• Read time-stamp of data-item X is denoted by R-
timestamp(X).
• Write time-stamp of data-item X is denoted by W-
timestamp(X).
• Timestamp ordering protocol works as follows −
• If a transaction Ti issues a read(X) operation −
– If TS(Ti) < W-timestamp(X)
• Operation rejected.
– If TS(Ti) >= W-timestamp(X)
• Operation executed.
– All data-item timestamps updated.
• If a transaction Ti issues a write(X) operation −
– If TS(Ti) < R-timestamp(X)
• Operation rejected.
– If TS(Ti) < W-timestamp(X)
• Operation rejected and Ti rolled back.
– Otherwise, operation executed.
 Log

• • Log is a history of actions executed by a

database management system to guarantee
ACID properties over crashes or hardware
failures.

• Physically, a log is a file of updates done to the

database, stored in stable storage.
 Log rule

– A log records for a given database update must

be physically written to the log, before the update
physically written to the database.
 – All other log record for a given transaction
must be physically written to the log, before the
commit log record for the transaction is
physically written to the log.
– Commit processing for a given transaction must
not complete until the commit log record for the
transaction is physically written to the log.
System log

– [ Begin transaction ,T ]
– [ write_item , T, X , oldvalue,newvalue]
– [read_item,T,X]
– [commit,T]
– [abort,T]
 DEADLOCK

• System is deadlocked if there is a set of

transactions such that every transaction in the
set is waiting for another transaction in the set.
• Consider the following two transactions:
 TRANSACTION RECOVERY
• Recovery Algorithms
• Recovery algorithms are techniques to ensure database consistency and
transaction atomicity and durability despite failures

• Recovery algorithms have two parts

– Actions taken during normal transaction processing to ensure enough
information exists to recover from failures
•  
– Actions taken after a failure to recover the database contents to a state that
ensures atomicity, consistency and durability
Example
Begin transaction
Update Acc 1001
{balance:=Balance-100};
If any error occurred then
Goto Undo;
End if;
Update Acc 1002{balance:=balance+100};
If any error occurred then
Goto undo;
 End if;
Commit;
Goto finish;
 Undo: rollback;
Finish: return;
Requirement for recovery
Implicit rollback
Message handling
Recovery log
Statement atomicity
No nested transaction
Transaction recovery
• Database updates are kept in buffer in main
memory and not physically written to disk
until commit.
System recovery
• Local failures –affect only the transaction
which the failure has actually occurred.
• Global failures- affect all the transaction in
progress at the time of failure.
• System failure – do not physically damage the
DB Eg: power shut down
• Media failure-cause damage to the DB.
 ISOLATION LEVEL
• Degree of interference
• An isolation levels mechanism is used to isolate each
transaction in a multi-user environment
• Dirty Reads: This situation occurs when
transactions read data that has not been committed.
• Non repeatable Reads: This situation occurs when a
transaction reads the same query multiple times and
results are not the same each time
• Phantoms: This situation occurs when a row of data
matches the first time but does not match subsequent
times
Types
Higher isolation level (Repeatable read)
– Less interference
– Lower concurrency 
– All schedules are serializable
Lower isolation level(cursor stability)
– More interference
– Higher concurrency
– Not a serializable
Thank You