Database Management System

B.Tech. II Year II Semester

UNIT - I
Database System Applications: A Historical Perspective, File Systems versus a DBMS, the Data
Model, Levels of Abstraction in a DBMS, Data Independence, Structure of a DBMS
Introduction to Database Design: Database Design and ER Diagrams, Entities, Attributes, and
Entity Sets, Relationships and Relationship Sets, Additional Features of the ER Model, Conceptual
Design with the ER Model
UNIT - II
Introduction to the Relational Model: Integrity constraint over relations, enforcing integrity
constraints, querying relational data, logical data base design, introduction to views,
destroying/altering tables and views.
Relational Algebra, Tuple relational Calculus, Domain relational calculus.
UNIT - III
SQL: QUERIES, CONSTRAINTS, TRIGGERS: form of basic SQL query, UNION, INTERSECT, and
EXCEPT, Nested Queries, aggregation operators, NULL values, complex integrity constraints in
SQL, triggers and active data bases.
Schema Refinement: Problems caused by redundancy, decompositions, problems related to
decomposition, reasoning about functional dependencies, FIRST, SECOND, THIRD normal forms,
BCNF, lossless join decomposition, multi-valued dependencies, FOURTH normal form, FIFTH
normal form.
UNIT - IV
Transaction Concept, Transaction State, Implementation of Atomicity and Durability, Concurrent
Executions, Serializability, Recoverability, Implementation of Isolation, Testing for serializability,
Lock Based Protocols, Timestamp Based Protocols, Validation- Based Protocols, Multiple
Granularity, Recovery and Atomicity, Log–Based Recovery, Recovery with Concurrent
Transactions.
UNIT - V
Data on External Storage, File Organization and Indexing, Cluster Indexes, Primary and Secondary
Indexes, Index data Structures, Hash Based Indexing, Tree base Indexing, Comparison of File
Organizations, Indexes and Performance Tuning, Intuitions for tree Indexes, Indexed Sequential
Access Methods (ISAM), B+ Trees: A Dynamic Index Structure.

1 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Introduction to Transaction Management
What is a Database Transaction?
A Database Transaction is a logical unit of processing in a DBMS which entails one or more database
access operation. All types of database access operation which are held between the beginning and end
transaction statements are considered as a single logical transaction in DBMS.
Facts about Database Transactions
• The transaction concept in DBMS is executed as a single unit.
• If the database operations do not update the database but only retrieve data, this type of
transaction is called a read-only transaction.
• DBMS transactions must be Atomic, Consistent, Isolated and Durable (ACID)
Operations of Transaction:
Following are the main operations of transaction:
Read(X): Read operation is used to read the value of X from the database and stores it in a buffer in main
memory.
Write(X): Write operation is used to write the value back to the database from the buffer.
Let's take an example to debit transaction from an account which consists of following operations:
1. R(X);
2. X = X - 500;
3. W(X);
In case of Transaction Failure, We have two important operations:
Commit: It is used to save the work done permanently.
Rollback: It is used to undo the work done.

2 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

What are ACID Properties?
ACID Properties are used for maintaining the integrity of database during transaction processing. ACID in
DBMS stands for Atomicity, Consistency, Isolation, and Durability.
1. ATOMICITY (Either all or none): A transaction is a single unit of operation. You either execute it
entirely or do not execute it at all. There cannot be partial execution. Atomicity involves the
following two operations:
ABORT: If a Transaction aborts then all the changes made are not visible.
COMMIT: If a Transaction commits then all the changes made are visible.
2. CONSISTENCY: This property states that every database must remain in a consistent state after
the transaction. If any transaction violates the consistency rules of the database, the whole
transaction will be rolled back. The integrity constraints are maintained so that the database is
consistent before and after the transaction. The consistent property of database states that
every transaction sees a consistent database instance.

