DBMS COMPLETE UNIT - 4

For better understanding watch this youtube playlist along with the topics covered
in this written material--

[Link]

Transaction
● The transaction is a set of logically related operations. It contains a group of tasks.

● A transaction is an action or series of actions. It is performed by a single user to

perform operations for accessing the contents of the database.

Example: Suppose an employee of a bank transfers Rs 800 from X's account to Y's

account. This small transaction contains several low-level tasks:

X's Account

1. Open_Account(X)

2. Old_Balance = [Link]

3. New_Balance = Old_Balance - 800

4. [Link] = New_Balance

5. Close_Account(X)

Y's Account

1. Open_Account(Y)

2. Old_Balance = [Link]

3. New_Balance = Old_Balance + 800

4. [Link] = New_Balance

5. Close_Account(Y)

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);
Let's assume the value of X before starting the transaction is 4000.
● The first operation reads X's value from the database and stores it in a buffer.
● The second operation will decrease the value of X by 500. So the buffer will contain

3500.

● The third operation will write the buffer's value to the database. So X's final value will

be 3500.

But it may be possible that because of the failure of hardware, software or power, etc. that
transaction may fail before finishing all the operations in the set.
For example: If in the above transaction, the debit transaction fails after executing operation
2 then X's value will remain 4000 in the database which is not acceptable by the bank.
To solve this problem, we have two important operations:

Commit: It is used to save the work done permanently.

Rollback: It is used to undo the work done.

Transaction property
The transaction has the four properties. These are used to maintain consistency in a database,
before and after the transaction.

Property of Transaction
1. Atomicity
2. Consistency
3. Isolation
4. Durability
Atomicity
● It states that all operations of the transaction take place at once if not, the transaction

is aborted.

● There is no midway, i.e., the transaction cannot occur partially. Each transaction is

treated as one unit and either run to completion or is not executed at all.

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.
Example: Let's assume that following transaction T consisting of T1 and T2. A consists of
Rs 600 and B consists of Rs 300. Transfer Rs 100 from account A to account B.

T1 T2

Read(A) Read(B)

A:= A-100 Write(A) Y:= Y+100 Write(B)

After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.

If the transaction T fails after the completion of transaction T1 but before completion of
transaction T2, then the amount will be deducted from A but not added to B. This shows the
inconsistent database state. In order to ensure correctness of database state, the transaction
must be executed in entirety.

Consistency
● The integrity constraints are maintained so that the database is consistent before and

after the transaction.

● The execution of a transaction will leave a database in either its prior stable state or a

new stable state.

● The consistent property of the database states that every transaction sees a consistent

database instance.

● The transaction is used to transform the database from one consistent state to another

consistent state.

For example: The total amount must be maintained before or after the transaction.
1. Total before T occurs = 600+300=900
2. Total after T occurs= 500+400=900
Therefore, the database is consistent. In the case when T1 is completed but T2 fails, then
inconsistency will occur.
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.

Durability
● The durability property is used to indicate the performance of the database's consistent

state. It states that the transaction made the permanent changes.

● They cannot be lost by the erroneous operation of a faulty transaction or by the system

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 Transaction
In a database, the transaction can be in one of the following states -
Active state
● The active state is the first state of every transaction. In this state, the transaction is

being executed.

● For example: Insertion or deletion or updating a record is done here. But all the

records are still not saved to the database.

Partially committed
● In the partially committed state, a transaction executes its final operation, but the data

is still not saved to the database.

● In the total mark calculation example, a final display of the total marks step is

executed in this state.

Committed
A transaction is said to be in a committed state if it executes all its operations successfully. In

this state, all the effects are now permanently saved on the database system.

Failed state
● If any of the checks made by the database recovery system fails, then the transaction is

said to be in the failed state.

● In the example of total mark calculation, if the database is not able to fire a query to

fetch the marks, then the transaction will fail to execute.

Aborted
● If any of the checks fail and the transaction has reached a failed state then the database

recovery system will make sure that the database is in its previous consistent state. If

