Concurrency Control

1
Concurrency Control

• WXES 2103
• Database

2
Content

• Concurrency Problems
• Concurrency Control
• Concurrency Control Approaches

3
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.

4
Concurrency Problems (cont)
5
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.

6
Concurrency Control

• 3 problems associated with concurrent processing
  
• Lost updates
• Uncommitted data
• Inconsistent retrievals

7
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.

8
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.

9
Lost Updates (cont)
10
Lost Updates (cont)
The scheduler below shows incorrect sequence
leading to lost updates
11
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

12
Uncommitted Data (cont)
This serial execution yields the correct results
Balance 400
13
Uncommitted Data (cont)
14
The scheduler below shows incorrect sequence
leading to lost updates
15
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

16
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

17
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.

18
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

19
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.

20
Example of a transaction performing locking
T2 lock-S(A)
read (A)
unlock(A)
lock-S(B) read
(B) unlock(B)
display(AB) 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.
21
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 lock-X(A) causes T3
  to wait for T4 to release its lock on A.

22
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.

23
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

24
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

25
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)
  ltTS(Tj).
• The protocol manages concurrent execution such
  that the time-stamps determine the
  serializability order.

26

• END
• Next Class Distributed Database