3. ISOLATION
It shows that the data which is used at the time of execution of a transaction cannot be used by the
second transaction until the first one is completed.
In isolation, if the transaction T1 is being executed and using the data item X, then that data item can't
be accessed by any other transaction T2 until the transaction T1 ends.
The concurrency control subsystem of the DBMS enforced the isolation property.
3 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD
4. DURABILITY
This property states that, when all the operations of a transaction are completed successfully, then the
changes made by the transaction saved to the database should be permanent. These changes are never
lost if there occurs any kind of failure.. When a transaction is completed, then the database reaches a
state known as the consistent state. That consistent state cannot be lost, even in the event of a system's
failure.
The recovery subsystem of the DBMS has the responsibility of Durability property.

States of Transactions
The various states of a transaction concept in DBMS are listed below:

1. State - Transaction enters into an active state when the execution process begins. During this
state read or write operations can be performed.
2. Partially Committed - transaction goes into the partially committed state after the-end of a
transaction.
3. Committed State - When the transaction is committed to state, it has already completed its
execution successfully. Moreover, all of its changes are recorded to the database permanently.
4. Failed State - A transaction considers failed when any one of the checks fails or if the transaction
is aborted while in the active state.
5. Terminated State - State of transaction reaches terminated state when certain transactions
which are leaving the system can't be restarted

4 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Schedule
A series of operation from one transaction to another transaction is known as schedule. It is used to
preserve the order of the operation in each of the individual transaction. There are 3 types of schedules
1. Serial Schedule
2. Non-Serial Schedule
3. Serializable Schedule
1. Serial Schedule
The serial schedule is a type of schedule where one transaction is executed completely before starting
another transaction. In the serial schedule, when the first transaction completes its cycle, then the next
transaction is executed.
For example: Suppose there are two transactions T1 and T2 which have some operations. If it has no
interleaving of operations, then there are the following two possible outcomes:
Execute all the operations of T1 which was followed by all the operations of T2.
Execute all the operations of T1 which was followed by all the operations of T2.

2. Non-serial Schedule - If interleaving of operations is allowed, then there will be non-serial schedule. It
contains many possible orders in which the system can execute the individual operations of the
transactions.
3. Serializable schedule - The Serializability of schedules is used to find non-serial schedules that allow
the transaction to execute concurrently without interfering with one another. A non-serial schedule will
be Serializable if its result is equal to the result of its transactions executed serially.

5 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Testing of Serializability
Serialization Graph is used to test the Serializability of a schedule.
Assume a schedule S. For S, we construct a graph known as Precedence Graph. This graph has a pair G =
(V, E), where V consists a set of vertices, and E consists a set of edges. The set of vertices is used to
contain all the transactions participating in the schedule. The set of edges is used to contain all edges Ti -
>Tj for which one of the three conditions holds:
Create a node Ti → Tj , if Ti executes write (Q) before Tj executes read (Q).
Create a node Ti → Tj , if Ti executes read (Q) before Tj executes write (Q).
Create a node Ti → Tj , if Ti executes write (Q) before Tj executes write (Q).
If a precedence graph contains a single edge Ti → Tj, then all the instructions of Ti are executed before
the first instruction of Tj is executed.
If a precedence graph for schedule S contains a Cycle, then S is Non- Serializable. If the precedence
graph has no cycle, then S is known as Serializable.

6 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Serializable Precedence graph of Schedule S

In above precedence graph of schedule S, contains two vertices T1 and T2, and a single edge T1->T2,
because all the instructions of T1 are executed before the first instruction of T2 is executed.

Non-Serializable Precedence graph of Schedule S1

In above precedence graph of schedule S1, contains two vertices T1 and T2, and edges T1->T2 and
T2->T1.
The precedence graph contains a cycle, that’s why schedule S1 is non-serializable.
Types of Serializability

7 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Conflict Serializability in DBMS
A schedule is said to be conflict serializable if it can transform into a serial schedule after swapping of
non-conflicting operations. It is a type of serializability that can be used to check whether the non-serial
schedule is conflict serializable or not.
Conflicting operations
The two operations are called conflicting operations, if all the following three conditions are satisfied:
1. Both the operation belongs to separate transactions.
2. Both works on the same data item.
3. At least one of them contains one write operation.
Note: Conflict pairs for the same data item are:
Read-Write, Write-Write, Write-Read
Example:
Swapping is possible only if S1 and S2 are logically equal.

