CS8492-Database Management Systems

UNIT III - TRANSACTIONS

Transaction Concepts – ACID Properties – Schedules – Serializability – Concurrency Control – Need
for Concurrency – Locking Protocols – Two Phase Locking – Deadlock – Transaction Recovery - Save
Points – Isolation Levels – SQL Facilities for Concurrency and Recovery

1. TRANSACTION CONCEPTS
What is Transaction?
A set of logically related operations is known as transaction. The main operations of a transaction are:
Read(A): Read operations Read(A) or R(A) reads the value of A from the database and stores it in a buffer
in main memory.
Write (A): Write operation Write(A) or W(A) writes the value back to the database from buffer.
Let us take a debit transaction from an account which consists of following operations:
1.R(A);
2.A=A-1000;
3.W(A);
Assume A’s value before starting of transaction is 5000.
• The first operation reads the value of A from database and stores it in a buffer.
• Second operation will decrease its value by 1000. So buffer will contain 4000.
• Third operation will write the value from buffer to database. So A’s final value will be 4000.
But it may also be possible that transaction may fail after executing some of its operations. The failure can be
because of hardware, software or power etc. For example, if debit transaction discussed above fails after
executing operation 2, the value of A will remain 5000 in the database which is not acceptable by the bank.

To avoid this, Database has two important operations:

Commit: After all instructions of a transaction are successfully executed, the changes made by transaction are
made permanent in the database.
Rollback: If a transaction is not able to execute all operations successfully, all the changes made by transaction
are undone.

1 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Why do you need concurrency in Transactions?

A database is a shared resource accessed. It is used by many users and processes concurrently. For example,
the banking system, railway, and air reservations systems, stock market monitoring, supermarket inventory,
and checkouts, etc.

Not managing concurrent access may create issues like:

• Hardware failure and system crashes

• Concurrent execution of the same transaction, deadlock, or slow performance

States of Transactions

The various states of a Database Transaction are listed below

State Transaction types

Active State A transaction enters into an active state when the execution process begins. During this
state read or write operations can be performed.

Partially A transaction goes into the partially committed state after the end of a transaction.
Committed

Committed When the transaction is committed to state, it has already completed its execution
State successfully. Moreover, all of its changes are recorded to the database permanently.

Failed State A transaction considers failed when any one of the checks fails or if the transaction is
aborted while it is in the active state.

Terminated State of transaction reaches terminated state when certain transactions which are
State leaving the system can't be restarted.

2 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Transition Diagram for a Database Transaction

Let's study a state transition diagram that highlights how a transaction moves between these various states.
1. Once a transaction states execution, it becomes active. It can issue READ or WRITE operation.
2. Once the READ and WRITE operations complete, the transactions becomes partially committed state.
3. Next, some recovery protocols need to ensure that a system failure will not result in an inability to record
changes in the transaction permanently. If this check is a success, the transaction commits and enters
into the committed state.
4. If the check is a fail, the transaction goes to the Failed state.
5. If the transaction is aborted while it's in the active state, it goes to the failed state. The transaction should
be rolled back to undo the effect of its write operations on the database.
6. The terminated state refers to the transaction leaving the system.

2. ACID PROPERTIES
A transaction is a single logical unit of work which accesses and possibly modifies the contents of a
database. Transactions access data using read and write operations.
In order to maintain consistency in a database, before and after transaction, certain properties are
followed. These are called ACID properties.
Atomicity
By this, 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 following two operations.
—Abort: If a transaction aborts, changes made to database are not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.

3 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

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)), then amount has been deducted from X but not added to Y. This results in an inconsistent
database state. Therefore, the transaction must be executed in entirety in order to ensure correctness of
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 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, 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
inconsistency of 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”.

4 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

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 correct value of X but 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 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 a 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 system failure occurs. These updates
now become permanent and are stored in a non-volatile memory. The effects of the transaction, thus, are
never lost.

3. SCHEDULES
1. Serial Schedules
Schedule 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.
Example: Consider the following schedule involving two transactions T1 and T2.

T1 T2

R(A)

W(A)

R(B)

W(B)

R(A)

R(B)

where R(A) denotes that a read operation is performed on some data item ‘A’
This is a serial schedule since the transactions perform serially in the order T1 —> T2

5 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

2. Complete Schedules
Schedules in which the last operation of each transaction is either abort (or) commit are called
Complete schedules.
Example: Consider the following schedule involving three transactions T1, T2 and T3.