not then it will abort or roll back the transaction to bring the database into a consistent

state.

● If the transaction fails in the middle of the transaction then before executing the

transaction, all the executed transactions are rolled back to its consistent state.
 ● After aborting the transaction, the database recovery module will select one of the two

operations:

1. Re-start the transaction

2. Kill the transaction

Schedule
A series of operations 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 transactions.

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:

1. Execute all the operations of T1 which was followed by all the operations of
T2.
2. Execute all the operations of T1 which was followed by all the operations of T2.

● In the given (a) figure, Schedule A shows the serial schedule where T1 followed by

T2.

● In the given (b) figure, Schedule B shows the serial schedule where T2 followed by

T1.
2. Non-serial Schedule
● If interleaving of operations is allowed, then there will be a non-serial schedule.

● It contains many possible orders in which the system can execute the individual

operations of the transactions.

● In the given figure (c) and (d), Schedule C and Schedule D are the non-serial

schedules. It has interleaving operations.

The Non-Serial Schedule can be divided further into Serializable and Non-Serializable.

a) Serializable:
This is used to maintain the consistency of the database. It is mainly used in the Non-Serial
scheduling to verify whether the scheduling will lead to any inconsistency or not. On the other
hand, a serial schedule does not need the serializability because it follows a transaction only
when the previous transaction is complete. The non-serial schedule is said to be in a serializable
schedule only when it is equivalent to the serial schedules, for an n number of transactions.
Since concurrency is allowed in this case thus, multiple transactions can execute concurrently.
A serializable schedule helps in improving both resource utilization and CPU throughput. These
are of two types:

1. Conflict Serializable:
A schedule is called conflict serializable if it can be transformed into a serial schedule by
swapping non-conflicting operations. Two operations are said to be conflicting if all conditions
satisfy:
● They belong to different transactions
● They operate on the same data item ● At Least one of them is a
write operation

2. View Serializable:
A Schedule is called view serializable if it is view equal to a serial schedule (no
overlapping transactions). A conflict schedule is a view serializable but if the
serializability contains blind writes, then the view
serializable does not conflict serializable.

b) Non-Serializable:
The non-serializable schedule is divided into two types, Recoverable and
Non-recoverable Schedule.
1. 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

T1 and T 2 .
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.
There can be three types of recoverable schedule:

a) 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:

b) 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 Tj wants to read value updated or written by some other

transaction Ti, then the commit of T j must read it after the commit of Ti.

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.

Example: Consider the following schedule involving two transactions T1 and

T2.
T1 T2

R(A)

W(A)

W(A)

R(A)
 ABORT

ABORT

It is a recoverable schedule but it does not avoid cascading aborts. It can be seen that if T1

aborts, T2 will have to be aborted too in order to maintain the correctness of the schedule as T

2 has already read the uncommitted value

written by T1.

c) Strict Schedule:
A schedule is strict if for any two transactions Ti, T j, 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 T 1 only after the

commit of T1.

2. Non-Recoverable Schedule:
Example: Consider the following schedule involving two transactions T1 and
T2.

T1 T2

R(A)

W(A)

W(A)

R(A)

COMMIT

ABORT

T2 read the value of A written by T 1, and committed. T 1 later aborted, therefore the

value read by T2 is wrong, but since T2 committed, this schedule is non-recoverable.

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.
The relation between various types of schedules can be depicted as:
DBMS Concurrency Control
Concurrency Control is the management procedure that is required for controlling concurrent
execution of the operations that take place on a database.
But before knowing about concurrency control, we should know about concurrent execution.

Concurrent Execution in DBMS

● 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.

 ● 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.

● The thing is that the 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. Thus, on making the concurrent execution

of the transaction operations, there occur several challenging problems that need to be

solved.

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. So, the following problems occur with the Concurrent Execution of
the operations:

Problem 1: Lost Update Problems (W - W Conflict)

The problem occurs when two different database transactions perform the read/write
operations on the same database items in an interleaved manner (i.e., concurrent execution)
that makes the values of the items incorrect hence making the database inconsistent.

