DBMS - Transaction

A transaction can be defined as a group of tasks. A single task is

the minimum processing unit which cannot be divided further.

o The transaction is a set of logically related operation. It contains a

group of tasks.
o 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.

Let’s take an example of a simple transaction. Suppose a bank

employee transfers Rs 500 from A's account to B's account. This
very simple and small transaction involves several low-level tasks.

A’s Account

Open_Account(A)
Old_Balance = [Link]
New_Balance = Old_Balance - 500
[Link] = New_Balance
Close_Account(A)

B’s Account

Open_Account(B)
Old_Balance = [Link]
New_Balance = Old_Balance + 500
[Link] = New_Balance
Close_Account(B)

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. 1. R(X);
2. 2. X = X - 500;
3. 3. W(X);

Let's assume the value of X before starting of the transaction is 4000.

o The first operation reads X's value from database and stores it in a buffer.
o The second operation will decrease the value of X by 500. So buffer will
contain 3500.
o 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 finished 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
o It states that all operations of the transaction take place at once if not, the
transaction is aborted.
o 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.
 Consistency
o The integrity constraints are maintained so that the database is consistent
before and after the transaction.
o The execution of a transaction will leave a database in either its prior
stable state or a new stable state.
o The consistent property of database states that every transaction sees a
consistent database instance.
o 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
o 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.
o 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.
o The concurrency control subsystem of the DBMS enforced the isolation
property.

Durability
o The durability property is used to indicate the performance of the
database's consistent state. It states that the transaction made the
permanent changes.
o 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.
o 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
o The active state is the first state of every transaction. In this state, the
transaction is being executed.
o 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
o In the partially committed state, a transaction executes its final operation,
but the data is still not saved to the database.
o 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
o If any of the checks made by the database recovery system fails, then the
transaction is said to be in the failed state.
o 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
o 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.
o 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.
o 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 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.

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.

o In the given (a) figure, Schedule A shows the serial schedule where T1
followed by T2.
o In the given (b) figure, Schedule B shows the serial schedule where T2
followed by T1.

2. Non-serial Schedule
o If interleaving of operations is allowed, then there will be non-serial
schedule.
o It contains many possible orders in which the system can execute the
individual operations of the transactions.
o In the given figure (c) and (d), Schedule C and Schedule D are the non-
serial schedules. It has interleaving of operations.

3. Serializable schedule
o The serializability of schedules is used to find non-serial schedules that
allow the transaction to execute concurrently without interfering with one
another.
o It identifies which schedules are correct when executions of the
transaction have interleaving of their operations.
o A non-serial schedule will be serializable if its result is equal to the result
of its transactions executed serially.
Here,

Schedule A and Schedule B are serial schedule.

Schedule C and Schedule D are Non-serial schedule.

Conflict Serializable Schedule

o A schedule is called conflict serializability if after swapping of non-
conflicting operations, it can transform into a serial schedule.
 o The schedule will be a conflict serializable if it is conflict equivalent to a
serial schedule.

Conflicting Operations
The two operations become conflicting if all conditions satisfy:

1. Both belong to separate transactions.

2. They have the same data item.
3. They contain at least one write operation.

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
In the conflict equivalent, one can be transformed to another 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.

Example:

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:

T1 T2

Read(A)
Write(A)
Read(B)
Write(B)
Read(A)
Write(A)
Read(B)
Write(B)

Since, S1 is conflict serializable.

View Serializability
o A schedule will view serializable if it is view equivalent to a serial schedule.
o If a schedule is conflict serializable, then it will be view serializable.
o The view serializable which does not conflict serializable contains blind
writes.

View Equivalent
Two schedules S1 and S2 are said to be view equivalent if they satisfy the
following conditions:

1. Initial Read
An initial read of both schedules must be the same. Suppose two schedule
S1 and S2. In schedule S1, if a transaction T1 is reading the data item A,
then in S2, transaction T1 should also read A.
Above two schedules are view equivalent because Initial read operation in
S1 is done by T1 and in S2 it is also done by T1.

2. Updated Read
In schedule S1, if Ti is reading A which is updated by Tj then in S2 also, Ti
should read A which is updated by Tj.

Above two schedules are not view equal because, in S1, T3 is reading A
updated by T2 and in S2, T3 is reading A updated by T1.

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

Example:

Schedule S

With 3 transactions, the total number of possible schedule

1. = 3! = 6
2. S1 = <T1 T2 T3>
3. S2 = <T1 T3 T2>
4. S3 = <T2 T3 T1>
5. S4 = <T2 T1 T3>
6. S5 = <T3 T1 T2>
7. S6 = <T3 T2 T1>

Taking first schedule S1:

 Schedule S1

Step 1: final updation on data items

In both schedules S and S1, there is no read except the initial read that's
why we don't need to check that condition.

Step 2: Initial Read

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:

1. T1 → T2 → T3