Here, S1 = S2. That means it is non-conflict Here, S1 ≠ S2. That means it is conflict.

Conflict Equivalent Schedule

Two schedules are called as a conflict equivalent schedule if one schedule can be transformed into
another schedule by swapping non-conflicting operations. In the given example, S2 is conflict equivalent
to S1 (S1 can be converted to S2 by swapping non-conflicting operations).
Two schedules are said to be conflict equivalent if and only if:
1. They contain the same set of the transaction.
2. If each pair of conflict operations are ordered in the same way.

8 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Schedule S2 is a serial schedule because, in this, all operations of T1 are performed before starting any
operation of T2. Schedule S1 can be transformed into a serial schedule by swapping non-conflicting
operations of S1.
After swapping of non-conflict operations, the schedule S1 becomes:

Example of Conflict Serializability:

Schedule S2 (Non-Serial Schedule):

Precedence graph for schedule S2:

9 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

In the above schedule, there are three transactions: T1, T2, and T3. So, the precedence graph contains
three vertices

To draw the edges between these nodes or vertices, follow the below steps:
Step1: At time t1, there is no conflicting operation for read(X) of Transaction T1.
Step2: At time t2, there is no conflicting operation for read(Y) of Transaction T3.
Step3: At time t3, there exists a conflicting operation Write(X) in transaction T1 for read(X) of
Transaction T3. So, draw an edge from T3?T1.
Step4: At time t4, there exists a conflicting operation Write(Y) in transaction T3 for read(Y) of
Transaction T2. So, draw an edge from T2?T3.
Step5: At time t5, there exists a conflicting operation Write (Z) in transaction T1 for read (Z) of
Transaction T2. So, draw an edge from T2?T1. Step6: At time t6, there is no conflicting operation
for Write(Y) of Transaction T3.
Step7: At time t7, there exists a conflicting operation Write (Z) in transaction T1 for Write (Z) of
Transaction T2. So, draw an edge from T2?T1, but it is already drawn.
After all the steps, the precedence graph will be ready, and it does not contain any cycle or loop, so the
above schedule S2 is conflict serializable.

10 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

View Serializability in DBMS

View Serializability in DBMS

1. A schedule will view serializable if it is view equivalent to a serial schedule.
2. If a schedule is conflict serializable, then it will be view serializable.
3. 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.

11 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

 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.

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.
Blind Write
Blind Write – It is write operation in which, if there is no read operation that happens prior to the first
write operation, then it is said to be Blind Write

Example:
12 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD
Schedule S
With 3 transactions, the total number of possible schedule
= 3! = 6
S1 = <T1 T2 T3>
S2 = <T1 T3 T2>
S3 = <T2 T3 T1>
S4 = <T2 T1 T3>
S5 = <T3 T1 T2>
S6 = <T3 T2 T1>
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
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
Schedule S taking first schedule S1:

13 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Concurrent Execution of Transaction
Advantages of concurrent execution of a transaction
1. Decrease waiting time or turnaround time.
2. Improve response time
3. Increased throughput or resource utilization.
Concurrency problems
Several problems can occur when concurrent transactions are run in an uncontrolled manner, such type
of problems is known as concurrency problems.
There are following different types of problems or conflicts which occur due to concurrent execution of
transaction
1. Read-Write Conflict (R-W) [Unrepeatable Problem]
2. Write-Read Conflict (W-R) [Dirty Read Problem]
3. Write-Write Conflict (W-W) [ Lost Update Problem]

14 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

1. Lost Update Problem (WRITE – WRITE CONFLICT)
This type of problem occurs when two transactions in database access the same data item and have their
operations in an interleaved manner that makes the value of some database item incorrect. If there are
two transactions T1 and T2 accessing the same data item value and then update it, then the second
record overwrites the first record.
Example: Let’s take the value of A is 100