T1 T2 T3

R(A)

W(A)

R(B)

W(B)

commit

commit

abort
This is a complete schedule since the last operation performed under every transaction is either
“commit” or “abort”.
3. Recoverable Schedules
Schedule in which transactions commit only after all transactions whose changes they read commit are
called recoverable schedules. In other words, if some transaction Tj is reading value updated or written by
some other transaction Ti, then the commit of Tj must occur after the commit of Ti.
Example – Consider the following schedule involving two transactions T1 and T2.

T1 T2

R(A)

W(A)

W(A)

R(A)

commit

commit

This is a recoverable schedule since T1 commits before T2, that makes the value read by T2 correct.

6 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

4. Cascadeless Schedules –
Also called Avoids cascading aborts/rollbacks (ACA). Schedules in which transactions read values
only after all transactions whose changes they are going to read commit are called cascadeless schedules.
Avoids that a single transaction abort leads to a series of transaction rollbacks. A strategy to prevent
cascading aborts is to disallow a transaction from reading uncommitted changes from another transaction
in the same schedule.
Example: Consider the following schedule involving two transactions T1 and T2.
T1 T2

R(A)

W(A)

W(A)

commit

R(A)

commit
This schedule is cascadeless. Since the updated value of A is read by T2 only after the updating
transaction i.e. T1 commits.
5. Strict Schedules
A schedule is strict if for any two transactions Ti, Tj, if a write operation of Ti precedes a conflicting
operation of Tj (either read or write), then the commit or abort event of Ti also precedes that conflicting
operation of Tj.
In other words, Tj can read or write updated or written value of Ti only after Ti commits/aborts.
Example: Consider the following schedule involving two transactions T1 and T2.
T1 T2

R(A)

R(A)

W(A)

commit

W(A)

R(A)

commit
This is a strict schedule since T2 reads and writes A which is written by T1 only after the commit of T1.
7 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

Note – It can be seen that:

1. Cascadeless schedules are stricter than recoverable schedules or are a subset of recoverable schedules.
2. Strict schedules are stricter than cascadeless schedules or are a subset of cascadeless schedules.
3. Serial schedules satisfy constraints of all recoverable, cascadeless and strict schedules and hence is a
subset of strict schedules.
4. SERIALIZABILITY
When multiple transactions are running concurrently then there is a possibility that the database may be
left in an inconsistent state. Serializability is a concept that helps us to check which schedules are
serializable. A serializable schedule is the one that always leaves the database in consistent state.
What is a serializable schedule?
A serializable schedule always leaves the database in consistent state. A serial schedule is always a
serializable schedule because in serial schedule, a transaction only starts when the other transaction finished
execution. However, a non-serial schedule needs to be checked for Serializability.
Types of Serializability
There are two types of Serializability.
I. Conflict Serializability
II. View Serializability
1. Conflict Serializability
Conflict Serializability is one of the type of Serializability, which can be used to check whether a non-
serial schedule is conflict serializable or not.
What is Conflict Serializability?
A schedule is called conflict serializable if we can convert it into a serial schedule after swapping its
non-conflicting operations.
Conflicting operations

Two operations are said to be in conflict, if they satisfy all the following three conditions:
1. Both the operations should belong to different transactions.
2. Both the operations are working on same data item.
3. At least one of the operations is a write operation.
Lets see some examples to understand this:
Example 1: Operation W(X) of transaction T1 and operation R(X) of transaction T2 are conflicting
operations, because they satisfy all the three conditions mentioned above. They belong to different
transactions, they are working on same data item X, one of the operation in write operation.
Example 2: Similarly Operations W(X) of T1 and W(X) of T2 are conflicting operations.
Example 3: Operations W(X) of T1 and W(Y) of T2 are non-conflicting operations because both the write
operations are not working on same data item so these operations don’t satisfy the second condition.
Example 4: Similarly R(X) of T1 and R(X) of T2 are non-conflicting operations because none of them is
write operation.

