Transcript Concurrency Control

Concurrency Control
WXES 2103
Database
Content
Concurrency Problems
Concurrency Control
Concurrency Control Approaches
Concurrency Problems
Data integrity and consistency problem may
arise when several transactions are processed
simultaneously (multi-user DB environment)
Example: Ali and Siti have a joint savings
account in a bank and each has been issued an
ATM card. It may happen that both Ali and Siti
withdraw money at the same time in 2 different
locations.
Concurrency Problems (cont…)
Time
1
Ali’s Transaction
Read account balance
(Balance = RM1000)
2
3
4
Siti’s Transaction
Read account balance
(Balance = RM1000)
Withdraw RM600
(Balance = RM400)
Write Balance = RM400
Withdraw RM700
(Insufficient Funds!)
Concurrency Control
This type of problems are caused by lack
of coordination of the two transactions.
Maybe the DBMS does not provide
concurrency control.
Concurrency control refers to the
coordination of execution of multiple
transactions in a multi-user DB
environment.
Concurrency Control
3 problems associated with concurrent
processing :
1. Lost updates
2. Uncommitted data
3. Inconsistent retrievals
Lost Updates
Scenario
Assume that customer balance (Bal) is 500. And
2 transaction T1 and T2 attempt to update the
balance at the same time.
Transaction
Action
T1
Deposit 200
T2
Withdraw 100
Computation
Bal = 500+200
(Bal = 700)
Bal = 700-100
(Bal = 600)
Lost Updates (cont…)
The serial execution of these transaction
yields the correct results (Bal = 600)
Transaction requires several steps such as
reading, modifying and writing. And it must
be in correct sequence.
See the correct sequence for the previous
transactions.
Lost Updates (cont…)
Time
Trans.
Step
Stored Value
1
T1
Read Balance
2
T1
Balance=500+200
3
T1
Write Balance
700
4
T2
Read Balance
700
5
T2
Balance=700-100
6
T2
Write Balance
500
600
Lost Updates (cont…)
The scheduler below shows incorrect sequence leading to
lost updates
Time
Trans.
Step
Stored Value
1
T1
Read Balance
500
2
T2
Read Balance
500
3
T1
Balance=500+200
700
4
T2
Balance=500-100
400
5
T1
Write Balance (Lost Update)
700
6
T2
Write Balance
400
Uncommitted Data
Data are not committed when 2
transaction T1 and T2 are executed
concurrently, and T1 is rolled back after T2
has already accessed the uncommitted
data
This violates the isolation property of
transaction
Uncommitted Data (cont…)
Transaction
Action
Computation
T1
Deposit 200
Bal = 500+200
(Rolled back)
T2
Withdraw 100
Bal = 500-100
(Bal = 400)
This serial execution yields the correct results Balance = 400
Uncommitted Data (cont…)
Time
Trans.
Step
Stored Value
1
T1
Read Balance
2
T1
Balance=500+200
3
T1
Write Balance
700
4
T1
Roll back
500
5
T2
Read Balance
500
6
T2
Balance=500-100
7
T2
Write Balance
500
400
The scheduler below shows incorrect sequence leading to
lost updates
Time
Trans.
Step
Stored Value
1
T1
Read Balance
2
T1
Balance=500+200
3
T1
Write Balance
700
4
T2
5
T1
Read Balance
(Uncommitted data)
Roll back
500
6
T2
Balance=700-100
7
T2
Write Balance (Lost
update)
500
600
Inconsistent Retrieval
Occurs when a transaction calculates an
aggregate or summary function (e.g SUM)
over a set of data, which the other
transactions are updating
The inconsistency happens because the
transaction may read some data before
they are changed and read other data
after they are changed
Concurrency Control Approaches
Locking – If one user is updating the data,
all the other users denied access to the
same data
Time stamping – a unique global time
stamp is assigned to each transaction
Locking
A lock is a mechanism to control concurrent access to a
data item
Data items can be locked in two modes :
1. exclusive (X) mode. Data item can be both read as
well as written. X-lock is requested using lock-X
instruction.
2. shared (S) mode. Data item can only be read. S-lock
is requested using lock-S instruction.
Lock requests are made to concurrency-control
manager. Transaction can proceed only after request is
granted.
Locking (cont…)
Lock-compatibility matrix
A transaction may be granted a lock on
an item if the requested lock is
compatible with locks already held on
the item by other transactions
Locking (cont…)
Any number of transactions can hold shared
locks on an item, but if any transaction holds an
exclusive on the item no other transaction may
hold any lock on the item.
If a lock cannot be granted, the requesting
transaction is made to wait till all incompatible
locks held by other transactions have been
released. The lock is then granted.
Example of a transaction performing locking:
T2: lock-S(A);
read (A);
unlock(A);
lock-S(B);
read (B);
unlock(B);
display(A+B)
Locking as above is not sufficient to guarantee
serializability — if A and B get updated in-between
the read of A and B, the displayed sum would be
wrong.
Deadlock
Consider the partial schedule
Neither T3 nor T4 can make progress —
executing lock-S(B) causes T4 to wait for T3
to release its lock on B, while executing lockX(A) causes T3 to wait for T4 to release its
lock on A.
Deadlock (cont…)
Such a situation is called a deadlock.
 To handle a deadlock one of T3 or T4 must be
rolled back
and its locks released.
Methods to control deadlocks
Deadlock prevention - transacation
requesting a new lock is aborted if there is
a possibility that it might cause a deadlock
to occur
Methods to control deadlocks
Deadlock detection - DBMS periodically
checks the DB for any deadlocks. If exist it
aborts one of the transaction
Deadlock recovery - requires each
transaction locks all its data items before it
begins execution
Time stamping
Each transaction is issued a timestamp
when it enters the system. If an old
transaction Ti has time-stamp TS(Ti), a
new transaction Tj is assigned time-stamp
TS(Tj) such that TS(Ti) <TS(Tj).
The protocol manages concurrent
execution such that the time-stamps
determine the serializability order.
END
Next Class – Distributed Database