For example:

Consider the below diagram where two transactions TX and T Y, are performed on the
same account A where the balance of account A is $300.
● At time t1, transaction TX reads the value of account A, i.e., $300 (only read).

● At time t2, transaction TX deducts $50 from account A that becomes $250 (only

deducted and not updated/written).

● Alternately, at time t3, transaction TY reads the value of account A that will be

$300 only because TX didn't update the value yet.

● At time t4, transaction TY adds $100 to account A that becomes $400 (only

added but not updated/write).

● At time t6, transaction TX writes the value of account A that will be updated as

$250 only, as TY didn't update the value yet.

 ● Similarly, at time t7, transaction TY writes the values of account A, so it will

write as done at time t4 that will be $400. It means the value written by TX is

lost, i.e., $250 is lost.

Hence data becomes incorrect, and the database sets to inconsistent.

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.

For example:

Consider two transactions TX and TY in the below diagram performing read/write

operations on account A where the available balance in account A is $300:

● At time t1, transaction TX reads the value of account A, i.e., $300.

● At time t2, transaction TX adds $50 to account A that becomes $350.
● At time t3, transaction TX writes the updated value in account A, i.e., $350.
● Then at time t4, transaction TY reads account A that will be read as $350.
 ● Then at time t5, transaction TX rollbacks due to server problem, and the value

changes back to $300 (as initially).

● But the value for account A remains $350 for transaction TY as committed,

which is the dirty read and therefore known as the Dirty Read Problem.

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.

For example:

Consider two transactions, TX and TY, performing the read/write operations on account
A, having an available balance = $300. The diagram is shown below:

● At time t1, transaction TX reads the value from account A, i.e., $300.
● At time t2, transaction TY reads the value from account A, i.e., $300.
● At time t3, transaction TY updates the value of account A by adding $100 to the

available balance, and then it becomes $400.

● At time t4, transaction TY writes the updated value, i.e., $400.

 ● After that, at time t5, transaction TX reads the available value of account A,

and that will be read as $400.

● It means that within the same transaction TX, it reads two different values of

account A, i.e., $ 300 initially, and after updation made by transaction TY, it

reads $400. It is an unrepeatable read and is therefore known as the

Unrepeatable read problem.

Thus, in order to maintain consistency in the database and avoid such problems that take
place in concurrent execution, management is needed, and that is where the concept of
Concurrency Control comes into play.
Concurrency Control
Concurrency Control is the working concept that is required for controlling and managing the
concurrent execution of database operations and thus avoiding the inconsistencies in the
database. Thus, for maintaining the concurrency of the database, we have the concurrency
control protocols.

Concurrency Control Protocols

The concurrency control protocols ensure the atomicity, consistency, isolation, durability
and serializability of the concurrent execution of the database transactions. Therefore,
these protocols are categorized as:
● Lock Based Concurrency Control Protocol
● Timestamp Concurrency Control Protocol
● Multi version concurrency control
● Validation Based / Optimistic Concurrency Control Protocol
We will understand and discuss each protocol one by one in our next sections.

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:

● 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 read as well as written by the transaction.

● This lock is exclusive, and in this lock, multiple transactions do not modify the same

data simultaneously.

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

● 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.
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 the growing phase.
2. Downgrading of lock (from X(a) to S(a)) must be done in the shrinking phase.
Example:

The following way shows how unlocking and locking work with 2-PL.
Transaction T1:
● Growing phase: from step 1-3
● Shrinking phase: from step 5-7
● Lock point: at 3
Transaction T2:
● Growing phase: from step 2-6
● Shrinking phase: from step 8-9
● Lock point: at 6
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 a shrinking phase of lock release.

It does not have cascading abort as 2PL does.

Timestamp Ordering Protocol

● 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.

● 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.
 ● 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.

● 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.

● The timestamp ordering protocol also maintains the timestamp of the 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:

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

● If W_TS(X) <= TS(Ti) then the operation is executed.