8 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Example 5: Similarly W(X) of T1 and R(X) of T1 are non-conflicting operations because both the
operations belong to same transaction T1.
Conflict Equivalent Schedules
Two schedules are said to be conflict Equivalent if one schedule can be converted into other schedule
after swapping non-conflicting operations.
Conflict Serializable check
Lets check whether a schedule is conflict serializable or not. If a schedule is conflict Equivalent to its
serial schedule then it is called Conflict Serializable schedule. Lets take few examples of schedules.
Example of Conflict Serializability
Lets consider this schedule:
T1 T2
----- ------
R(A)
R(B)
R(A)
R(B)
W(B)
W(A)
To convert this schedule into a serial schedule we must have to swap the R(A) operation of transaction
T2 with the W(A) operation of transaction T1. However, we cannot swap these two operations because they
are conflicting operations, thus we can say that this given schedule is not Conflict Serializable.
Lets take another example:
T1 T2
----- ------
R(A)
R(A)
R(B)
W(B)
R(B)
W(A)
Lets swap non-conflicting operations:
9 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

After swapping R(A) of T1 and R(A) of T2 we get:

T1 T2
----- ------
R(A)
R(A)
R(B)
W(B)
R(B)
W(A)
After swapping R(A) of T1 and R(B) of T2 we get:
T1 T2
----- ------
R(A)
R(B)
R(A)
W(B)
R(B)
W(A)
After swapping R(A) of T1 and W(B) of T2 we get:
T1 T2
----- ------
R(A)
R(B)
W(B)
R(A)
R(B)
W(A)
We finally got a serial schedule after swapping all the non-conflicting operations so we can say that the
given schedule is Conflict Serializable.
10 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

2. View Serializability
View Serializability is a process to find out that a given schedule is view serializable or not.
To check whether a given schedule is view serializable, we need to check whether the given schedule
is View Equivalent to its serial schedule. Lets take an example to understand what I mean by that.
Given Schedule:
T1 T2
----- ------
R(X)
W(X)
R(X)
W(X)
R(Y)
W(Y)
R(Y)
W(Y)
Serial Schedule of the above given schedule:
As we know that in Serial schedule a transaction only starts when the current running transaction is
finished. So the serial schedule of the above given schedule would look like this:
T1 T2
----- ------
R(X)
W(X)
R(Y)
W(Y)
R(X)
W(X)
R(Y)
W(Y)
If we can prove that the given schedule is View Equivalent to its serial schedule then the given schedule is
called view Serializable.
View Serializability
11 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

o A schedule will be view serializable if it is view equivalent to a serial schedule.

o If a schedule is conflict serializable, then it will be view serializable.

o The view serializable which does not conflict serializable contains blind writes.

View Equivalent

Two schedules S1 and S2 are said to be view equivalent if they satisfy the following conditions:
1. Initial Read

An initial read of both schedules must be the same. Suppose two schedule S1 and S2. In schedule S1, if a
transaction T1 is reading the data item A, then in S2, transaction T1 should also read A.

Above two schedules are view equivalent because Initial read operation in S1 is done by T1 and in S2 it is also
done by T1.
2. Updated Read
In schedule S1, if Ti is reading A which is updated by Tj then in S2 also, Ti should read A which is
updated by Tj.

Above two schedules are not view equal because, in S1, T3 is reading A updated by T2 and in S2, T3 is reading
A updated by T1.
3. Final Write
A final write must be the same between both the schedules. In schedule S1, if a transaction T1 updates
A at last then in S2, final writes operations should also be done by T1.

12 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Above two schedules is view equal because Final write operation in S1 is done by T3 and in S2, the
final write operation is also done by T3.

Example:

Schedule S
With 3 transactions, the total number of possible schedule
3! = 6
1. S1 = <T1 T2 T3>
2. S2 = <T1 T3 T2>
3. S3 = <T2 T3 T1>
4. S4 = <T2 T1 T3>
5. S5 = <T3 T1 T2>
6. S6 = <T3 T2 T1>
Taking first schedule S1:

Schedule S1
Step 1: final updation on data items
In both schedules S and S1, there is no read except the initial read that's why we don't need to check that
condition.
Step 2: Initial Read
13 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

The initial read operation in S is done by T1 and in S1, it is also done by T1.
Step 3: Final Write
The final write operation in S is done by T3 and in S1, it is also done by T3. So, S and S1 are view Equivalent.
The first schedule S1 satisfies all three conditions, so we don't need to check another schedule.
Hence, view equivalent serial schedule is: T1 → T2 → T3

TESTING FOR SERIALIZABILITY