Here,
1. At t1 time, T1 transaction reads the value of A i.e., 100.
2. At t2 time, T1 transaction deducts the value of A by 50.
3. At t3 time, T2 transactions read the value of A i.e., 100.
4. At t4 time, T2 transaction adds the value of A by 150.
5. At t5 time, T1 transaction writes the value of A data item on the basis of value seen at time t2
i.e., 50.
6. At t6 time, T2 transaction writes the value of A based on value seen at time t4 i.e., 150.
7. So at time T6, the update of Transaction T1 is lost because Transaction T2 overwrites the value of
A without looking at its current value.
Such type of problem is known as the Lost Update Problem
2. Dirty Read Problem (WRITE – READ CONFLICT)
This type of problem occurs when one transaction T1 updates a data item of the database, and then that
transaction fails due to some reason, but its updates are accessed by some other transaction.
Example: Let’s take the value of A is 100

15 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

 1. At t1 time, T1 transaction reads the value of A i.e., 100.
2. At t2 time, T1 transaction adds the value of A by 20.
3. At t3 time, T1transaction writes the value of A (120) in the database.
4. At t4 time, T2 transactions read the value of A data item i.e., 120.
5. At t5 time, T2 transaction adds the value of A data item by 30.
6. At t6 time, T2transaction writes the value of A (150) in the database.
7. At t7 time, a T1 transaction fails due to power failure then it is rollback according to atomicity
property of transaction (either all or none).
So, transaction T2 at t4 time contains a value which has not been committed in the database. The value
read by the transaction T2 is known as a dirty read.
3. Unrepeatable Read (R-W CONFLICT) or Inconsistent Retrievals Problem
It is also known as an inconsistent retrieval problem. If a transaction T 1 reads a value of data item twice
and the data item is changed by another transaction T 2 in between the two read operation. Hence
T1 access two different values for its two read operation of the same data item. Example: Let’s take the
value of A is 100

Here,
1. At t1 time, T1 transaction reads the value of A i.e., 100.
2. At t2 time, T2transaction reads the value of A i.e., 100.
3. At t3 time, T2 transaction adds the value of A data item by 30.
4. At t4 time, T2 transaction writes the value of A (130) in the database.
5. Transaction T2 updates the value of A. Thus, when another read statement is performed by
transaction T1, it accesses the new value of A, which was updated by T2. Such type of conflict is
known as R-W conflict.

16 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Recoverable schedule:
Schedules 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 T 1 and T2.
This is a recoverable schedule since T1 commits before T2, that makes the value read by T2 correct.

Non-Recoverable Schedule:
Example: Consider the following schedule involving two transactions T 1 and T2. T2 read the value of A
written by T1, and committed. T1 later aborted, therefore the value read by T2 is wrong, but since
T2 committed, this schedule is non-recoverable.

Three types of Recoverable Schedules

1. CASCADING SCHEDULE: Also called Avoids cascading aborts/rollbacks (ACA). When there is a
failure in one transaction and this leads to the rolling back or aborting other dependent
transactions, then such scheduling is referred to as Cascading Rollback or Cascading Abort.
Example

17 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

 2. CASCADELESS SCHEDULE 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. In other words, if some transaction T j wants to read
value updated or written by some other transaction Ti, then the commit of Tj must read it after the
commit of Ti.

Example: Consider the following schedule involving two transactions T 1 and T2. This schedule is
Cascadeless. Since the updated value of A is read by T2 only after the updating transaction i.e.
T1 commits.
3. STRICT SCHEDULE:
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 T i only after
Ti commits/aborts.

Example: Consider the following schedule involving two transactions T 1 and T2. This is a strict schedule
since T2 reads and writes A which is written by T1 only after the commit of T1.

