Database Transaction Processing

Suppose X has Rs.500 and Y has Rs.800 in their respective bank accounts and X sends some money let’s say Rs.200 to Y, this is referred to as transaction between X and Y.


Transaction is a set of interrelated operations that combinedly result in some unit of work.


The operations involved in this transaction are:

1.) Read the value (Rs.500) from X’s account in a variable- Read(X).

2.) Write the updated value (Rs.300) of variable in database- Write(X).

3.) Read the value (Rs.800) from Y’s account in a variable- Read(Y).

4.) Write the updated value (Rs.1000) of variable in database- Write(Y).


●A set of events created by some user program that manipulate (insert, update etc.) the data stored in database is referred to as the database transaction.


This transaction, let’s say T can be defined stepwise as:

                                           T:   read(X);

                                                   X: = X − 200;



                                                   Y: = Y + 200;



● A database transaction cycle consists of 5 states. These states can be described using a transaction model:

1.) During the execution, transaction stays in active state. It is the initial state of transaction.


2.) If the last operation of transaction gets executed without any error, then it moves to the partially committed state.


3.) In case any error occurs during any operation, then the transaction is sent to the failed state.


4.) After entering the failed state, the database need to discard all the changes occurred during transaction (rollback) and then abort the process by moving to abort state. The transaction can abort in 2 ways:

        i.) If the transaction was aborted as a result of some hardware or software error that was not created through the internal logic of the transaction, the system will restart the transaction as a new process.


         ii.) If the transaction was aborted because of some internal logical error that can be corrected only by rewriting the application program, or because the input was bad, or because the desired data was not found in the database, the system will kill the process.


5.) If the transaction has reached the partially committed state, and no error occurs during this state, then the transaction moves to the final or committed state as a successful completion.


Database Transaction Processing is a system which maintains the integrity of the data before and after the database transactions. The main responsibilities of a database transaction processing system are to maintain the Atomicity, Consistency, Isolation and Durability of database. These four properties are combinedly known as the ACID property.


Suppose the X’s account gets updated from Rs.500 to Rs.300 but due to some issues like the transaction failed, the operating system crashed, or the computer stopped operating, Y’s account could not get updated from Rs.800 to Rs.1000 then it will show the system failures. The atomicity takes care of either both accounts get updated or none gets.


Atomicity: This property ensures that either all the operations involved in transaction are saved in database completely or all the operations are rejected and the original records are restored. It is also referred as the All-or-None property. Ensuring atomicity is the responsibility of a component of the database called the recovery system.


Suppose the previous transaction between X and Y starts at time t1 and completes at time t2. During this period, another transaction completes at time t3 (t1<t3<t2) such that Y sends 1% of his money to Z and Z already has Rs.100 in his account. Now the system can execute these operations in various orders, two of them are:

1.) a.) update X’s account from Rs.500 to Rs.300

     b.) update Y’s account from Rs.800 to Rs.1000

     c.) update Y’s account from Rs.1000 to Rs.990

     d.) update Z’s account from Rs.100 to Rs.110


2.) a.) update X’s account from Rs.500 to Rs.300

    b.) update Y’s account from Rs.800 to Rs.792

    c.) update Y’s account from Rs.792 to Rs.992

    d.) update Z’s account from Rs.100 to Rs.108


Both of these cases will end up with distinct values of Y’s account’s money. That’s why we need to manage the concurrent transactions which refers to the isolation property.


Isolation:  The database system must take special actions to ensure that transactions operate properly without interference from concurrently executing database statements. Ensuring the isolation property is the responsibility of a component of the database system called the concurrency-control system.


Suppose the transaction between X and Y completes at t2 and the system crashed at the same instance, which results in the loss of memory of system about this transaction, means database will restore its previous state. Thus, a transaction’s actions must persist across crashes and the durability property ensures it.


Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures. The recovery system of the database is responsible for ensuring durability, in addition to ensuring atomicity.


During transaction X can send any amount of money to Y in form of integer or float only it cannot be in form of string, boolean, etc. Except that X+Y must be same before and after the transaction. This property is known as the consistency of database.


Consistency: A transaction must not violate any integrity constraints during its execution, and if the transaction leaves the database in an illegal state it is aborted and an error is thrown. This characteristic of a database is called the consistency. Ensuring consistency for an individual transaction is the responsibility of the application programmer who codes the transaction.


These are the basic points of a database transaction processing system.

The concurrency control system and the recovery system can be covered in next articles.



Contributor's Info



• Database transaction

• Concurrency control problems

• Operation in database

• Acid properties

• Why Recovery is needed