Precedence graph
A precedence graph, also known as serialization graph or conflict graph, is used for testing Conflict
Serializability of a schedule in the condition that forms the setting of concurrency control in databases.
It is also known as a directed Graph G = (V, E), which consists of a pair of a set of nodes V = {V1, V2, V3,
..., Vn} and a set of directed edges E = {E1, E2, E3, ..., Em}. Where the set of nodes V are testing to retrieve
identical data attribute through the transactions of a schedule and the set of edges E is regulated connectivity
between a set of two nodes.
Nodes: In the graph, for each transaction Tp the graph contains a single node. So, In a schedule of a precedence
graph, The total number of transactions will be similar to the total number of nodes.
Edges: An edge is regulated connectivity between a set of two distinct transactions Tq and Tr and it shows in
the format Tq –>Tr, where Tq is the beginning of the edge and Tr is the ending.
Find the following Schedule S is conflict Serializable or not?

14 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Solution:
Let's make a precedence graph,

In the above precedence graph, by following accordingly to the Algorithm, Transaction Tp implements
reads A before Transaction Tq implements writes A, therefore the first arrow directed from
Transaction Tp towards Transaction Tq and Transaction Tq reads B before Transaction Tp writes B, therefore
the second arrow directed from Transaction Tq towards Transaction Tp.

Since from the above precedence graph it's clearly visible that the graph is cyclic, therefore the schedule S is
not conflicted Serializable.

Q2) Find the following Schedule S is conflict Serializable or not?

15 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

Solution:
Let's make a precedence graph,

In the above precedence graph, by following accordingly to the Algorithm, Transaction Tp implements
reads A before Transaction Tq implements writes A, therefore the first arrow directed.
from Transaction Tp towards Transaction Tq and Transaction Tq reads B before Transaction Tr writes B,
therefore the second arrow directed from Transaction Tq towards Transaction Tr and then the
Transaction Tp reads C before Transaction Tr writes C, therefore the third arrow directed from
Transaction Tp towards Tr.
From the above precedence graph it's clearly visible that the graph is acyclic, therefore the schedule S is conflict
Serializable.
CONCURRENCY CONTROL
In concurrency control, multiple transactions can be executed simultaneously. It may affect the
transaction result. It is highly important to maintain the order of execution of those transactions.
5. NEED FOR CONCURRENCY
Problems of concurrency control
Several problems can occur when concurrent transactions are executed in an uncontrolled manner.
Following are the three problems in concurrency control.

• Lost updates

• Dirty read

• Unrepeatable read

16 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

1. Lost update problem

When two transactions that access the same database items contain their operations in a way that
makes the value of some database item incorrect, then the lost update problem occurs.
If two transactions T1 and T2 read a record and then update it, then the effect of updating of the first
record will be overwritten by the second update.
Example:

Here,
o At time t2, transaction-X reads A's value.
o At time t3, Transaction-Y reads A's value.
o At time t4, Transactions-X writes A's value on the basis of the value seen at time t2.
o At time t5, Transactions-Y writes A's value on the basis of the value seen at time t3.
o So at time T5, the update of Transaction-X is lost because Transaction y overwrites it without
looking at its current value.
o Such type of problem is known as Lost Update Problem as update made by one transaction is
lost here.
2. Dirty Read
o The dirty read occurs in the case when one transaction updates an item of the database, and
then the transaction fails for some reason. The updated database item is accessed by another
transaction before it is changed back to the original value.
o A transaction T1 updates a record which is read by T2. If T1 aborts then T2 now has values
which have never formed part of the stable database.
Example:

17 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

o At time t2, transaction-Y writes A's value.

o At time t3, Transaction-X reads A's value.
o At time t4, Transactions-Y rollbacks. So, it changes A's value back to that of prior to t1.
o So, Transaction-X now contains a value which has never become part of the stable database.
o Such type of problem is known as Dirty Read Problem, as one transaction reads a dirty value
which has not been committed.
3. Inconsistent Retrievals Problem
o Inconsistent Retrievals Problem is also known as unrepeatable read. When a transaction
calculates some summary function over a set of data while the other transactions are updating the data,
then the Inconsistent Retrievals Problem occurs.
o A transaction T1 reads a record and then does some other processing during which the
transaction T2 updates the record. Now when the transaction T1 reads the record, then the new value
will be inconsistent with the previous value.
Example:
Suppose two transactions operate on three accounts.

18 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

o Transaction-X is doing the sum of all balance while transaction-Y is transferring an amount 50
from Account-1 to Account-3.
o Here, transaction-X produces the result of 550 which is incorrect. If we write this produced
result in the database, the database will become an inconsistent state because the actual sum is 600.
o Here, transaction-X has seen an inconsistent state of the database.
Concurrency Control Protocol
Concurrency control protocols ensure atomicity, isolation, and serializability of concurrent
transactions. The concurrency control protocol can be divided into three categories:
1. Lock based protocol
2. Time-stamp protocol