18 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Log Based Recovery in DBMS
Atomicity property of DBMS states that either all the operations of transactions must be performed or
none.
ABORT - The modifications done by an aborted transaction should not be visible to database (Rollback)
COMMIT - the modifications done by committed transaction should be visible.
To achieve our goal of atomicity, user must first output to stable storage information describing the
modifications, without modifying the database itself.
LOG BASED RECOVERY is a concept used to recover from failure by logging the transactions in a log
before caching the updates in memory. In case the server crashes before the in-memory data is persisted
to a durable storage medium, then the transactions logged in a log file can be replayed to construct the
state of the data in memory which can then be persisted to a durable storage medium.
LOG & LOG RECORDS
The log is a sequence of log records, recording all the update activities in the database. In a stable
storage, logs for each transaction are maintained. Any operation which is performed on the database is
recorded is on the log. Prior to performing any modification to database, an update log record is created
to reflect that modification.
An update log record represented as: <Ti, Xj, V1, V2> has these fields:
Ti-> Transaction identifier: Unique Identifier of the transaction that performed the write operation.
Xj-> Data item: Unique identifier of the data item written.
V1-> Old value: Value of data item prior to write.
V2-> New value: Value of data item after write operation.
<Ti start>: It contains information about when a transaction Ti starts.
<Ti commit>: It contains information about when a transaction Ti commits.
<Ti abort>: It contains information about when a transaction Ti aborts.
Undo and Redo Operations
Because all database modifications must be preceded by creation of log record, the system has available
both the old value prior to modification of data item and new value that is to be written for data item.
This allows system to perform redo and undo operations as appropriate:
Undo: using a log record sets the data item specified in log record to old value.
Redo: using a log record sets the data item specified in log record to new value.

19 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

The database can be modified using two approaches –
Deferred Modification Technique: If the transaction does not modify the database until it has partially
committed, it is said to use Deferred Modification Technique.
Immediate Modification Technique: If database modification occurs while transaction is still active, it is
said to use Immediate Modification Technique.
Recovery using Log records
After a system crash has occurred, the system consults the log to determine which transactions need to
be redone and which need to be undone.
1. Transaction Ti needs to be Undone if the log contains the record <Ti start> but does not contain
either the record <Ti commit> or the record <Ti abort>.
2. Transaction Ti needs to be Redone if log contains record <Ti start> and either the record <Ti
commit> or the record <Ti abort>.

1. Deferred Modification Technique: If the transaction does not modify the database until it has
partially committed, it is said to use Deferred Modification Technique.

2. Immediate Modification Technique: If database modification occur while transaction is still active, it is
said to use Immediate Modification Technique.

20 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Use of Checkpoints
When a system crash occurs, user must consult the log. In principle, that need to search the entire log to
determine this information. There are two major difficulties with this approach:
1. The search process is time-consuming.
2. Most of the transactions that, according to our algorithm, need to be redone have already
written their updates into the database. Although redoing them will cause no harm, it will cause
recovery to take longer.
To reduce these types of overhead, user introduce Checkpoints. A log record of the form <checkpoint L>
is used to represent a checkpoint in log where L is a list of transactions active at the time of the
checkpoint.
After a system crash has occurred, the system examines the log to find the last
<checkpoint L> record

21 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Concurrency Control
Concurrency Control is the management procedure that is required for controlling concurrent execution
of the operations that take place on a database.
Concurrent Execution in DBMS
1. In a multi-user system, multiple users can access and use the same database at one time, which is
known as the concurrent execution of the database. It means that the same database is executed
simultaneously on a multi-user system by different users.
2. While working on the database transactions, there occurs the requirement of using the database
by multiple users for performing different operations, and in that case, concurrent execution of
the database is performed.
3. Simultaneous execution that is performed should be done in an interleaved manner, and no
operation should affect the other executing operations, thus maintaining the consistency of the
database.
Problems with Concurrent Execution
In a database transaction, the two main operations are READ and WRITE operations. So, there is a need
to manage these two operations in the concurrent execution of the transactions as if these operations
are not performed in an interleaved manner, and the data may become inconsistent.
Concurrent Execution of the operations:
1. Lost Update Problems (W - W Conflict) : The problem occurs when two different database
transactions perform the write/write operations on the same database items in an interleaved
manner that makes the values of the items incorrect hence making the database inconsistent.
2. Dirty Read Problems (W-R Conflict): The dirty read problem occurs when one transaction
updates an item of the database, and somehow the transaction fails, and before the data gets
rollback, the updated database item is accessed by another transaction. There comes the Read-
Write Conflict between both transactions.
3. Unrepeatable Read Problem (W-R Conflict): Also known as Inconsistent Retrievals Problem that
occurs when in a transaction, two different values are read for the same database item.

