concurrency control n.
Skip this Video
Loading SlideShow in 5 Seconds..
Concurrency Control PowerPoint Presentation
Download Presentation
Concurrency Control

Loading in 2 Seconds...

play fullscreen
1 / 26

Concurrency Control - PowerPoint PPT Presentation

  • Uploaded on

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)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Concurrency Control' - dane

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
concurrency control

Concurrency Control

WXES 2103


  • Concurrency Problems
  • Concurrency Control
  • Concurrency Control Approaches
concurrency problems
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 control1
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 control2
Concurrency Control
  • 3 problems associated with concurrent processing :
      • Lost updates
      • Uncommitted data
      • Inconsistent retrievals
lost updates
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.

lost updates cont
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 cont2
Lost Updates (cont…)

The scheduler below shows incorrect sequence leading to lost updates

uncommitted data
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
Uncommitted Data (cont…)

This serial execution yields the correct results Balance = 400

inconsistent retrieval
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
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 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
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 cont1
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 wrong.

  • 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
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
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 deadlocks1
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
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