- When more than one user is accessing same data at the same time then it is known as concurrency.
- When several transactions run concurrently database consistency can be destroyed .
- To remove this problem Concurrency control is used .
- The technique used to protect data when multiple users are accessing it concurrently is called as concurrency control .
Advantages of concurrency
The good is to serve many users and provides better throughput by sharing resources.
- Reduced waiting time response time or turn around time.
- Increased throughput or resource utilization
- If we run only one transaction at a time than the acid property is sufficient but it is possible that when multiple transactions are executed concurrently than database may become inconsistent.
- Overlapping with the input-output activity with CPU also makes the response time better.
- But interleaving of instruction between transaction may also lead to many problems due to which concurrency control is required.
Problems of concurrency control
- If transactions are executed serially, i.e., sequentially with no overlap in time, no transaction concurrency exists.
- Several problems can occur when concurrent transactions are executed in an uncontrolled manner.
Following are the three problems in concurrency control.
- Lost updates
- Dirty read
- Inconsistent Retrivals
1. Lost updates
The lost update problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost (overwritten by the other transaction).
One of the PRODUCT table’s attributes is a product’s quantity on hand (Prod_Qty).
Assume that you have a product whose current Pr od_Qty value is 35. Also assume that two concurrent transactions, T1 and T2, occur that update the Prod_Qty value for some item in the PRODUCT table.
The transactions are as follows.
Two concurrent transactions update Prod_Qty:
T1: Purchase 100 units Prod_Qty = Prod_Qty + 100
T2: Sell 30 units Prod_Qty = Prod_Qty– 30
The following Table shows how the lost update problem can arise.
|5||T1||Write Prod_qty(Lost Update)||135|
Note that the first transaction (T1) has not yet been committed when the second transaction (T2) is executed. Therefore, T2 still operates on the value 35, and its subtraction yields 5 in memory. In the meantime, T1 writes the value 135 to disk, which is promptly overwritten by T2. In short, the addition of 100 units is “lost” during the process.
2.Dirty read (Uncommited data)
The dirty read arises when one transaction updates some item and then fail due to some reason.
This updated item is retrieved by another transaction before it is changed back to the original value.
T1: Purchase 100 units PROD_QOH = PROD_QOH + 100 (Rolled back)
T2: Sell 30 units PROD_QOH = PROD_QOH – 30
The following Table shows how the uncommitted data problem can arise when the ROLLBACK is completed after T2 has begun its execution.
|4||T2||Read Prod_Qty(Read Uncommited data)||135|
3. Inconsistent Retrievals:
The inconsistent retrieval problem arises when one transaction retrieves data to use in some operation.
But before it can use this data another transaction update that data and commits.
So, this change will be hidden from first transaction and it will continue to use previous retrieved data. This problem is also known as Inconsistent Analysis Problem .
• In given below figure shows two transaction operating on three accounts.
• Transaction-A is summing all balances and Transaction-B is transferring an amount 50 from Account3 to Account1 .
Account1 Balance = 200
Account2 Balance = 300
Account3 Balance = 100
|Transaction – A||Time||Transaction – B|
|Read Balance of Acc1
Sum <- 200
|Read Balance of Acc2
Sum <- Sum + 300
|—||t3||Read Balance of Acc3|
|—||t4||Update Balance of Acc3
100 -> 100 – 50 -> 50
|—||t5||Read Balance of Acc1|
|—||t6||Update Balance of Acc 200 -> 200 + 50 -> 250|
Read Balance of Acc3
Sum <- Sum + 50 = 550
• Here, the result produced by Transaction – A is 550 , which is incorrect.
• So, if this result is written in database then database will be in inconsistent state because actual sum is 600 .
• In this case, Transaction – B commits all of its updates before Transaction – A reads Balance of Account3 .