Concurrency Control Protocols - is for controlling and managing the concurrent execution of database
operations and thus avoiding the inconsistencies in the database. The concurrency control protocols

22 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

ensure the atomicity, consistency, isolation, durability and serializability of the concurrent execution of
the database transactions.
1. Lock Based Concurrency Control Protocol
2. Time Stamp Concurrency Control Protocol
3. Validation Based Concurrency Control Protocol
4. Multiple Granularity

Lock Based Concurrency Control 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:
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.

23 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

There are four types of lock protocols available:
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
1. Pre-claiming Lock Protocols evaluate the transaction to list all the data items on which they need
locks.
2. Before initiating an execution of the transaction, it requests DBMS for all the lock on all those
data items.
3. 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.
4. 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.
1. In the first part, when the execution of the transaction starts, it seeks permission for the lock it
requires.
2. In the second part, the transaction acquires all the locks. The third phase is started as soon as the
transaction releases its first lock.
3. In the third phase, the transaction cannot demand any new locks. It only releases the acquired
locks.

24 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Consistency of database should be maintained during transactions. To ensure consistency, the
transaction schedules must be serial schedules or serializable schedules. One way to ensure
Serializability is to access the required data items in a mutually exclusive manner. That is, while one
transaction is accessing a data item, the other transactions cannot modify that particular data item. This
can be achieved by using locks.
Two phase locking protocol (2PL) ensures Serializability using locks. According to this protocol, each
transaction must request lock on data items from lock manager and it happens in two phases as follows;

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:
Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.
Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.
Two phase locking protocol

25 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

What is LOCK POINT? The Point at which the growing phase ends, i.e., when a transaction takes the final
lock it needs to carry on its work
2-PL ensures Serializability, but there are still some drawbacks of 2-PL. Let’s glance at the drawbacks:
Cascading Rollback is possible under 2-PL.
Deadlocks and Starvation are possible.
Cascading Rollbacks in 2-PL – Let’s see the following Schedule

Deadlock in 2-PL –
Consider this simple example, it will be easy to understand. Say we have two transactions T 1 and T2.
Schedule: Lock-X1(A) Lock-X2(B) Lock-X1(B) Lock-X2(A)

26 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

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

2. Timestamp Ordering Concurrency Control Protocol

1. Timestamp based Protocol in DBMS is an algorithm which uses the System Time or Logical
Counter as a timestamp to serialize the execution of concurrent transactions.
2. The Timestamp-based protocol ensures that every conflicting read and write operations are
executed in a timestamp order.
3. The older transaction is always given priority in this method. It uses system time to determine the
time stamp of the transaction. This is the most commonly used concurrency protocol.
4. Lock-based protocols help you to manage the order between the conflicting transactions when
they will execute. Timestamp-based protocols manage conflicts as soon as an operation is
created.
Example:
Suppose there are there transactions T1, T2, and T3.
T1 has entered the system at time 0010
T2 has entered the system at 0020
T3 has entered the system at 0030

27 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Priority will be given to transaction T1, then transaction T2 and lastly Transaction T3.
Advantages:
• Schedules are serializable just like 2PL protocols
• No waiting for the transaction, which eliminates the possibility of deadlocks!
Disadvantages:
• Starvation is possible if the same transaction is restarted and continually aborted
• But the schedule may not be cascade free, and may not even be recoverable.

Basic Timestamp ordering protocol works as follows:

1. Check the following condition whenever a transaction Ti issues a Read (X) operation:
a. If W_TS(X) >TS(Ti) then the operation is Rollback Ti.
b. Otherwise execute Read(X) operation.
and set R_TS(X)=MAX { R_TS(X), TS(Ti) }
Timestamps of all the data items are updated.
2. Check the following condition whenever a transaction Ti issues a Write(X) operation:
a. If R_TS(X) > TS(Ti) then the operation is Rollback Ti.
b. If W_TS(X) > TS(Ti) then the operation is rejected and Ti is rolled back
c. Otherwise execute Write(X) operation and set W_TS(X)=TS(Ti).
Where,
TS(Ti) denotes the timestamp of the transaction Ti.
R_TS(X) denotes the Read time-stamp of data-item X. (Last Transaction)
W_TS(X) denotes the Write time-stamp of data-item X. (Last Transaction)