6. LOCKING PROTOCOLS
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:
o It is also known as a Read-only lock. In a shared lock, the data item can only read by the
transaction.
o 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:
o In the exclusive lock, the data item can be both reads as well as written by the transaction.
o This lock is exclusive, and in this lock, multiple transactions do not modify the same data
simultaneously.

7. TWO PHASE LOCKING (2PL)

The two-phase locking protocol divides the execution phase of the transaction into three parts.
o In the first part, when the execution of the transaction starts, it seeks permission for the lock it
requires.
o In the second part, the transaction acquires all the locks. The third phase is started as soon as
the transaction releases its first lock.
o In the third phase, the transaction cannot demand any new locks. It only releases the acquired
locks.

19 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

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.
In the below example, if lock conversion is allowed then the following phase can happen:
1. Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.
2. Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.
Example:

The following way shows how unlocking and locking work with 2-PL.
Transaction T1:
o Growing phase: from step 1-3
o Shrinking phase: from step 5-7
o Lock point: at 3
Transaction T2:
o Growing phase: from step 2-6
o Shrinking phase: from step 8-9
o Lock point: at 6
20 Prepared By: Mrs. E. Ajitha (AP/CSE)
 CS8492-Database Management Systems

Types of Two-Phase Locking (2PL)

1. Strict Two-phase locking (Strict-2PL)
o 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.
o The only difference between 2PL and strict 2PL is that Strict-2PL does not release a lock after
using it.
o Strict-2PL waits until the whole transaction to commit, and then it releases all the locks at a
time.
o Strict-2PL protocol does not have shrinking phase of lock release.

It does not have cascading abort as 2PL does.

2. Rigorous Two-Phase Locking
• Rigorous Two – Phase Locking Protocol avoids cascading rollbacks.
• This protocol requires that all the share and exclusive locks to be held until the transaction
commits.
Timestamp Ordering Protocol
o The Timestamp Ordering Protocol is used to order the transactions based on their Timestamps.
The order of transaction is nothing but the ascending order of the transaction creation.
o The priority of the older transaction is higher that's why it executes first. To determine the
timestamp of the transaction, this protocol uses system time or logical counter.
o The lock-based protocol is used to manage the order between conflicting pairs among
transactions at the execution time. But Timestamp based protocols start working as soon as a
transaction is created.
o Let's assume there are two transactions T1 and T2. Suppose the transaction T1 has entered the
system at 007 times and transaction T2 has entered the system at 009 times. T1 has the higher priority,
so it executes first as it is entered the system first.
o The timestamp ordering protocol also maintains the timestamp of last 'read' and 'write'
operation on a data.
Basic Timestamp ordering protocol works as follows:
1. Check the following condition whenever a transaction Ti issues a Read (X) operation:

21 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

o If W_TS(X) >TS(Ti) then the operation is rejected.

o If W_TS(X) <= TS(Ti) then the operation is executed.
o Timestamps of all the data items are updated.
2. Check the following condition whenever a transaction Ti issues a Write(X) operation:
o If TS(Ti) < R_TS(X) then the operation is rejected.
o If TS(Ti) < W_TS(X) then the operation is rejected and Ti is rolled back otherwise the
operation is executed.
Where,
TS(TI) denotes the timestamp of the transaction Ti.
R_TS(X) denotes the Read time-stamp of data-item X.
W_TS(X) denotes the Write time-stamp of data-item X.
Thomas write Rule
Thomas Write Rule provides the guarantee of serializability order for the protocol. It improves the
Basic Timestamp Ordering Algorithm.
The basic Thomas write rules are as follows:
o If TS(T) < R_TS(X) then transaction T is aborted and rolled back, and operation is rejected.
o If TS(T) < W_TS(X) then don't execute the W_item(X) operation of the transaction and
continue processing.
o If neither condition 1 nor condition 2 occurs, then allowed to execute the WRITE operation by
transaction Ti and set W_TS(X) to TS(T).
8. DEADLOCK
A deadlock is a condition wherein two or more tasks are waiting for each other in order to be finished
but none of the task is willing to give up the resources that other task needs. In this situation no task ever gets
finished and is in waiting state forever.
Coffman conditions
Coffman stated four conditions for a deadlock occurrence. A deadlock may occur if all the following
conditions holds true.
• Mutual exclusion condition: There must be at least one resource that cannot be used by more than one
process at a time.
• Hold and wait condition: A process that is holding a resource can request for additional resources that
are being held by other processes in the system.
• No preemption condition: A resource cannot be forcibly taken from a process. Only the process can
release a resource that is being held by it.
• Circular wait condition: A condition where one process is waiting for a resource that is being held by
second process and second process is waiting for third process ….so on and the last process is waiting
for the first process. Thus, making a circular chain of waiting.