Recoverability of Schedule
Sometimes a transaction may not execute completely due to a software
issue, system crash or hardware failure. In that case, the failed
transaction has to be rollback. But some other transaction may also have
used value produced by the failed transaction. So we also have to rollback
those transactions.
The above table 1 shows a schedule which has two transactions. T1 reads
and writes the value of A and that value is read and written by T2. T2
commits but later on, T1 fails. Due to the failure, we have to rollback T1.
T2 should also be rollback because it reads the value written by T1, but T2
can't be rollback because it already committed. So this type of schedule is
known as irrecoverable schedule.

Irrecoverable schedule: The schedule will be irrecoverable if Tj reads

the updated value of Ti and Tj committed before Ti commit.

The above table 2 shows a schedule with two transactions. Transaction T1

reads and writes A, and that value is read and written by transaction T2.
But later on, T1 fails. Due to this, we have to rollback T1. T2 should be
rollback because T2 has read the value written by T1. As it has not
committed before T1 commits so we can rollback transaction T2 as well.
So it is recoverable with cascade rollback.
Recoverable with cascading rollback: The schedule will be
recoverable with cascading rollback if Tj reads the updated value of Ti.
Commit of Tj is delayed till commit of Ti.

The above Table 3 shows a schedule with two transactions. Transaction T1

reads and write A and commits, and that value is read and written by T2.
So this is a cascade less recoverable schedule.

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
transaction or process is hurt, then this is called as transaction
failure.

Reasons for a transaction failure could be -

1. Logical errors: If a transaction cannot complete due to some code

error or an internal error condition, then the logical error occurs.
2. 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
o 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
o 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.
o 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.

Log-Based Recovery
o 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.
o If any operation is performed on the database, then it will be recorded in
the log.
o 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.

o When the transaction is initiated, then it writes 'start' log.

1. <Tn, Start>
o When the transaction modifies the City from 'Noida' to 'Bangalore', then
another log is written to the file.

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

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

1. <Tn, Commit>

There are two approaches to modify the database:

1. Deferred database modification:
o The deferred modification technique occurs if the transaction does not
modify the database until it has committed.
o 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:

o The Immediate modification technique occurs if database modification
occurs while the transaction is still active.
o In this technique, the database is modified immediately after every
operation. It follows an actual database modification.

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.

Checkpoint
o The checkpoint is a type of mechanism where all the previous logs are
removed from the system and permanently stored in the storage disk.
o 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.
o When it reaches to 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 next checkpoint and so on.
o 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:
 o The recovery system reads log files from the end to start. It reads log files
from T4 to T1.
o Recovery system maintains two lists, a redo-list, and an undo-list.
o 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.
o 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 transaction into redo list.
o 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.
o For example: Transaction T4 will have <Tn, Start>. So T4 will be put into
undo list since this transaction is not yet complete and failed amid.

Deadlock in DBMS
A deadlock is a condition where two or more transactions are waiting
indefinitely for one another to give up locks. Deadlock is said to be one of
the most feared complications in DBMS as no task ever gets finished and
is in waiting state forever.

For example: In the student table, transaction T1 holds a lock on some

rows and needs to update some rows in the grade table. Simultaneously,
transaction T2 holds locks on some rows in the grade table and needs to
update the rows in the Student table held by Transaction T1.

Now, the main problem arises. Now Transaction T1 is waiting for T2 to

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

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

Deadlock Detection
In a database, when a transaction waits indefinitely to obtain a lock, then
the DBMS should detect whether the transaction is involved in a deadlock
or not. The lock manager maintains a Wait for the graph to detect the
deadlock cycle in the database.
Wait for Graph
o This is the suitable method for deadlock detection. In this method, a graph
is created based on the transaction and their lock. If the created graph has
a cycle or closed loop, then there is a deadlock.
o The wait for the graph is maintained by the system for every transaction
which is waiting for some data held by the others. The system keeps
checking the graph if there is any cycle in the graph.

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

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

Wait-Die scheme
In this scheme, if a transaction requests for a resource which is already
held with a conflicting lock by another transaction then the DBMS simply
checks the timestamp of both transactions. It allows the older transaction
to wait until the resource is available for execution.

Let's assume there are two transactions Ti and Tj and let TS(T) is a
timestamp of any transaction T. If T2 holds a lock by some other
transaction and T1 is requesting for resources held by T2 then the
following actions are performed by DBMS:

1. Check if TS(Ti) < TS(Tj) - If Ti is the older transaction and Tj has held some
resource, then Ti is allowed to wait until the data-item is available for
execution. That means if the older transaction is waiting for a resource
which is locked by the younger transaction, then the older transaction is
allowed to wait for resource until it is available.
2. Check if TS(Ti) < TS(Tj) - If Ti is older transaction and has held some
resource and if Tj is waiting for it, then Tj is killed and restarted later with
the random delay but with the same timestamp.

Wound wait scheme

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