Unit IV

Transaction Management
A Transaction is a set of logically related operations. For example, you are transferring
money from your bank account to your friend’s account, the set of operations would be like
this:

Simple Transaction Example

1. Read your account balance
2. Deduct the amount from your balance
3. Write the remaining balance to your account
4. Read your friend’s account balance
5. Add the amount to his account balance
6. Write the new updated balance to his account

This whole set of operations can be called a transaction. Although I have shown you read,
write and update operations in the above example but the transaction can have operations
like read, write, insert, update, delete.

In DBMS, we write the above 6 steps transaction like this:

Lets say your account is A and your friend’s account is B, you are transferring 10000 from A
to B, the steps of the transaction are:

1. R(A);
2. A = A - 10000;
3. W(A);
4. R(B);
5. B = B + 10000;
6. W(B);

In the above transaction R refers to the Read operation and W refers to the write operation.
Transaction failure in between the operations

Now that we understand what is transaction, we should understand what are the problems
associated with it.

The main problem can happen during a transaction is the transaction can fail before
finishing the all the operations in the set. This can happen due to power failure, system crash etc.
This is a serious problem that can leave database in an inconsistent state. Assume that transaction
fail after third operation (see the example above) then the amount would be deducted from your
account but your friend will not receive it.

Database Management Systems

School of Computing
Department Computer Science and Engineering
To solve this problem, we have the following two operations

Commit: If all the operations in a transaction are completed successfully then commit those
changes to the database permanently.
Rollback: If any of the operation fails then rollback all the changes done by previous operations.

Even though these operations can help us avoiding several issues that may arise during transaction
but they are not sufficient when two transactions are running concurrently. To handle those
problems, we need to understand database ACID properties.

ACID Properties in DBMS

A transaction is a single logical unit of work which accesses and possibly modifies the
contents of a database. Transactions access data using read and write operations.
In order to maintain consistency in a database, before and after the transaction, certain
properties are followed. These are called ACID properties.

Database Management Systems

School of Computing
Department Computer Science and Engineering
Atomicity
The entire transaction takes place at once or doesn’t happen at all. There is no midway
i.e. transactions do not occur partially. Each transaction is considered as one unit and either
runs to completion or is not executed at all. It involves the following two operations.

Abort: If a transaction aborts, changes made to database are not visible.

Commit: If a transaction commits, changes made are visible.

Atomicity is also known as the ‘All or nothing rule’.

Consider the following transaction T consisting of T1 and T2:

Transfer of 100 Rupees from account X to account Y.

If the transaction fails after completion of T1 but before completion of T2.(

say, after write(X) but before write(Y)), then amount has been deducted from X but not
added to Y. This results in an inconsistent database state. Therefore, the transaction must be
executed in entirety in order to ensure correctness of database state.

Consistency
This means that integrity constraints must be maintained so that the database is consistent
before and after the transaction. It refers to the correctness of a database. Referring to the
example above, The total amount before and after the transaction must be maintained.

Total before T occurs = 500 + 200 = 700.

Total after T occurs = 400 + 300 = 700.

Therefore, database is consistent.

Inconsistency occurs in case T1 completes but T2 fails. As a result, T is incomplete.

Database Management Systems

School of Computing
Department Computer Science and Engineering
Isolation

This property ensures that multiple transactions can occur concurrently without leading
to the inconsistency of database state. Transactions occur independently without interference.
Changes occurring in a particular transaction will not be visible to any other transaction until
that particular change in that transaction is written to memory or has been committed. This
property ensures that the execution of transactions concurrently will result in a state that is
equivalent to a state achieved these were executed serially in some order.

Let X= 500, Y = 500.

Consider two transactions T and T”.

T T’’
Read(X) Read(X)
X:= X+100 Read (Y)
Write (X) Z:= X+Y
Read (Y) Write (Z)
Y:= Y-50
Write (Y)