● Timestamps of all the data items are updated.

2. Check the following condition whenever a transaction Ti issues a Write(X) operation:

● If TS(Ti) < R_TS(X) then the operation is rejected.

● 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.

Advantages and Disadvantages of TO protocol:

 ● TO protocol ensures serializability since the precedence graph is as follows:

● TS protocol ensures freedom from deadlock that means no transaction ever waits.

● But the schedule may not be recoverable and may not even be cascade- free.

Multiversion Concurrency Control:

Multiversion schemes keep old versions of data items to increase concurrency.

Multiversion 2 phase locking:

Each successful write results in the creation of a new version of the data item written.
Timestamps are used to label the versions. When a read(X) operation is issued, select an
appropriate version of X based on the timestamp of the transaction.

To improve database performance, multiversion concurrency control protocols are developed

to
extend the basic single version protocols. For single version databases, we have Two-phase
Locking, Timestamp Ordering and Optimistic Concurrency Control. Consequently. For
multiversion databases, there are Multiversion Two-phase Locking (MV2PL), Multiversion
Timestamp Ordering (MVTSO), and Multi Version Optimistic Concurrency Control.

The basic idea behind multiversion concurrency control is to maintain one or more old versions
(values) of data item when the item is updated.
When a transaction requires access to an item, an appropriate version is chosen to maintain the
serializability of the currently executing schedule, if possible. The idea is that some read
operations that would be rejected in other techniques can still be accepted by reading an older
version of the item to maintain serializability. When a transaction writes an item, it writes a
new
version and the old version of the item is retained.

Disadvantages: The drawback of multi version techniques is that more storage is needed to

maintain multiple versions of the database items.

Multiversion Technique Based On Timestamp Ordering

In this method, several versions of each data item X are maintained. For each version, the
value
of version and the following two timestamps are kept:

1. read_TS: The read timestamp of is the largest of all the timestamps of transactions that
have successfully read versions .

2. write_TS: The write timestamp of is the timestamp of the transaction that wrote the value
of version .

Whenever a transaction T is allowed to execute a write_item(X) operation, a new version of

item
X is created, with both the write_TS and the read_TS set to TS(T). Correspondingly, when a
transaction T is allowed to read the value of version Xi, the value of read_TS() is set to max(
read_TS() , TS(T)).

To ensure serializability, the following two rules are used:

1. If transaction T issues a write_item(X) operation, and version i of X has the highest

write_TS()
of all versions of X that is also less than or equal to TS(T), and read_TS() > TS(T), then abort
and
roll back transaction T; otherwise, create a new version of X with read_TS() = write_TS() =
TS(T).
2. If transaction T issues a read_item(X) operation, find the version i of X that has the
highest
write_TS() of all versions of X that is also less than or equal to TS(T); then return the value
of to
transaction T, and set the value of read_TS() to the larger of TS(T) and the current read_TS().

As we can see in case 2, a read_item(X) is always successful, since it finds the appropriate
version to read based on the write_TS of the various existing versions of X. In case 1,
however,
transaction T may be aborted and rolled back. This happens if T is attempting to write a
version
of X that should have been read by another transaction T whose timestamp is read_TS();
however, T has already read version Xi, which was written by the transaction with timestamp
equal to write_TS(). If this conflict occurs, T is rolled back; otherwise, a new version of X,
written
by transaction T, is created.

Validation Based Protocol

Validation phase is also known as optimistic concurrency control technique. In the validation
based protocol, the transaction is executed in the following three phases:

The optimistic approach is based on the assumption that the majority of the database operations
do not conflict. The optimistic approach requires neither locking nor time stamping techniques.
Instead, a transaction is executed without restrictions until it is committed. Using an optimistic
approach, each transaction moves through 2 or 3 phases, referred to as read, validation and
write.

(i) During the read phase, the transaction reads the database, executes the needed
computations and makes the updates to a private copy of the database values. All update
operations of the transactions are recorded in a temporary update file, which is not accessed
by the remaining transactions.
 (ii) During the validation phase, the transaction is validated to ensure that the
