Need for concurrency control, Concurrency Anomalies, Concurrent Execution Problems, Example for lost update problem, what does cause lost update problem in concurrent execution of transactions?
Lost Update Problem
Lost update problem occurs when two
or more transactions are updating same data items simultaneously. The
transaction (among the concurrent transactions) that commits at last (lately)
will decide the final value of that particular data item.
Example:
To understand lost update problem,
let us use the schedule 1 given in Table 2.
Let us assume that the initial value
of data items A and B are 1000 and 1000 respectively. Transaction T1 transfers
50 from A to B. Transaction T2 withdraws 10% of amount from A. If T1 and T2 are
executed in serial order then the final values will be as given in the Table 1;
If T1 then T2
|
If T2 then T1
|
Final values are,
A = 855
B = 1050
|
Final values are,
A = 850
B = 1050
|
Table 1: Final values of A and B if
T1 and T2 are executed in serial order
Let us suppose that we go for
concurrent execution of T1 and T2, ie., the instructions of both transactions are
interleaved, for example, as given in the table 2. As given in the table 2, the
execution goes as follows;
- T1 reads A (current value of A = 1000) then it calculates the new value of A (new value of A = 950).
- Now the control goes to T2 and T2 read A (current value of A = 1000). After that T2 calculates the new value of A as per its instruction (new value of A = 900).
- Then again control is transferred to T1 and T1 writes new value of A as it has calculated by T1. That means, T1 writes 950 as the new value of A and then reads the current value of B as 1000.
- Now, the control is transferred to T2 and T2 writes the new value of A as 900. Actually T2 overwrites the value that has been written by T1. That means T1 lost its update due to the action of T2.
- T1 takes the control and calculates the new B value, ie., B = 1050.
The final value of A and B are 900
and 1050 respectively as produced by schedule 1, which is wrong. This is because, T2 overwrites a value which was written by T1.
Transaction
T1
|
Transaction
T2
|
A
= 1000, B = 1000
|
read(A);
A := A – 50;
write(A);
read(B);
B := B + 50;
write(B);
commit;
|
read(A);
temp := A * 0.1;
A := A – temp;
write(A);
commit;
|
T1 read: A = 1000
T2 read: A = 1000
T1 write: A = 950
T1 read: B = 1000
T2 write: A = 900
(Overwrite)
T1 write: B =
1050
|
Table 1: Schedule 1
Discussion:
- Overwriting an uncommitted data leads the database to an inconsistent state.
- Lost update problem is caused due to Write-Write conflict between transactions.
***********************
Go to Transaction Management in DBMS home page
Go to Some keywords in DBMS - Need to know page
Go to Need for Concurrency Control in DBMS page
Sir please review the value of A and B in table 1. I think that those values are incorrect but I might be wrong. Thanks.
ReplyDelete