Showing posts with label Transaction Management. Show all posts
Showing posts with label Transaction Management. Show all posts

Concurrency Anomalies - Lost Update Problem



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 Need for Concurrency Control in DBMS page







What is concurrency control in database transactions

What is concurrency control in database transactions? Define concurrency control, Why do we need concurrency control schemes? concurrent transactions


Concurrency Control in DBMS


Definition:

The process of executing many different simultaneous (concurrent execution) transactions by complying with ACID properties in a multi-user database system is called as concurrency control.

Why concurrent execution is needed?” is discussed in this post. Please refer.

Discussion:

Executing transactions in serial order, ie., one after the other maintains the integrity of data. All transactions that are executed serially will show a safe, consistent, durable database. But the major disadvantage is the time. Executing individual transactions in serial order consumes lot of time and reduces the transaction throughput. Hence, we need to execute multiple transactions simultaneously.
Assume that you are executing two transactions T1 and T2 simultaneously. Also assume that T1 transfers money from account A to account B and T2 withdraws money from account C. If we execute T1 and T2 simultaneously, the execution will not harm the consistency of the database because both transactions are working on different data items.
Let us suppose T1 transfers money from account A to account B and T2 withdraws money from account B. What would happen if we execute T1 and T2 simultaneously? As both transactions are working (writing) on the same data items (in this case, both T1 and T2 changes the value of data item B), whichever writes later will be the final value of B which is not correct.
From the above discussion we would understand that, the simultaneous transactions have to be handled with extra care to ensure the integrity of the data and the database if they read or write same data items. The process that is used to ensure this type of safe execution of transaction is called concurrency control.

********************









Serial Schedules in Database Transactions

Serial Schedules in Database Transactions, what is serial schedule?, Example for serial schedules, Define serial schedule


Serial Schedule


Schedule - A schedule is a list of operations (read, write, commit, abort, checkpoint, calculation – we mostly consider the read and write operations) that are performed by various concurrently (simultaneous) executing set of transactions. For further information refer the Transaction Schedules page.

Serial Schedule - A serial schedule is a schedule in which all the instructions belong to a transaction complete before the other transactions. That is, the transactions are executed one after the other without interleaving.

For example, assume that a schedule S with two transactions, namely, T1 and T2. If all the instructions of T1 are executed before T2 or all the instructions of T2 are executed before T1, then S is said to be a serial schedule.

Example:

Table 1, 2, and 3 show some examples of serial schedules.
In schedule S1, T1 completes before T2.
Transaction T1
Transaction T2
read(A);
A := A – 5;
write(A);
read(B);
B := B – 5;
write(B);






read(A);
temp := A * 0.1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
Table 1: Schedule S1

In schedule S2, the execution order is T3, T1, T2
Transaction T1
Transaction T2
Transaction T3








read(A);
A := A – 5;
write(A);
read(B);
B := B + 5;
write(B);














read(A);
temp := A * 0.1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
read(C);
temp1 := C * 0.5;
C := C + temp1;
write(C);
read(D);
temp2 := D * 0.5;
D := D + temp1;
write(D);

Table 2: Schedule S2

In schedule S3, T2 completes before T1.
Transaction T1
Transaction T2







read(A);
A := A – 5;
write(A);
read(B);
B := B – 5;
write(B);
read(A);
temp := A * 0.1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
Table 3: Schedule S3

 *******************



Go to Transaction Management in DBMS home page









Wikipedia

Search results