Suppose T has been executed till Read (Y) and then T’’ starts. As a result, interleaving of
operations takes place due to which T’’ reads correct value of X but incorrect value of Y and
sum computed by

T’’: (500, 500, 500+500, 1000)

is thus not consistent with the sum at end of transaction:

T: (500+100 = 600, 600-50 = 550, 550).

This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take
place in isolation and changes should be visible only after they have been made to the main
memory.

Durability:
This property ensures that once the transaction has completed execution, the updates and
modifications to the database are stored in and written to disk and they persist even if a system
failure occurs. These updates now become permanent and are stored in non-volatile memory.
The effects of the transaction, thus, are never lost.

Database Management Systems

School of Computing
Department Computer Science and Engineering
 The ACID properties provide a mechanism to ensure correctness and consistency of a
database in a way such that each transaction is a group of operations that acts a single unit,
produces consistent results, acts in isolation from other operations and updates that it makes are
durably stored.

DBMS Transaction States

Every transaction undergoes several states in its execution.A transaction can be in any one of the
following states:

1. start

2. partially committed

3. committed

4. failed

5. aborted or terminate

Active State
This is the first state of transaction and here the transaction is being executed. For example,
updating or inserting or deleting a record is done here. But it is still not saved to the database.
When we say transaction it will have set of small steps, and those steps will be executed here.

Database Management Systems

School of Computing
Department Computer Science and Engineering
 Partially Committed State
This is also an execution phase where last step in the transaction is executed. But data is
still not saved to the database. In example of calculating total marks, final display the total marks
step is executed in this state.
Committed State
In this state, all the transactions are permanently saved to the database. This step is the last
step of a transaction, if it executes without fail.
Fai l ed S tate
If a transaction cannot proceed to the execution state because of the failure of the system
or database, then the transaction is said to be in failed state. In the total mark calculation example,
if the database is not able fire a query to fetch the marks, i.e.; very first step of transaction, then
the transaction will fail to execute.
Aborted State
If a transaction fails during execution then the transaction goes into a failed state.
The changes made into the local memory (or buffer) are rolled back to the previous
consistent state and the transaction goes into aborted state from the failed state. Refer the
diagram to see the interaction between failed and aborted state.

Types of Schedules in DBMS

Schedule, as the name suggests, is a process of lining the transactions and executing them
one by one. When there are multiple transactions that are running in a concurrent manner and the
order of operation is needed to be set so that the operations do not overlap each other, Scheduling
is brought into play and the transactions are timed accordingly.

1. Serial Schedules:
Schedules in which the transactions are executed non-interleaved, i.e., a serial schedule is
one in which no transaction starts until a running transaction has ended are called serial schedules.
i.e., In Serial schedule, a transaction is executed completely before starting the execution of another
transaction. In other words, you can say that in serial schedule, a transaction does not start
execution until the currently running transaction finished execution. This type of execution of
transaction is also known as non- interleaved execution. The example we have seen above is the
serial schedule.

Example: Consider the following schedule involving two transactions T1 and T2.

T1 T2
R(A)
W(A)
R(B)
W(B)
R(A)
R(B)

Database Management Systems

School of Computing
Department Computer Science and Engineering
 here R(A) denotes that a read operation is performed on some data item ‘A’
This is a serial schedule since the transactions perform serially in the order T1 —> T2

2. Non-Serial Schedule:
This is a type of Scheduling where the operations of multiple transactions are interleaved.
This might lead to a rise in the concurrency problem. The transactions are executed in a non- serial
manner, keeping the end result correct and same as the serial schedule. Unlike the serial schedule
where one transaction must wait for another to complete all its operation, in the non-serial
schedule, the other transaction proceeds without waiting for the previous transaction to complete.
This sort of schedule does not provide any benefit of the concurrent transaction. It can be of two
types namely, Serializable and Non-Serializable Schedule.

T1 T2
R(A)
W(A)
W(B)
R(B)
R(A)
R(B)
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. 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.

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.

Database Management Systems

School of Computing
Department Computer Science and Engineering