Transaction Management

Transaction Management
Transaction 

A transaction is an event occurring in the real world that changes the state of an organisation.

A transaction is an atomic unit of database access which is either completely executed or not executed at all.

examples of transaction: deposit transaction and withdraw transaction, reservation and cancellation transaction, book issue and return transaction, purchase of item and sale of item transaction.

ACID properties of transaction

Atomicity 
A transaction is either completely executed or not executed at all.

Consistency 
The execution of a transaction should preserve database consistency despite concurrent execution of transactions.

Isolation 
The effect of non committed transaction must not be visible to another transaction. Each transaction must be executed in an isolated manner.

Durability 
The effect of committed transactions should persist in the database despite any kind of hardware software failure, fire, flood, natural or man made disaster.

Transaction States 

State Diagram

Active 

It is the initial state. The transaction remains in this state while it is executing.

Partially Committed 

After the execution of final statement or transaction moves to partially committed state.

Failed 

When a transaction can no longer continue to execute it moves to failed state.

Aborted 

A failed transaction is rolled back and the database is restored to its state prior to the start of transaction and then the transaction moves to aborted state.

Committed
After successful completion a transaction moves to committed state.

All the changes made by the transaction to the database is made permanent.

Schedule 

The execution sequences of two or more transactions is called schedule.

Types of schedule 

1. Serial and 2. non serial schedule

Serial Schedule

It is a schedule in which transactions are executed one after the other in a serial manner. It preserves consistency and isolation property of a transaction, but it gives low throughput, low resource utilization and low performance.

example

Non serial schedule

 It is a schedule in which instructions of two or more transactions are executed in a non serial manner. A non serial schedule may violate consistency and isolation property of a transaction, but it gives better throughput, resource utilization and performance.

example

Serializability

From consistency point of view we define serializability. It is a concept which ensure that the schedule generated by the system when executed will have the same effect as that of the serial schedule.

Conflict Serializability 

A non serial schedule which can be converted to a serial schedule through a series of swapping of non conflicting instructions is called conflict serializable schedule.

Conflict 

Two instructions i1 and i2 belonging to transactions t1 and t2 respectively are said to be in conflict, if both i1 and i2 are operating on the same data items and one of them is write operation.

Types of conflict 

1. read write conflict 

2. write read conflict 

3. write write conflict

example

Precedence graph to determine conflict serializability

Precedence graph G=(V,E) where V is set of vertices representing transactions and E is set of edges representing conflict between transactions.

A cycle in the precedence graph means the schedule is not conflict serializable.

If the precedence graph contains no cycle, the schedule is conflict serializable.

example

Recoverability 

From isolation point of view we define recoverability. It deals with the schedules acceptable from viewpoint of recovery from transaction failure.

There are two types schedules under this recoverable schedule and cascadeless schedule 

Recoverable Schedule

 It is a schedule where for each pair of transactions Ti and Tj such that Tj reads data items written by Ti, the commit of Ti appears before the commit of Tj.

example

Cascadeless Schedule

 It is a schedule where for each pair of transactions Ti and Tj such that Tj reads a data item previously written by Ti then commit operation of Ti appears before the read operation of Tj.

example