changes made will not affect the integrity and consistency of the database. If the validation
test is positive, the transaction goes to a write
phase. If the validation test is negative, the transaction is restarted and the changes are
discarded.
(iii) During the write phase, the changes are permanently applied to the database.

Here each phase has the following different timestamps:

Start(Ti): It contains the time when Ti started its execution.

Validation (Ti): It contains the time when Ti finishes its read phase and starts its validation
phase.

Finish(Ti): It contains the time when Ti finishes its write phase.

● This protocol is used to determine the time stamp for the transaction for serialization
using the timestamp of the validation phase, as it is the actual phase which determines

if the transaction will commit or rollback.

● Hence TS(T) = validation(T).

● The serializability is determined during the validation process. It can't be decided in

advance.

● While executing the transaction, it ensures a greater degree of concurrency and also less
number of conflicts.

● Thus it contains transactions which have less number of rollbacks.

Failure Classification
To find that where the problem has occurred, we generalize a failure into the following
categories:
1. Transaction failure
2. System crash
3. Disk failure
1. Transaction failure
The transaction failure occurs when it fails to execute or when it reaches a point from where

it can't go any further. If a few transactions or processes are hurt, then this is called

transaction failure.

Reasons for a transaction failure could be -

○ Logical errors: If a transaction cannot complete due to some code error or an internal

error condition, then the logical error occurs.

○ Syntax error: It occurs where the DBMS itself terminates an active transaction because

the database system is not able to execute it. For example, The system aborts an active

transaction, in case of deadlock or resource unavailability.

2. System Crash

○ System failure can occur due to power failure or other hardware or software failure.

Example: Operating system error.

Fail-stop assumption: In the system crash, non-volatile storage is assumed not to be

corrupted.

3. Disk Failure

○ It occurs where hard-disk drives or storage drives used to fail frequently. It was a common

problem in the early days of technology evolution.

○ Disk failure occurs due to the formation of bad sectors, disk head crash, and unreachability

to the disk or any other failure, which destroy all or part of disk storage.
DATABASE RECOVERY TECHNIQUES:

1) Log-Based Recovery
● The log is a sequence of records. Log of each transaction is maintained in some stable

storage so that if any failure occurs, then it can be recovered from there.

● If any operation is performed on the database, then it will be recorded in the log.

● But the process of storing the logs should be done before the actual transaction is applied

in the database.

Let's assume there is a transaction to modify the City of a student. The following logs are
written for this transaction.
● When the transaction is initiated, then it writes 'start' log.
<Tn, Start>
● When the transaction modifies the City from 'Noida' to 'Bangalore', then another log is

written to the file.

<Tn, City, 'Noida', 'Bangalore' >

● When the transaction is finished, then it writes another log to indicate the end of the

transaction.

<Tn, Commit>
There are two approaches to modify the database:

1. Deferred database modification:

Video Lecture: [Link]

● The deferred modification technique occurs if the transaction does not modify the

database until it has committed.

● In this method, all the logs are created and stored in the stable storage, and the

database is updated when a transaction commits.

 2. Immediate database modification:
Video Lecture: [Link]

● The Immediate modification technique occurs if database modification occurs while

the transaction is still active.

● In this technique, the database is modified immediately after every operation.

It follows an actual database modification.

2) Recovery using Log records

When the system is crashed, then the system consults the log to find which transactions need
to be undone and which need to be redone.

1. If the log contains the record <Ti, Start> and <Ti, Commit> or <Ti, Commit>, then the

Transaction Ti needs to be redone.

2. If log contains record<Tn, Start> but does not contain the record either <Ti,

commit> or <Ti, abort>, then the Transaction Ti needs to be undone.

3) Checkpoint
● The checkpoint is a type of mechanism where all the previous logs are removed from

the system and permanently stored in the storage disk.

● The checkpoint is like a bookmark. While the execution of the transaction, such

checkpoints are marked, and the transaction is executed then using the steps of the

