Concurrency Control

Document Sample
Concurrency Control Powered By Docstoc
					Concurrency Control

      Steve Russ
    November 2009
        Who needs ‘control’?
The context for this section of material is that
large databases are usually shared (by many
users, and resources) and it is efficient to
allow concurrent access. The relevant
concepts here are integrity (consistency and
correctness), security (ensuring users only do
what they are allowed), and recovery (return
after an error to a known correct state).
Concurrent access endangers each of these.
         Level of treatment
The topics of recovery and concurrency are
large and in practice very complex. We confine
treatment here to the logical or conceptual
level. Our material closely follows that in C.J.
Date Introduction to Database Systems (8th
ed.) Ch.16. There are similar treatments in
Silberschatz et al., and Connolly & Begg. The
book by Date has an excellent annotated
bibliography for each chapter.
        Sources for Learning
There are good chapters on each of the main
topics associated with concurrency control in
the Date book:
   Integrity (Chapter 9)
   Recovery (Chapter 15)
   Concurrency (Chapter 16)
   Security (Chapter 17)
For basic coverage you need to read and
digest the chapters on the first three topics.
            More Sources
There are similar chapters in the Silberschatz
et al recommended book (Ch.5, 15, 16, 17).
There are some good sets of notes on the web
on these topics – sometimes under the title of
‘Transaction processing’ or similar. For
example, Google finds several useful sets of
slides :‘lost update problem database’ (in UK).
Better to stick with one source in detail.
A transaction is a series of database commands
with a clear semantics (e.g. transfer of funds
from one account to another). It is a logical unit
of work. Thus if any command fails within the
transaction the whole series of commands is
undone. This is called Rollback. If nothing fails
the transaction concludes with Commit. Any
DBMS, and notations like SQL, support these.
           Transaction Example
IF any error occurred THEN GO TO UNDO; END IF;
IF any error occurred THEN GO TO UNDO; END IF;
COMMIT; GO TO FINISH; /*successful end*/
   ROLLBACK;               /*unsuccessful end*/
         Transaction Properties
• Atomicity: all or nothing (any error – leads to
  Rollback, as if nothing happened)
• Consistent: a consistent state always goes to
  another consistent state
• Isolation: a given transaction’s updates are
  hidden until that transaction Commits
• Durability: after a Commit, updates persist
These are the ACID properties of transactions.
  Each of the ACID properties of a transaction
  can be challenged by the environment. In
  order to recover the state of the database we
  can use:
• A log file recording every database operation.
• Checkpoints recording the state of all active
  It is then possible to develop an algorithm for
  transactions that we need to UNDO, and those
  that we need to REDO, to effect recovery.
         Three classic problems
• Lost Update
• Uncommited Dependency
• Inconsistent Analysis
  It is the logical possibility of each of these that
  demands the need for concurrency control.
  The problems arise from two or more
  transactions reading or writing on the same
  part of the same database.
                 Lost Update problem
  Time                   User 1 (Trans A)                       User2 (Trans B)
    1                       Retrieve t

    2                                                              Retrieve t

    3                       Update t

    4                                                              Update t




t is a tuple in a table retrieved by both users in the course of both transactions
Transaction A loses an update at time 4.
         Uncommitted Dependency
  Time                User 1 (Trans A)                    User 2 (Trans B)

    1                                                         Update t
    2                    Retrieve t
    3                                                         Rollback

    6                                                         Update t
    7                    Update t
    8                                                         Rollback

Here are two versions of this problem. In each one (Times 1-3 and 6-8) Transaction A
is dependent on an uncommitted change made by Transaction B which is lost on
           Inconsistent Analysis
Initially: Acc 1 = 40;       Acc2 = 50;   Acc3 = 30;
    Time         User 1 (Trans A)         User 2 (Trans B)

1                 Retrieve Acc 1 :
                     Sum = 40
2                 Retrieve Acc2 :
                    Sum = 90
3                                          Retrieve Acc3 :

4                                          Update Acc3:
                                             30 → 20
5                                          Retrieve Acc1:

6                                          Update Acc1:
                                             40 → 50
7                                             commit

8                Retrieve Acc3:
               Sum = 110 (not 120)
How to prevent such problems?
One way to manage concurrency to avoid such
problems is by a locking protocol.
Other approaches include serializability, time-
stamping, and shadow-paging. See books.
Where there is low risk of interference, two-
phase locking is a common approach although
this usually requires deadlock avoidance.
A lock applies to a particular tuple and may be
exclusive (X) or shared(S). More details in
lecture and in books.
       Why these problems?
Regarding Retrieve as a ‘read’ (R) and Update
as a ‘write’ (W). These problems do in fact
correspond to the three logical possibilities of
inconsistency arising from interleaving two
transactions. Consider successive actions:
RR – no problem
WR – uncommitted dependency
WW – lost update
RW – inconsistent analysis
                  Lost Update ‘solved’
  Time                  User 1 (Trans A)                       User2 (Trans B)
    1             Retrieve t (get S-lock on t)

    2                                                    Retrieve t (get S-lock on t)

    3           Update t (request X-lock on t)

    4                         wait                     Update t (request X-lock on t)

    5                         wait                                  wait

    6                         wait                                  wait


No update is lost but the result is deadlock – see later how to deal with this.
          Uncommitted Dependency
   Time                User 1 (Trans A)                       User 2 (Trans B)

     1                                                   Update t (get X-lock on t)
     2          Retrieve t (request S-lock on t)                      -
     3                       wait                                     -
     4                       wait                                     -
     5                       wait                             Commit / Rollback
                                                            (releases X-lock on t)
     6                Resume: Retrieve t
                       (get S-lock on t)
     7                         -
A resolution of the first version of the problem. Second version is similar (check this!).
       Inconsistent Analysis
Time        User 1 (Trans A)                User 2 (Trans B)

 1     Retrieve Acc1 : (get S-lock)
                Sum = 40
 2     Retrieve Acc2 : (get S-lock)
                Sum = 90
 3                                     Retrieve Acc3: (get S-lock)

 4                                      Update Acc3: (get X-lock)
                                               30 → 20
 5                                     Retrieve Acc1: (get S-lock)
 6                                    Update Acc1: (request X-lock)
 7           Retrieve Acc3:                       wait
            (request S-lock)                      wait
                  wait                            wait