Concurrent Execution of Transaction

In the transaction process, a system usually allows executing more than one transaction simultaneously. This process is called a concurrent execution.

Advantages of concurrent execution of a transaction

1. Decrease waiting time or turnaround time.
2. Improve response time
3. Increased throughput or resource utilization.

Concurrency problems

Several problems can occur when concurrent transactions are run in an uncontrolled manner, such type of problems is known as concurrency problems.

There are following different types of problems or conflicts which occur due to concurrent execution of transaction:

1. Lost update problem (Write – Write conflict)

This type of problem occurs when two transactions in database access the same data item and have their operations in an interleaved manner that makes the value of some database item incorrect.

If there are two transactions T1 and T2 accessing the same data item value and then update it, then the second record overwrites the first record.

Example: Let's take the value of A is 100

Here,

• At t1 time, T1 transaction reads the value of A i.e., 100.
• At t2 time, T1 transaction deducts the value of A by 50.
• At t3 time, T2 transactions read the value of A i.e., 100.
• At t4 time, T2 transaction adds the value of A by 150.
• At t5 time, T1 transaction writes the value of A data item on the basis of value seen at time t2 i.e., 50.
• At t6 time, T2 transaction writes the value of A based on value seen at time t4 i.e., 150.
• So at time T6, the update of Transaction T1 is lost because Transaction T2 overwrites the value of A without looking at its current value.
• Such type of problem is known as the Lost Update Problem.

Dirty read problem (W-R conflict)

This type of problem occurs when one transaction T1 updates a data item of the database, and then that transaction fails due to some reason, but its updates are accessed by some other transaction.

Example: Let's take the value of A is 100

Here,

• At t1 time, T1 transaction reads the value of A i.e., 100.
• At t2 time, T1 transaction adds the value of A by 20.
• At t3 time, T1transaction writes the value of A (120) in the database.
• At t4 time, T2 transactions read the value of A data item i.e., 120.
• At t5 time, T2 transaction adds the value of A data item by 30.
• At t6 time, T2transaction writes the value of A (150) in the database.
• At t7 time, a T1 transaction fails due to power failure then it is rollback according to atomicity property of transaction (either all or none).
• So, transaction T2 at t4 time contains a value which has not been committed in the database. The value read by the transaction T2 is known as a dirty read.

Unrepeatable read (R-W Conflict)

It is also known as an inconsistent retrieval problem. If a transaction T₁ reads a value of data item twice and the data item is changed by another transaction T₂ in between the two read operation. Hence T₁ access two different values for its two read operation of the same data item.

Example: Let's take the value of A is 100

Here,

• At t1 time, T1 transaction reads the value of A i.e., 100.
• At t2 time, T2transaction reads the value of A i.e., 100.
• At t3 time, T2 transaction adds the value of A data item by 30.
• At t4 time, T2 transaction writes the value of A (130) in the database.
• Transaction T2 updates the value of A. Thus, when another read statement is performed by transaction T1, it accesses the new value of A, which was updated by T2. Such type of conflict is known as R-W conflict. 

Related Posts:

• DBMS View: Read, Update, Create and Drop
• DBMS Data Independence: Logical and Physical
• Hashing in DBMS: Static and Dynamic
• Foreign key in DBMS
• Primary Key in DBMS
• Candidate Key in DBMS
• Aggregate Functions in DBMS
• Concurrency Control Protocols
• View Serializability in DBMS
• Conflict Serializability in DBMS
• Concurrent Execution of Transaction