Concurrency Control - Download as PowerPoint by HC12091023510


									Concurrency Control
      WXES 2103
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
Concurrency Problems (cont…)
Time      Ali’s Transaction     Siti’s Transaction

 1     Read account balance
       (Balance = RM1000)
 2                             Read account balance
                               (Balance = RM1000)
 3     Withdraw RM600
       (Balance = RM400)
       Write Balance = RM400
 4                             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
    Concurrency Control
3 problems associated with concurrent
processing :

 1. Lost updates
 2. Uncommitted data
 3. Inconsistent retrievals
              Lost Updates
Assume that customer balance (Bal) is 500. And
2 transaction T1 and T2 attempt to update the
balance at the same time.

Transaction         Action      Computation
    T1        Deposit 200     Bal = 500+200
                              (Bal = 700)
    T2        Withdraw 100    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
       Lost Updates (cont…)
Time   Trans.             Step    Stored Value

 1      T1      Read Balance          500

 2      T1      Balance=500+200

 3      T1      Write Balance         700

 4      T2      Read Balance          700

 5      T2      Balance=700-100

 6      T2      Write Balance         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
This violates the isolation property of
    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           500

 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          400
The scheduler below shows incorrect sequence leading to
lost updates

 Time   Trans.              Step            Stored Value
  1       T1     Read Balance                   500
  2       T1     Balance=500+200
  3       T1     Write Balance                  700
  4       T2     Read Balance
                 (Uncommitted data)
  5       T1     Roll back                      500
  6       T2     Balance=700-100
  7       T2     Write Balance (Lost            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
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
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
        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);
                  read (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
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 lock-
X(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.

Next Class – Distributed Database

To top