22 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

For example: In the student table, transaction T1 holds a lock on some rows and needs to update some
rows in the grade table. Simultaneously, transaction T2 holds locks on some rows in the grade table and needs
to update the rows in the Student table held by Transaction T1.

Now, the main problem arises. Now Transaction T1 is waiting for T2 to release its lock and similarly,
transaction T2 is waiting for T1 to release its lock. All activities come to a halt state and remain at a standstill.
It will remain in a standstill until the DBMS detects the deadlock and aborts one of the transactions.

Deadlock Avoidance
o When a database is stuck in a deadlock state, then it is better to avoid the database rather than aborting
or restating the database. This is a waste of time and resource.
o Deadlock avoidance mechanism is used to detect any deadlock situation in advance. A method like "wait
for graph" is used for detecting the deadlock situation but this method is suitable only for the smaller
database. For the larger database, deadlock prevention method can be used.

Deadlock Detection

In a database, when a transaction waits indefinitely to obtain a lock, then the DBMS should detect
whether the transaction is involved in a deadlock or not. The lock manager maintains a Wait for the graph to
detect the deadlock cycle in the database.
Wait for Graph

o This is the suitable method for deadlock detection. In this method, a graph is created based on the
transaction and their lock. If the created graph has a cycle or closed loop, then there is a deadlock.
o The wait for the graph is maintained by the system for every transaction which is waiting for some data
held by the others. The system keeps checking the graph if there is any cycle in the graph.

23 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

The wait for a graph for the above scenario is shown below:

Deadlock Prevention
o Deadlock prevention method is suitable for a large database. If the resources are allocated in such a way
that deadlock never occurs, then the deadlock can be prevented.
o The Database management system analyzes the operations of the transaction whether they can create a
deadlock situation or not. If they do, then the DBMS never allowed that transaction to be executed.

I. Wait-Die scheme

In this scheme, if a transaction requests for a resource which is already held with a conflicting lock by
another transaction, then the DBMS simply checks the timestamp of both transactions. It allows the older
transaction to wait until the resource is available for execution.
Let's assume there are two transactions Ti and Tj and let TS(T) is a timestamp of any transaction T. If
T2 holds a lock by some other transaction and T1 is requesting for resources held by T2 then the following
actions are performed by DBMS:
1. Check if TS(Ti) < TS(Tj) - If Ti is the older transaction and Tj has held some resource, then Ti is
allowed to wait until the data-item is available for execution. That means if the older transaction is
waiting for a resource which is locked by the younger transaction, then the older transaction is allowed
to wait for resource until it is available.
2. Check if TS(Ti) < TS(Tj) - If Ti is older transaction and has held some resource and if Tj is waiting
for it, then Tj is killed and restarted later with the random delay but with the same timestamp.

24 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

II. Wound wait scheme

o In wound wait scheme, if the older transaction requests for a resource which is held by the younger
transaction, then older transaction forces younger one to kill the transaction and release the resource.
After the minute delay, the younger transaction is restarted but with the same timestamp.

o If the older transaction has held a resource which is requested by the Younger transaction, then the
younger transaction is asked to wait until older releases it.

Here is the table representation of resource allocation for each algorithm. Both of these
algorithms take process age into consideration while determining the best possible way of resource
allocation for deadlock avoidance.

Deadlock Prevention Wait/Die Wound/Wait

Older process needs a resource held by younger process Older process waits Younger process dies

Younger process needs a resource held by older process Younger process dies Younger process waits

o Once of the famous deadlock avoidance algorithm is Banker’s algorithm

25 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

9. TRANSACTION RECOVERY

26 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

27 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

28 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

29 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

30 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

31 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

32 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

33 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

34 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

35 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

36 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

37 Prepared By: Mrs. E. Ajitha (AP/CSE)

 CS8492-Database Management Systems

38 Prepared By: Mrs. E. Ajitha (AP/CSE)