Concurrency Control
o In the concurrency control, the multiple transactions can be executed
simultaneously.
o It may affect the transaction result. It is highly important to maintain the order
of execution of those transactions.
Problems of concurrency control
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. Unrepeatable read
a. Lost update problem
o When two transactions that access the same database items contain their
operations in a way that makes the value of some database item incorrect, then
the lost update problem occurs.
o If two transactions T1 and T2 read a record and then update it, then the effect
of updating of the first record will be overwritten by the second update.
Example:
Here,
o At time t2, transaction-X reads A's value.
o At time t3, Transaction-Y reads A's value.
o At time t4, Transactions-X writes A's value on the basis of the value seen at
time t2.
o At time t5, Transactions-Y writes A's value on the basis of the value seen at
time t3.
o So at time T5, the update of Transaction-X is lost because Transaction y
overwrites it without looking at its current value.
o Such type of problem is known as Lost Update Problem as update made by one
transaction is lost here.
b. Dirty Read
o The dirty read occurs in the case when one transaction updates an item of the
database, and then the transaction fails for some reason. The updated database
item is accessed by another transaction before it is changed back to the original
value.
o A transaction T1 updates a record which is read by T2. If T1 aborts then T2
now has values which have never formed part of the stable database.
Example:
o At time t2, transaction-Y writes A's value.
o At time t3, Transaction-X reads A's value.
o At time t4, Transactions-Y rollbacks. So, it changes A's value back to that of
prior to t1.
o So, Transaction-X now contains a value which has never become part of the
stable database.
o Such type of problem is known as Dirty Read Problem, as one transaction reads
a dirty value which has not been committed.
c. Inconsistent Retrievals Problem
o Inconsistent Retrievals Problem is also known as unrepeatable read. When a
transaction calculates some summary function over a set of data while the other
transactions are updating the data, then the Inconsistent Retrievals Problem
occurs.
o A transaction T1 reads a record and then does some other processing during
which the transaction T2 updates the record. Now when the transaction T1
reads the record, then the new value will be inconsistent with the previous
value.
Example:
Suppose two transactions operate on three accounts.
Transaction X Time Transaction Y
--- t1 ---
Read Balance of Acc-1 t2 ---
Sum=200
Read Balance of Acc-2
Sum=Sum +250=450 t3 ---
--- t4 Read Balance of Acc-3
--- t5 Update Balance of Acc-3 =150-50=100
---- t6 Read Balance of Acc-1
--- t7 Update Balance of Acc-1 =200+50=250
Read Balance of Acc-3 t8 COMMIT
Sum=Sum+100=550 t9 ---
o Transaction-X is doing the sum of all balance while transaction-Y is
transferring an amount 50 from Account-1 to Account-3.
o Here, transaction-X produces the result of 550 which is incorrect. If we write
this produced result in the database, the database will become an inconsistent
state because the actual sum is 600.
o Here, transaction-X has seen an inconsistent state of the database.
