In this post, we will learn about concurrent transaction in dbms. We will see what is it, it’s example etc.

The main advantage of DBMS is that it allows ‘n’ number of users to access the data from the database in the concurrent manner.

In a multi-programming mode, many transactions are executed concurrently. In such a mode, while one transaction waits for the completion of an input/output operation, in the mean time, the processor can be used to do the computation of another transaction. That means, number of transactions alternatively share CPU and desk which will increase the throughput of the system.

System throughput is the average number of transactions completed in a given time.

Among the transactions running in the system, few may be short and other may be long. In case of serial transactions, short transactions may have to wait for the preceding long transaction to complete. Thus, there is wastage of time. This drawback is avoided in concurrent execution of the transaction which allows the sharing of CPU and disk read/write operations.

If the database is shared by the transactions for read-only purpose, it does not cause any problem. Problem arises when any transaction need to perform write operation in database.

Few situations when concurrent executions of transactions cause inconsistency in the database.

Example: Suppose that two ticket agents are dealing with the seat booking system of a flight. If both agents book last available seat in the flight, it would result in the overbooking or double booking of that seat. This leaves the database in an inconsistent state.

To avoid this inconsistency, some type of isolation should be enforced among the transactions so that they may affect each other –

In case of concurrent transactions, we cannot predict order of execution of the statements. Any statement of any transaction can execute in any order. The order in which these statements execute is called a Schedule.

Consider two concurrent transactions as shown below –

Concurrent transactions (where few operations of transaction T1 are executed, the T2 and again the remaining operations of T1).

In above transactions, transaction T1 transfers Rs. 50 from an account a to another account b. Transaction T2 just add Rs. 10 in the first account a referenced in the transaction T1.

Each transaction read some data items, performs some operations on the data item that change its value and then writes out the modified data item. The modifications are reflected in the database only after the write operation which implies that the changes are committed.

We went through concurrent transaction in dbms. In next post, we will see problems associated with concurrent transactions in dbms.