Concurrency Control in Transaction

Concurrency Control

  • 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.

  1. Lost updates
  2. Dirty read
  3. 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:

Transaction  Operation

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.

Time Transaction Step Stored Value
1 T1 Read Prod_Qty 35
2 T2 Read Prod_Qty 35
3 T1 Prod_qty=35+100
4 T2 Prod_qty=35-30
5 T1 Write Prod_qty(Lost Update) 135
6 T2 Write Prod_qty 5

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.

Transaction                      Operation
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.

Time Transaction Step Stored Value
1 T1 Read Prod_Qty 35
2 T1 Prod_qty=35+100
3 T1 Write Prod_qty 135
4 T2 Read Prod_Qty(Read Uncommited data) 135
5 T2 Prod_qty=135-30
6 T1 **Rollback** 35
7 T2 Write Prod_qty 105

 

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 .

Example:

•  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
t0
Read Balance of Acc1

Sum <- 200

t1
Read Balance of Acc2

Sum <- Sum + 300

t2
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
t7 Commit

Read Balance of Acc3

Sum <- Sum + 50 = 550

t8

•  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 .

Leave a Reply

Your email address will not be published. Required fields are marked *