• Types of failures



The transaction provides a mechanism for describing logical units of database processing the database consist of hundreds of concurrent users are connected to each other to form a database transaction examples of such system include

Example: Banking application

Airline reservation system

Credit Card processing

These systems includes high amount of process of data and fast response time from hundreds of concurrent users a transaction is typically implemented by a computer program in which has database commands like insertion deletions and retrievals and some database technique.



A database will face three concurrency problems which is categorized as

• Dirty read

• Lost update

• Incorrect summary


Dirty read:

The first one occurs when two people read same data concurrently where one writes changes the data but that is not reflected to the data read by other one this is called dirty data read.

Lost update:

When two transaction can perform same operation like read and write operation in a same time then will be conflict operation can perform this will due to the lost update

Incorrect summary:

When two transaction can be performed simultaneously there will be the one transaction can be update and another one be calculated the sum of transaction these will due to incorrect problem in database These problems can be overcome by providing lock or by providing thread locks


Operation in Database:

Read item (x): Read the Database includes the following steps

• Find the address of disk block that contains item(x)

• The address can be store in the main memory that can be used any buffer

• The copy item(x) stored in the main memory

Write item(x):

• Find the address of the disk block that contains item(x)

• The address can be store in the main memory that can be used any buffer



In any databases there is four properties to maintain the data

• Atomicity

• Consistency

• Isolation

• Durability



In atomicity ensures that a transaction will run to completion as an indivisible unit at the end of which either no changes have occurred to the database or database has been changed in the consistent manner.

Example: if A transfers to money B rupees of 500 Read (A ,a)


Write (A, a)

Read (B, b)


Write (B, b)

If A=2000, and B=3000

After a successful completion



If we can A+B=1500+3500=5000


Correctness ensures that if the database was in a consistent state before the start of a transaction then or termination the database will also in consistent state

Example: if sum (A, B) is before transaction =sum (A, B) after transaction


Indicates that the actions performed by a transaction will be isolated or hidden from outside the transaction until it terminates.


All updates done by a transaction must become permanent



Recovery is needed because restore the data form the from Database

There are states of Database recovery are there:

• Precondition

• Condition

• Post condition



If a database is in a consistent state before it failure then is said to be precondition state


It occur some kind of system failure the database recovery can be occurs

Post condition:

Restore the database to the consistent state that existed before the failure



Failures are generally classified as transaction system and media failures. There are several possible reasons for a transaction to fail in the middle of execution

• Computer Failure

• Transaction Failure

• Local error exception

• Concurrency control enforcement

• Disk Failure

• Physical problem


Computer Failure:

A computer hardware and software occurs in a computer system then may be during transaction during the time of execution the system can be crashes that may be lot of data can be effected this caused the system failure.

Transaction Failure:

In Transaction failure some operation can performed addition operation that may cause due to the transaction failure. Transaction failure also occurs due to the erroneous parameters in time of execution

Local error exception:

During transaction execution certain condition may occur that necessitate cancelation of the transaction execution certain conditions may occurs in while transaction can performed there is in a insufficient transaction can be performed

Concurrency Control Enforcement:

The problem indicates the one or more transaction can be performed there is one transaction can be deadlock state that will be performed in the control state

Disk Failure:

In some transaction there is read and write operation can be performed in order to have any system crash there will be disk failure in memory

Physical problem:

This refers to an endless problem that include power or air conditioning and hardware problem this may be due to the physical problem only of them.

Contributor's Info

Comparison of 2 phase locking protocols

2 Phase Locking Protocol: It is a concurrency method that guarantees serializabilty.
This protocol mainly consists of 2 phases:

  1. Growing Phase: in this all locks are acquired.
  2. Shrinking Phase: in this all locks are released.

There are basically 4 types of 2 Phase Locking Protocol:

  1. Basic 2PL
  2. Conservative 2PL
  3. Strict 2PL
  4. Rigorous 2PL
  • Basic 2PL: In this during growing phase , locks are obtained but not released.

In shrinking phase , locks will be released, no new locks will be obtained.

Problems: deadlock and cascading rollbacks.

  • Conservative 2PL:In this during growing phase , all locks are obtained before transaction starts.

In shrinking phase , locks will be released.
Problems:practical implementation is difficult and cascading rollbacks.

  • Strict 2PL: most popularly used.

guarantees strict schedule
transaction does not release exclusive lock until it commits or aborts.
no dirty read is there
easily recoverable
Problems: deadlock.

  • Rigorous 2PL:  transaction doesn't release any lock until it commits or aborts

Problems: deadlock.

Contributor's Info