transaction, the log files will be created.

 ● When it reaches the checkpoint, then the transaction will be updated into the

database, and till that point, the entire log file will be removed from the file. Then the

log file is updated with the new step of transaction till the next checkpoint and so on.

● The checkpoint is used to declare a point before which the DBMS was in the

consistent state, and all transactions were committed.

Recovery using Checkpoint

In the following manner, a recovery system recovers the database from this failure:

● The recovery system reads log files from the end to start. It reads log files from T4 to

T1.

● Recovery system maintains two lists, a redo-list, and an undo-list.

● The transaction is put into redo state if the recovery system sees a log with <Tn,

Start> and <Tn, Commit> or just <Tn, Commit>. In the redo-list and their previous

list, all the transactions are removed and then redone before saving their logs.

● For example: In the log file, transaction T2 and T3 will have <Tn, Start> and <Tn,

Commit>. The T1 transaction will have only <Tn, commit> in the log file. That's
 why the transaction is committed after the checkpoint is crossed. Hence it puts T1,

T2 and T3 transactions into the redo list.

● The transaction is put into undo state if the recovery system sees a log with <Tn,

Start> but no commit or abort log found. In the undo-list, all the transactions are

undone, and their logs are removed.

● For example: Transaction T4 will have <Tn, Start>. So T4 will be put into the undo

list since this transaction is not yet complete and failed amid.

4) Shadow Paging
Video Lecture: [Link]

Shadow Paging is a recovery technique that is used to recover databases. In this

recovery technique, a database is considered as made up of fixed size of logical units of storage

which are referred to as pages. pages are mapped into physical blocks of storage, with help of

the page table which allow one entry for each logical page of the database. This method uses

two page tables named current page table and shadow page table.

The entries which are present in the current page table are used to point to most recent database
pages on disk. Another table i.e., Shadow page table is used when the transaction starts which
is copying current page table. After this, the shadow page table gets saved on disk and the
current page table is going to be used for transaction. Entries present in the current page table
may be changed during execution but in the shadow page table it never gets changed. After the
transaction, both tables become identical.
This technique is also known as Cut-of-Place updating.
To understand the concept, consider the above figure. In this 2 write operations are performed
on page 3 and 5. Before the start of write operation on page 3, the current page table points to
old page 3. When write operation starts following steps are performed :

1. Firstly, search starts for available free blocks in disk blocks.

2. After finding a free block, it copies page 3 to the free block which is
represented by Page 3 (New).

3. The current page table points to Page 3 (New) on disk but the
shadow page table points to old page 3 because it is not modified.

4. The changes are now propagated to Page 3 (New) which is pointed

by the current page table.

COMMIT Operation :

To commit transaction following steps should be done :

 1. All the modifications which are done by transactions which are present in buffers
are transferred to the physical database.

2. Output current page table to disk.

3. Disk address of current page table output to fixed location which is in stable
storage containing address of shadow page table. This operation overwrites the
address of the old shadow page table. With this current page table becomes the
same as the shadow page table and transaction is committed.

Failure :

If a system crashes during execution of transaction but before commit operation, With this, it
is sufficient only to free modified database pages and discard current page table. Before
execution of transaction, state of database gets recovered by reinstalling shadow page table.
If the crash of the system occurs after last write operation then it does not affect propagation
of changes that are made by transaction. These changes are preserved and there is no need to
perform redo operation.

Advantages :
● This method requires fewer disk accesses to perform operation.
● In this method, recovery from a crash is inexpensive and quite fast.
● There is no need for operations like- Undo and Redo.
Disadvantages :
● Due to location change on disk due to updating databases it is quite difficult to
keep related pages in databases closer on disk.
● During commit operation, changed blocks are going to be pointed by the shadow
page table which have to be returned to collection of free
blocks otherwise they become accessible.
● The commit of a single transaction requires multiple blocks which decreases
execution speed.
● To allow this technique to multiple transactions concurrently it is
difficult.
For more database recovery please read these article also:

[Link]