Explain about concurrent transactions in DBMS

A transaction is a unit of database processing which contains a set of operations. For example, deposit of money, balance enquiry, reservation of tickets etc.

Every transaction starts with delimiters begin transaction and terminates with end transaction delimiters. The set of operations within these two delimiters constitute one transaction.

main()
{
   begin transaction
} end transaction

There are three possible ways in which a transaction can be executed. These are as follows −

• Serial execution.
• Parallel execution.
• Concurrent execution.

Concurrent transaction or execution includes multiple transactions which are executed concurrently or simultaneously in the system.

Advantages

The advantages of the concurrent transactions are as follows −

• Increases throughput which is nothing but number of transactions completed per unit time.

• It reduces the waiting time.

Example

T1= 90sec
T2= 500sec
T3= 5sec.

If we execute serially by T1->T2->T3 then transaction T3 waits for 590 sec, so we go for non-serial or concurrent transactions to reduce waiting time.

i.e. T3 -> T1 -> T2.

Disadvantage

The disadvantage is that the execution of concurrent transactions may result in inconsistency.

Example 1

The concurrent execution given below is in a consistent state.

Example 2

The transaction given below is not consistent.

Concurrent schedules do not keep the database in the consistent state. The concurrent execution of the transactions has to be carried out in a controlled environment.

If a system consists of ‘n’ number of transactions, we will have more than ‘n!’ number of concurrent schedules, out of which only a few of them are keeping the database in consistent state.