28 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

3. Validation Based Protocol
1. Validation based Protocol in DBMS also known as Optimistic Concurrency Control Technique is a
method to avoid concurrency in transactions.
2. In this protocol, the local copies of the transaction data are updated rather than the data itself,
which results in less interference while execution of the transaction.
The Validation based Protocol is performed in the following three phases:
1. Read phase: In this phase, the transaction T is read and executed. It is used to read the value of
various data items and stores them in temporary local variables. It can perform all the write
operations on temporary variables without an update to the actual database.
2. Validation phase: In this phase, the temporary variable value will be validated against the actual
data to see if it violates the serializability.
3. Write phase: If the validation of the transaction is validated, then the temporary results are
written to the database or system otherwise the transaction is rolled back.
4. Here each phase has the following different Time Stamps:
5. Start(Ti): It contains the time when Ti started its execution.
6. Validation (Ti): It contains the time when Ti finishes its read phase and starts its validation phase.
7. Finish(Ti): It contains the time when Ti finishes its write phase.
8. This protocol is used to determine the time stamp for the transaction for serialization using the
time stamp of the validation phase, as it is the actual phase which determines if the transaction
will commit or rollback.
9. Hence TS(T) = validation(T).
10. The serializability is determined during the validation process. It can't be decided in advance.
11. While executing the transaction, it ensures a greater degree of concurrency and also less number
of conflicts.
12. Thus it contains transactions which have less number of rollbacks.
Transaction Ti Validation Test (Three conditions):

1. If Finish(Ti)<Start(Tj) execute operation

2. If Finish(Ti)<Validate(Tj)
3. If Validate(Ti)<Validate(Tj)

If any one of the condition is true, then validation is pass. So, so final write into database will be done.

29 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Multiple Granularities

Granularity: It is the size of data item allowed to lock.

Multiple Granularities:
1. It can be defined as hierarchically breaking up the database into blocks which can be locked.
2. The Multiple Granularity protocol enhances concurrency and reduces lock overhead.
3. It maintains the track of what to lock and how to lock.
4. It makes easy to decide either to lock a data item or to unlock a data item.
5. This type of hierarchy can be graphically represented as a tree.
There are three additional lock modes with multiple granularity:
Intention Mode Lock
1. Intention-shared (IS): It contains explicit locking at a lower level of the tree but only with shared
locks.
2. Intention-Exclusive (IX): It contains explicit locking at a lower level with exclusive or shared locks.
3. Shared & Intention-Exclusive (SIX): In this lock, the node is locked in shared mode, and some
node is locked in exclusive mode by the same transaction.
Compatibility Matrix with Intention Lock Modes:

For
example: Consider a tree which has four levels of nodes.
• The first level or higher level shows the entire database.
• The second level represents a node of type area. The higher level database consists of exactly
these areas.
• The area consists of children nodes which are known as files. No file can be present in more than
one area.
• Finally, each file contains child nodes known as records. The file has exactly those records that
are its child nodes. No records represent in more than one file.
• Hence, the levels of the tree starting from the top level are as follows:

30 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD

Database, Area, File, Record

Observe that in multiple-granularity, the locks are acquired in top-down order, and locks must be
released in bottom-up order.
1. If transaction T1 reads record Ra9 in file Fa, then transaction T1 needs to lock the database, area
A1 and file Fa in IX mode. Finally, it needs to lock Ra2 in S mode.
2. If transaction T2 modifies record Ra9 in file Fa, then it can do so after locking the database, area
A1 and file Fa in IX mode. Finally, it needs to lock the Ra9 in X mode.
3. If transaction T3 reads all the records in file Fa, then transaction T3 needs to lock the database,
and area A in IS mode. At last, it needs to lock Fa in S mode.
4. If transaction T4 reads the entire database, then T4 needs to lock the database in S mode.

31 Prepared by Dr. P. Sammulal, Professor, Department of CSE, JNTUH-HYD