Showing posts with label Concurrency Control. Show all posts
Showing posts with label Concurrency Control. Show all posts

Need for Concurrency Control in Executing Database Transactions

Why do we need concurrency control mechanisms in DBMS? Problems with concurrent execution of transactions
 
Need for Concurrency Control in Executing Database Transactions

Concurrency or concurrent execution of transactions is about executing multiple transactions simultaneously. This is done by executing few instructions of one transaction then the next and so on. This way will increase the Transaction throughput (number of transactions that can be executed in a unit of time) of the system is increased. All the advantages are discussed here.

When you execute multiple transactions simultaneously, extra care should be taken to avoid inconsistency in the results that the transactions produce. This care is mandatory especially when two or more transactions are working (reading or writing) on the same database items (data objects). 

For example, one transaction is transferring money from account A to account B while the other transaction is withdrawing money from account A. these two transactions should not be permitted to execute in interleaved fashion like the transaction that are working on different data items. We need to serially execute (one after the other) such transactions.

If we do not take care about concurrent transactions that are dealing with same data items, that would end up in following problems;



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


Go to Transaction Management in DBMS home page












Concurrency Anomalies - Incorrect Summary Problem

Need for concurrency control, Concurrency Anomalies, Concurrent Execution Problems
Incorrect Summary / Inconsistent Analysis problem

This problem is caused when one of the transactions is executing an aggregate operation on several data items, and other transactions are updating one or more of those data items. This causes a inconsistent database state.

Example:

Consider the schedule S1 given below, in which, transaction T1 transfers money from account A to account B and in the mean time, transaction T2 calculates the sum of 3 accounts namely, A, B, and C. The third column shows the account balances and calculated values after every instruction is executed.

Transaction T1
Transaction T2
A = 1000, B = 1000, C = 1000




read(A);
A := A – 50;
write(A);






read(B);
B := B + 50;
write(B);
commit;
sum = 0;
avg = 0;
read(C);
sum := sum + C;



read(A);
sum := sum + A;
read(B);
sum := sum + B;
avg := sum/3;
commit;
sum  = 0
avg = 0
T2 read: C = 1000
sum = 1000
T1 read: A = 1000

T1 write: A = 950
T2 read: A = 950
sum = 1950
t2 read: B = 1000
sum = 2950
avg = 983.33

T2 read: B = 1000

T2 write: B = 1050

Discussion:

Transaction T2 reads the value of account A after A is updated and reads B before B is updated. [The portion that violates in T2 is highlighted in green color]. Hence, the aggregate operation is end up with an inconsistent result.
If all the instructions in T1 are executed before T2 starts, then A will be 950, B will be 1050 and average value will be 1000.
If all the instructions in T1 are executed after T1 finishes, then A will be 950, B will be 1050 and average value will be 1000.
But, due to this interleaved execution, the final value of A is 950, B is 1050, and average is 983.33 which is wrong.
This problem is called as Inconsistent analysis or Incorrect summary problem. This is caused due to the interleaved execution of multiple simultaneous transactions that are working on same data items.

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

Go to Transaction Management in DBMS home page









Concurrency Anomalies - Uncommitted Read Problem



Need for concurrency control, Concurrency Anomalies, Concurrent Execution Problems

Reading Uncommitted Data / Temporary Update / Dirty Read Problem


This problem is caused when one transaction is permitted to read a data item that is modified by an uncommitted transaction. It will cause us trouble if the uncommitted transaction decided to rollback.

For example, if a transaction T1 has modified a data item Q and if a transaction T2 is allowed to read the value of Q as modified by T1 before T1 commits, then it causes dirty read problem. Here, the read operation performed by T2 is called dirty read.

Example:

To explain this concept, let us use the schedule s1 given below;
Here, transaction T1 transfers 50 from account A to B, and transaction T2 calculates the 10 % interest on the balance of A and credits A with the interest.
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

T1 write: A = 950
T2 read: A = 950 (read uncommitted data)


T2 write: A = 1045

T1 decided to rollback.
Table: Schedule S1
Let us suppose the initial value of A and B are 1000;

  • The transaction T1, at first, read the value of A (ie., 1000).

  • Then T1 is writing account A with 950 after calculation.

  • Now transaction T2 reads the value of A that is written by T1 and consecutively calculated the interest value. Also, T2 has committed by writing 1045 into A.

  • In the mean time T1 is decided to roll back for some reason.

At this point, if T1 is rolled back, that makes the value 950 to be rolled back to 1000. But the interleaved transaction T2 has used that uncommitted value (950) which does not exist now. This read by T2 is called dirty read. And, this lead the database to an inconsistent state.

Discussion:

  • The process of allowing another transaction to view the intermediate results (not yet committed) of a transaction before it commits causes this problem. This is violating the Isolation property of transactions.

  • It is caused due to Write-Read conflict between transactions.



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

Go to Transaction Management in DBMS home page

Go to Need for Concurrency Control in DBMS page





Wikipedia

Search results

Followers