Database Management System
SE SH 2025
Ms. Ashwini Khillari
Assistant Professor
CSE-IOT,
SIES Graduate School of Technology
1
Prof. Ashwini Khillari
Module 5 Transaction Management
A transaction can be defined as a group of tasks. A single task is the minimum processing unit
which cannot be divided further.
ACID Properties
A transaction is a very small unit of a program and it may contain several low level tasks. A
transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability −
commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.
2
Prof. Ashwini Khillari
Example: Consider the following example of transaction operations to be performed to
withdraw cash from an ATM.
Steps for ATM Transaction
1. Transaction Start.
2. Insert your ATM card.
3. Select a language for your transaction.
4. Select the Savings Account option.
5. Enter the amount you want to withdraw.
6. Enter your secret pin.
7. Wait for some time for processing.
8. Collect your Cash.
9. Transaction C
A transaction can include the following basic database access operation.
● Read/Access data (R): Accessing the database item from disk (where the database
stored data) to memory variable.
● Write/Change data (W): Write the data item from the memory variable to the disk.
● Commit: Commit is a transaction control language that is used to permanently save
the changes done in a transactionompleted
3
Prof. Ashwini Khillari
Transaction States
Transactions can be implemented using SQL queries and Servers. In the below-given diagram,
you can see how transaction states work.
4
Prof. Ashwini Khillari
Active State:
When the operations of a transaction are running then the transaction is said to be in an active state. If all the read
and write operations are performed without any error then it progresses to the partially committed state, if somehow
any operation fails, then it goes to a state known as
failed state.
Partially Committed:
After all the read and write operations are completed, the changes which were previously made in the main memory
are now made permanent in the database, after which the state will progress to committed state but in case of a
failure it will go to the failed state.
Failed State:
If any operation during the transaction fails due to some software or hardware issues, then it goes to the failed state .
The occurrence of a failure during a transaction makes a permanent change to data in the database. The changes
made into the local memory data are rolled back to the previous consistent state.
Aborted State:
If the transaction fails during its execution, it goes from failed state to aborted state and because in the previous
states all the changes were only made in the main memory, these uncommitted changes are either deleted or rolled
back. The transaction at this point can restart and start afresh
from the active state.
Committed State:
If the transaction completes all sets of operations successfully, all the changes made during the partially committed
state are permanently stored and the transaction is stated to be completed, thus the transaction can progress to
finally get terminated in the terminated state.
Terminated State:
If the transaction gets aborted after roll-back or the transaction comes from the committed state, then the database
comes to a consistent state and is ready for further new transactions since the previous transaction is now
terminated.
5
Prof. Ashwini Khillari
ACID Properties /Transaction Properties in DBMS
6
Prof. Ashwini Khillari
Atomicity: Transaction Manager
By this, we mean that either the entire transaction takes place at once or doesn’t happen at all.
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.
7
Prof. Ashwini Khillari
Consistency: Application Programmer
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: Concurrency Control Manager
This property ensures that multiple transactions can occur concurrently without leading to the inconsistency
of the database state. Transactions occur independently without interference.
Durability: Recover Manager
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.
8
Prof. Ashwini Khillari
Serializability in DBMS:
Serializability is a concept in DBMS that ensures concurrent transaction execution results in a consistent database,
just like some serial (one-by-one) execution.
Non-serial Schedule
A non-serial schedule allows transactions to run concurrently and may access the same data.
To ensure database consistency, it must be serializable, meaning it should produce the same result as some serial
(one-by-one) execution. Transaction-1 Transaction-2
R(a)
W(a)
R(b)
We can observe that Transaction-2 begins its execution before
Transaction-1 is finished, and they are both working on the
same data, i.e., "a" and "b", interchangeably. W(b)
Where "R"-Read, "W"-Write
R(b)
R(a)
W(b)
W(a) 9
Prof. Ashwini Khillari
Types of Serializability
There are two ways to check whether any non-serial schedule is serializable.
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 operation is a write operation.
10
Prof. Ashwini Khillari
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.
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.
11
Prof. Ashwini Khillari
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:
By swapping the 4 and 5 instructions which are
non-conflicting, we can get:
T1 T2 T1 T2
read A; read A;
write A; write A;
read A;
read A;
write A; read B;
read B; write A;
write B; write B;
read B; read B;
write B; write B;
12
Prof. Ashwini Khillari
By swapping the 5 and 6 instructions which are non-conflicting, we can get schedule-6 (T1, T2) :
T1 T2 This is
read A; equivalent to
schedule-1.
write A;
read B;
write B;
read A;
write A;
read B;
write B;
13
Prof. Ashwini Khillari
Deadlock in DBMS:
A deadlock occurs in a multi-user database environment when two or more transactions block each other
indefinitely by each holding a resource the other needs. This results in a cycle of dependencies (circular
wait) where no transaction can proceed.
For Example: Consider the image below
In the above image, we can see
that:
T1 locks Resource "Student" and
needs Resource "Grade“
T2 locks Resource "Grade" and
needs Resource "Student“
T1 waits for T2, T2 waits for T1,
hence resulting in a deadlock
14
Prof. Ashwini Khillari
Necessary Conditions of Deadlock
For a deadlock to occur, all four of these conditions must be true:
Mutual Exclusion: Only one transaction can hold a particular resource at a time.
Hold and Wait: The Transactions holding resources may request additional resources held by others.
No Preemption: The Resources cannot be forcibly taken from the transaction holding them.
Circular Wait: A cycle of transactions exists where each transaction is waiting for the resource held by the next
transaction in the cycle.
Real-Life Example
Transaction T1:
Locks rows in Students
Wants to update rows in Grades
Transaction T2:
Locks rows in Grades
Wants to update rows in Students
Both wait on each other and this results in deadlock, and all database activity comes to a standstill.
15
Prof. Ashwini Khillari
Deadlock Detection
1. If Resources Have a Single Instance
In this case for Deadlock detection, we can run an algorithm to check for the cycle in the Resource Allocation
Graph. The presence of a cycle in the graph is a sufficient condition for deadlock.
In the above diagram, resource 1 and resource 2 have single instances. There is a cycle R1 → P1 → R2 → P2. So,
Deadlock is Confirmed.
2. Wait-For Graph Algorithm
The Wait-For Graph Algorithm is a deadlock detection algorithm used to detect deadlocks in a system where
resources can have multiple instances. The algorithm works by constructing a Wait-For Graph, which is a directed
graph that represents the dependencies between processes and resources.
16
Prof. Ashwini Khillari
Example 1:
Consider a Resource Allocation Graph with 4 Processes P1, P2, P3, P4, and 4 Resources R1, R2, R3, R4. Find
if there is a deadlock in the Graph using the Wait for Graph-based deadlock detection algorithm.
First take Process P1 which is waiting for Resource R1, resource R1 is acquired by Process P2, Start a Wait-for-
Graph for the above Resource Allocation Graph.
17
Prof. Ashwini Khillari
Now we can observe that there is a path from P1 to P2 as P1 is waiting for R1 which is been acquired by P2.
Now the Graph would be after removing resource R1 looks like.
From P2 we can observe a path from P2 to P3 as P2 is
waiting for R4 which is acquired by P3. So make a path from
P2 to P3 after removing resource R4 looks like.
18
Prof. Ashwini Khillari
From P3 we find a path to P4 as it is waiting for P3 which is acquired by P4. After removing R3 the graph looks
like this.
Here we can find Process P4 is waiting for R2 which is
acquired by P1. So finally the Wait-for-Graph is as follows:
Note: Finally In this Graph, we found a cycle as the
Process P4 again came back to the Process P1 which
is the starting point (i.e., it's a closed-loop). So,
According to the Algorithm if we found a closed loop,
then the system is in a deadlock state. So here we can
say the system is in a deadlock state.
19
Prof. Ashwini Khillari
Example 2:
Now consider another Resource Allocation Graph with 4 Processes P1, P2, P3, P4, and 3 Resources R1, R2, R3.
Find if there is a deadlock in the Graph using the Wait for Graph-based deadlock detection algorithm.
20
Prof. Ashwini Khillari
Recovery from Deadlock:
When a detection algorithm detects that there is a deadlock, the system must be able to recover from
the deadlock.
The most common solution to recover from the dead-lock situation is to roll back one or more
transactions and break the deadlock.
Here, three actions are needed to be taken :
(a)Selection of a Victim :
Given a set of deadlocked transactions, one must determine which transaction has to be rolled back to
break the deadlock. Then, that transaction should be rolled back which occurs at minimum cost.
Many other factors also play a role in deciding which transactions need to be rolled back
(b)Rollback :
Once it is decided that a particular transaction must be rolled back, then determine how far this
transaction should be rolled back.
The simplest solution is ‘Total Rollback’. That is, we abort the transaction and then restart it.
Sometimes, it is possible to do ‘partial rollback’, which retains the consistency of the database.
‘Partial Rollback’ requires the system to maintain additional information about the state of all the
running transactions.
(c)Starvation:
In a system where the selection of a transaction is based primarily on cost factors, then it is possible to
pick the same transaction to be rolled back, known as ‘victim’. As a result, the transaction never
completes its designated task, thus there is a ‘starvation’. So, here one should decide that a selected
transaction be rolled back for a finite number of times only. Then ‘starvation’ does not occur.
21
Prof. Ashwini Khillari
Thank You!
(ashwinik@[Link])
22
Prof. Ashwini Khillari