; concurrency_a
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

concurrency_a

VIEWS: 0 PAGES: 73

  • pg 1
									                          Concurrency Control



                                       Chapters 16,17




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   1
        Transaction Concept
                  A transaction is a unit of program execution
                   that accesses and possibly updates various
                   data items.
                  A transaction must see a consistent database.
                  During transaction execution the database
                   may be inconsistent.
                  When the transaction is committed, the
                   database must be consistent.
                  Two main issues to deal with:
                     Failures of various kinds, such as hardware
                      failures and system crashes
                     Concurrent execution of multiple transactions


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        2
        Concurrency in a DBMS
   Users submit transactions, and can think of each
    transaction as executing by itself.
       Concurrency is achieved by the DBMS, which interleaves
        actions (reads/writes of DB objects) of various transactions.
       Each transaction must leave the database in a consistent
        state if the DB is consistent when the transaction begins.
         • DBMS will enforce some ICs, depending on the ICs declared in
           CREATE TABLE statements.
         • Beyond this, the DBMS does not really understand the semantics of
           the data. (e.g., it does not understand how the interest on a bank
           account is computed).
   Issues: Effect of interleaving transactions, and crashes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                  3
        Atomicity of Transactions
 A transaction might commit after completing all its
  actions, or it could abort (or be aborted by the DBMS)
  after executing some actions.
 A very important property guaranteed by the DBMS
  for all transactions is that they are atomic. That is, a
  user can think of a Xact as always executing all its
  actions in one step, or not executing any actions at all.
       DBMS logs all actions so that it can undo the actions of
        aborted transactions.



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     4
        Example
   Consider two transactions (Xacts):
        T1:       BEGIN A=A+100, B=B-100 END
        T2:       BEGIN A=1.06*A, B=1.06*B END

 Intuitively, the first transaction is transferring $100
  from B’s account to A’s account. The second is
  crediting both accounts with a 6% interest payment.
 There is no guarantee that T1 will execute before T2 or
  vice-versa, if both are submitted together. However,
  the net effect must be equivalent to these two
  transactions running serially in some order.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   5
        Example (Contd.)
   Consider a possible interleaving (schedule):
        T1:        A=A+100,                           B=B-100
        T2:                          A=1.06*A,                   B=1.06*B

   This is OK. But what about:
        T1:        A=A+100,                                      B=B-100
        T2:                          A=1.06*A, B=1.06*B

   The DBMS’s view of the second schedule:
        T1:        R(A), W(A),                                      R(B), W(B)
        T2:                            R(A), W(A), R(B), W(B)

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                   6
    ACID Properties
     To preserve integrity of data, the database system must ensure:

          Atomicity. Either all operations of the
           transaction are properly reflected in the database
           or none are.
          Consistency. Execution of a transaction in
           isolation preserves the consistency of the
           database.
          Isolation. Although multiple transactions may
           execute concurrently, each transaction must be
           unaware of other concurrently executing
           transactions. Intermediate transaction results
           must be hidden from other concurrently executed
           transactions.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke         7
    ACID Properties – cont.
           That is, for every pair of transactions Ti
            and Tj, it appears to Ti that either Tj,
            finished execution before Ti started, or Tj
            started execution after Ti finished.
           Durability. After a transaction completes
            successfully, the changes it has made to
            the database persist, even if there are
            system failures.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   8
        Transaction State
             Active, the initial state; the transaction stays
              in this state while it is executing
             Partially committed, after the final statement
              has been executed.
             Failed, after the discovery that normal
              execution can no longer proceed.
             Aborted, after the transaction has been rolled
              back and the database restored to its state
              prior to the start of the transaction. Two
              options after it has been aborted:
                restart the transaction – only if no internal logical
                 error
                kill the transaction
             Committed, after successful completion.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           9
        Transaction State (Cont.)




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   10
        Transaction T1 and T2

          T1                                    T2
            begin                                 begin
            R(X);                                 X:=X+M;
            X:=X-N;                               W(X);
            W(X);                                 commit
            R(Y);
            Y:=Y+N;
            W(Y);
            commit

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   11
   The ‘lost update’ problem - atomicity
          T1                           T2
            R(X);                        begin
            X:=X-N;
                                          R(X);
                                          X:=X+M;
              W(X);
                                                                 Update
              R(Y);                                              X of
                                          W(X);                  T1 lost
              Y:=Y+N;                     commit
              W(Y);
              commit
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke             12
   The ‘wrong sum’ problem - isolation
  T1                                   T2
       begin                                begin
                                            sum:=0;
                                            R(A);
                                            sum:=sum+A;
       R(X);                                .
       X:=X-N;
                                            .
       W(X)
                                            .
                                            R(X)                 Read X
                                            sum:=sum+X;          before
                                            R(Y)                 update
                                            sum:=sum+Y;          and Y
       R(Y);                                commit               after
       Y:=Y+N                                                    update
       W(Y)
       commit

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            13
   Temporary update problem -
   isolation
  T1                                   T2
       begin                                begin
       R(X);
       X:=X-N;                                               time
       W(X)
                                            R(X);
                                            X:=X+M;
                                            W(X)

       R(Y);                                                        X must
                                            commit                  be
       abort
                                                                    restored




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                 14
        Schedules

           Schedules – sequences that indicate the
            chronological order in which instructions of
            concurrent transactions are executed
              a schedule for a set of transactions must consist of
               all instructions of those transactions
              must preserve the order in which the instructions
               appear in each individual transaction.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        15
        Example Schedules
            Let T1 transfer $50 from A to B, and T2
             transfer 10% of the balance from A to B. The
             following is a serial schedule (Schedule 1 in
             the text), in which T1 is followed by T2.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   16
     Example Schedule (Cont.)
        Let T1 and T2 be the transactions defined
         previously. The following schedule
         (Schedule 3 in the text) is not a serial
         schedule, but it is equivalent to Schedule 1.




          In both Schedule 1 and 3, the sum A + B is preserved.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    17
       Example Schedules (Cont.)
              The following concurrent schedule (Schedule
               4 in the text) does not preserve the value of
               the the sum A + B.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   18
       Serializability
         Basic Assumption – Each transaction preserves
          database consistency.
         Thus serial execution of a set of transactions
          preserves database consistency.
         A (possibly concurrent) schedule is serializable if it
          is equivalent to a serial schedule. Different forms
          of schedule equivalence give rise to the notions of:
           1. conflict serializability
           2. view serializability
         We ignore operations other than read and write
          instructions, and we assume that transactions may
          perform arbitrary computations on data in local
          buffers in between reads and writes. Our
          simplified schedules consist of only read and write
          instructions.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     19
         Conflict Serializability
        actions li and lj of transactions Ti and Tj respectively,
         conflict if and only if there exists some item Q
         accessed by both li and lj, and at least one of these
         instructions wrote Q.
         1. li = read(Q), lj = read(Q). li and lj don’t conflict.
         2. li = read(Q), lj = write(Q). They conflict.
         3. li = write(Q), lj = read(Q). They conflict
         4. li = write(Q), lj = write(Q). They conflict
        Intuitively, a conflict between li and lj forces a
         (logical) temporal order between them. That is,
         replacing their order will change the result!
         If li and lj are consecutive in a schedule and they do
         not conflict, their results would remain the same
         even if they had been interchanged in the schedule.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       20
      Conflict Serializability (Cont.)
              If a schedule S can be transformed into a schedule
               S´ by a series of swaps of non-conflicting
               instructions, we say that S and S´ are conflict
               equivalent.
              We say that a schedule S is conflict serializable if
               it is conflict equivalent to a serial schedule
              Example of a schedule that is not conflict
               serializable:
                                  T3      T4
                                read(Q)
                                        write(Q)
                                write(Q)

               We are unable to swap instructions in the above
               schedule to obtain either the serial schedule < T3,
               T4 >, or the serial schedule < T4, T3 >.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        21
     Conflict Serializability (Cont.)
            Schedule 3 below can be transformed into
             Schedule 1, a serial schedule where T2
             follows T1, by series of swaps of non-
             conflicting instructions. Therefore
             Schedule 3 is conflict serializable.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   22
        Scheduling Transactions
 Serial schedule: Schedule that does not interleave the
  actions of different transactions.
 Equivalent schedules: For any database state, the effect
  (on the set of objects in the database) of executing the
  first schedule is identical to the effect of executing the
  second schedule.
 Serializable schedule: A schedule that is equivalent to
  some serial execution of the transactions.
(Note: If each transaction preserves consistency, every
  serializable schedule preserves consistency. )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   23
        Conflict Serializable Schedules

           Two schedules are conflict equivalent if:
                They involve the same actions of the same
                 transactions
                Every pair of conflicting actions is ordered the
                 same way
           Schedule S is conflict serializable if S is
            conflict equivalent to some serial schedule




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      24
        Dependency Graph

         Dependency graph: One node per Xact; edge
          from Ti to Tj if Tj reads/writes an object last
          written by Ti.
         Theorem: A Schedule is conflict serializable if
          and only if its dependency graph is acyclic




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   25
      Testing for Serializability
               Consider some schedule of a set of
                transactions T1, T2, ..., Tn
               Precedence graph — a direct graph where
                the vertices are the transactions (names).
               We draw an arc from Ti to Tj if the two
                transaction conflict, and Ti accessed the data
                item on which the conflict arose earlier.
               We may label the arc by the item that was
                accessed.
                                             x
               Example 1




                                                      y

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   26
       Example Schedule (Schedule A)
                  T1         T2             T3        T4           T5
                           read(X)
               read(Y)
               read(Z)
                                                               read(V)
                                                               read(W)
                                                               read(W)
                           read(Y)
                           write(Y)
                                        write(Z)
               read(U)
                                                        read(Y)
                                                        write(Y)
                                                        read(Z)
                                                        write(Z)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           27
        Precedence Graph for Schedule A
                                            Y
                                 T1                      T2

                             Z                                   Y



                                                          T4
                                   T3
                                                Z


               There is no Cycle!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       28
        Test for Conflict Serializability
         A schedule is conflict serializable if and only if its
          precedence graph is acyclic.
         Cycle-detection algorithms exist which take order n2
          time, where n is the number of vertices in the graph.
          (Better algorithms take order n + e where e is the
          number of edges.)
         If precedence graph is acyclic, the serializability
          order can be obtained by a topological sorting of the
          graph. This is a linear order consistent with the
          partial order of the graph.
          For example, a serializability order for Schedule A
          would be
          T1  T3  T2  T4 .

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     29
        Serializability Theorem

           A schedule is conflict – serializable if and
            only if the dependency graph has no cycle.

           Proof in Ullman (old book)
            (see also old Hovereth)




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   30
        Example
            A schedule that is not conflict serializable:

       T1:        R(A), W(A),                                        R(B), W(B)
       T2:                            R(A), W(A), R(B), W(B)

                                  A
                 T1                               T2         Dependency graph
                                  B
            The cycle in the graph reveals the problem.
             The output of T1 depends on T2, and vice-
             versa.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                    31
        Example

            begin                     begin                      begin
            R(X);                     R(Z);                      R(Y);
            W(X);                     R(Y);                      R(Z);
            R(Y);                     W(Y);                      W(Y);
            W(Y);                     R(X);                      W(Z);
                                      W(X);
            commit                    commit                     commit




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            32
        Example – cont.
     Transaction T1         Transaction T2          Transaction T3
        begin                  begin                   begin
                               R(Z);
                               R(Y);
                               W(Y);
                                                        R(Y);
                                                        R(Z);
         R(X);
         W(X);
                                                        W(Y);
                                                        W(Z);
                                R(X);
         R(Y);
         W(Y);                  W(X);
         commit                 commit                  commit
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       33
        Example – cont.
     Transaction T1        Transaction T2           Transaction T3
        begin                 begin                    begin
                                                       R(Y);
                                                       R(Z);

        R(X);
        W(X);                                           W(Y);
                                                        W(Z);
                               R(Z);

        R(Y);
        W(Y);
                               R(Y);
                               W(Y);
                               R(X);
                               W(X);
        commit                 commit                   commit
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       34
           Concurrency Control vs. Serializability
           Tests
        Testing a schedule for serializability after it has
         executed is a little too late!
        Goal – to develop concurrency control
         protocols that will assure serializability. They
         will generally not examine the precedence
         graph as it is being created; instead a protocol
         will impose a discipline that avoids
         nonseralizable schedules.
        Example for Protocol – A traffic light!
        We will study the two-phase Locking protocol
         later
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   35
        View Serializability
       Schedules S1 and S2 are view equivalent if:
            If Ti reads initial value of A in S1, then Ti also reads
             initial value of A in S2
            If Ti reads value of A written by Tj in S1, then Ti also
             reads value of A written by Tj in S2
            If Ti writes final value of A in S1, then Ti also writes
             final value of A in S2
     T1: R(A)      W(A)                             T1: R(A),W(A)
     T2:      W(A)                                  T2:           W(A)
     T3:                W(A)                        T3:                W(A)


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                36
      View Serializability (Cont.)
        A schedule S is view serializable it is view
         equivalent to a serial schedule.
        Every conflict serializable schedule is also view
         serializable.
        Schedule 9 (from text) — a schedule which is view-
         serializable but not conflict serializable.




        Every view serializable schedule that is not conflict
         serializable has blind writes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   37
      Other Notions of Serializability
             Schedule 8 (from text) given below produces
              same outcome as the serial schedule < T1, T5 >, yet
              is not conflict equivalent or view equivalent to it.




             Determining such equivalence requires analysis
              of operations other than read and write.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       38
  Recoverability
  Need to address the effect of transaction failures on concurrently
  running transactions.
       Recoverable schedule — if a transaction Tj reads a
        data items previously written by a transaction Ti , the
        commit operation of Ti should appear before the
        commit operation of Tj.
       The following schedule (Schedule 11) is not
        recoverable if T9 commits immediately after the read




         If T8 should abort, T9 would have read (and possibly
          shown to the user) an inconsistent database state.
          Hence database must ensure that schedules are
          recoverable.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke         39
    Recoverability (Cont.)
        Cascading rollback – a single transaction failure
         leads to a series of transaction rollbacks.
         Consider the following schedule where none of
         the transactions has yet committed (so the
         schedule is recoverable)




           If T10 fails, T11 and T12 must also be rolled back.
        Can lead to the undoing of a significant amount
           of work
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   40
        Recoverability (Cont.)

         Cascadeless schedules — cascading rollbacks
          cannot occur; for each pair of transactions Ti
          and Tj such that Tj reads a data item
          previously written by Ti, the commit
          operation of Ti appears before the read
          operation of Tj.
         Every cascadeless schedule is also recoverable
         It is desirable to restrict the schedules to those
          that are cascadeless


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   41
         Concurrency Control Protocols
         (guarantee serializability rather than testing after
         the fact…)

        Lock-Based Protocols
        Timestamp-Based Protocols
        Validation-Based Protocols
        Multiple Granularity
        Multiversion Schemes
        Deadlock Handling
        Concurrency in Index Structures



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   42
         Lock-Based Protocols
        A lock is a mechanism to control concurrent access to a
         data item
        Analog to traffic lights
        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 (Lock)
         manager. Transaction can proceed only after request is
         granted (by the Lock manager).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       43
         Lock-Based Protocols (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
        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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     44
      Lock-Based Protocols (Cont.)
        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.
        A locking protocol is a set of rules followed by all
         transactions while requesting and releasing locks.
         Locking protocols restrict the set of possible schedules.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       45
Locking does not guarantee
Serializability
          T1                           T2
               begin
               S_lock(Y);
               R(Y);
               unlock(Y);                   begin
                                            S_lock(X);
                                            R(X);
                                            unlock(X);           Result of schedule S
                                            X_lock(Y);           X = 50, Y = 50
                                            R(Y);
                                            Y:=X+Y;              nonserializable
                                            W(Y);
                                            unlock(Y);
               X_lock(X);                   commit
               R(X);
               X:=X+Y;
               W(X);
               unlock(X);
               commit

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                          46
        Two-Phase Locking (2PL)

           Two-Phase Locking Protocol
                Each Xact must obtain a S (shared) lock on object
                 before reading, and an X (exclusive) lock on object
                 before writing.
                A transaction can not request additional locks
                 once it releases any locks.
                If an Xact holds an X lock on an object, no other
                 Xact can get a lock (S or X) on that object.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke         47
        The Two-Phase Locking Protocol
           This is a protocol which ensures conflict-serializable
            schedules.
           Phase 1: Growing Phase
              transaction may obtain locks
              transaction may not release locks
           Phase 2: Shrinking Phase
              transaction may release locks
              transaction may not obtain locks
           The protocol assures serializability. It can be proved
            that the transactions can be serialized in the order of
            their lock points (i.e. the point where a transaction
            acquired its final lock).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        48
        2PL Theorem

         2PL => conflict serilizable
         Note: this is sufficient condition not necessary
          one
         Proof in (old) Ullman (also old Hovereth)




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   49
      Automatic Acquisition of Locks
        A transaction Ti issues the standard read/write
         instruction, without explicit locking calls.
        The operation read(D) is processed as:
                   if Ti has a lock on D
                      then
                          read(D)
                      else
                          begin
                            if necessary wait until no other
                               transaction has a lock-X on D
                            grant Ti a lock-S on D;
                            read(D)
                          end
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   50
     Automatic Acquisition of Locks
     (Cont.)
       write(D) is processed as:
        if Ti has a lock-X on D
           then
             write(D)
          else
            begin
              if necessary wait until no other trans. has any lock on D,
              if Ti has a lock-S on D
                  then
                    upgrade lock on D to lock-X
                 else
                    grant Ti a lock-X on D
                 write(D)
            end;
       All locks are released after commit or abort

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke             51
         Lock Conversions
        Two-phase locking with lock conversions:
         – First Phase:
           can acquire a lock-S on item
           can acquire a lock-X on item
           can convert a lock-S to a lock-X (upgrade)
         – Second Phase:
           can release a lock-S
           can release a lock-X
           can convert a lock-X to a lock-S (downgrade)
        This protocol assures serializability. But still relies on
         the programmer to insert the various locking
         instructions.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        52
Problem with regular 2PL – Cascading
Rollbacks
          T1                           T2
               begin                        begin                T3
               X_lock(X);                   X_lock(Z);                begin
               W(X);                        .
               .                            .
               .                            .
               Rest of T1
               .
               .
               unlock(X);
               unlock(Y);                   S_lock(X);
                                            R(X);
                                            Rest of T2
                                            unlock(Z);
                                            unlock(X);                S_lock(Z);
                                            unlock(Y);                R(Z);
                                                                      rest of T3
                                            commit                    unlock(X);
                                                                      unlock(Y);
               Abort
                                                                      unlock(Z);
                                                                      commit
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                     53
    Lock-Based Concurrency Control
   Strict Two-phase Locking (Strict 2PL) Protocol:
        Each Xact must obtain a S (shared) lock on object before
         reading, and an X (exclusive) lock on object before writing.
        All locks held by a transaction are released when the
         transaction completes
        Strict 2PL prevents Cascading rollbacks!


   Strict 2PL allows only serializable schedules.
      Additionally, it simplifies transaction aborts
      (Non-strict) 2PL also allows only serializable schedules,
       but may not prevent cascading rollbacks

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      54
        Problem even with S2PL - Deadlock

          T1                               T2
               begin                            begin
               S_lock(Y);                       S_lock(X);
               R(Y);                            R(X);
               .                                .
               .                                .
               X_lock(X)                        X_lock(Y)
               wait                             wait




        Solution – VS2PL – acquires all locks at the
          beginning (not practical!)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   55
        Types of 2PL protocols

           2PL


           S2PL


           VS2PL
            deadlock cannot happen


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   56
        Pitfalls of Lock-Based Protocols
           The potential for deadlock exists in most locking
            protocols. Deadlocks are a necessary evil.
           Starvation is also possible if concurrency control
            manager is badly designed. For example:
              A transaction may be waiting for an X-lock on an item, while
               a sequence of other transactions request and are granted an
               S-lock on the same item.
              The same transaction is repeatedly rolled back due to
               deadlocks.
           Concurrency control manager can be designed to
            prevent starvation.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                57
        Lock Management

           Lock and unlock requests are handled by the lock
            manager
           Lock table entry:
                Number of transactions currently holding a lock
                Type of lock held (shared or exclusive)
                Pointer to queue of lock requests
           Locking and unlocking have to be atomic operations
           Lock upgrade: transaction that holds a shared lock
            can be upgraded to hold an exclusive lock



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     58
     Lock Table                                       Black rectangles indicate
                                                       granted locks, white ones
                                                       indicate waiting requests
                                                      New request is added to the end
                                                       of the queue of requests for the
                                                       data item, and granted if it is
                                                       compatible with all earlier locks
                                                      Unlock requests result in the
                                                       request being deleted, and later
                                                       requests are checked to see if
                                                       they can now be granted
                                                      If transaction aborts, all waiting
                                                       or granted requests of the
                                                       transaction are deleted
                                                         lock manager may keep a
                                                            list of locks held by each
                                                            transaction, to implement
                                                            this efficiently


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                              59
        Starvation Problem

         T1, T2,T3, T1, T2, T3,….
         T1 – R
          T2 – X
          T3 – R
         If we let T3 in T2 will “starve”
        Lock upgrade problem
         T0 – R
          T1 – R
          T2 – X
         T1 upgrades lock to X and queued after T2 - Deadlock!


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    60
        Deadlock Handling

           System is deadlocked if there is a set of transactions
            such that every transaction in the set is waiting for
            another transaction in the set.
           Deadlock prevention protocols ensure that the
            system will never enter into a deadlock state. Some
            prevention strategies :
              Require that each transaction locks all its data items before it
               begins execution (predeclaration).
              Impose partial ordering of all data items and require that a
               transaction can lock data items only in the order specified by
               the partial order (graph-based protocol).
           Both are impractical for Databases!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                    61
        Deadlock Detection

           Create a waits-for graph:
                Nodes are transactions
                There is an edge from Ti to Tj if Ti is waiting for Tj
                 to release a lock
         Periodically check for cycles in the waits-for
          graph
         Why its ok to check for cycles in the Wait-for
          graph and not ok to check for cycles in the
          Serializability graph?

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            62
        Deadlock Recovery

           When deadlock is detected :
              Some transaction will have to rolled back (made a victim) to
               break deadlock. Select that transaction as victim that will
               incur minimum cost.
              Rollback -- determine how far to roll back transaction
                  • Total rollback: Abort the transaction and then restart it.
                  • More effective to roll back transaction only as far as necessary
                    to break deadlock.
              Starvation happens if same transaction is always chosen as
               victim. Include the number of rollbacks in the cost factor to
               avoid starvation



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                         63
        Aborting a Transaction
 If a transaction Ti is aborted, all its actions have to be
  undone. Not only that, if Tj reads an object last
  written by Ti, Tj must be aborted as well!
 Most systems avoid such cascading aborts by releasing
  a transaction’s locks only at commit time.
       If Ti writes an object, Tj can read this only after Ti commits.
   In order to undo the actions of an aborted transaction,
    the DBMS maintains a log in which every write is
    recorded. This mechanism is also used to recover
    from system crashes: all active Xacts at the time of the
    crash are aborted when the system comes back up.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        64
        The Log
   The following actions are recorded in the log:
       Ti writes an object: the old value and the new value.
         • Log record must go to disk before the changed page!
       Ti commits/aborts: a log record indicating this action.
 Log records are chained together by Xact id, so it’s
  easy to undo a specific Xact.
 Log is often duplexed and archived on stable storage.
 All log related activities (and in fact, all CC related
  activities such as lock/unlock, dealing with deadlocks
  etc.) are handled transparently by the DBMS.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    65
        Deadlock Detection (Continued)
        Example:

        T1: S(A), R(A),           S(B)
        T2:             X(B),W(B)                X(C)
        T3:                           S(C), R(C)           X(A)
        T4:                                           X(B)

           T1                      T2                       T1   T2



           T4                     T3                        T3   T3
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        66
        Deadlock detection and recovery

         When? (time, Hold/Wait condition)
         Who to kill?
         How? (cycle discovery)




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   67
         More Deadlock Prevention
         Strategies
        Following schemes use transaction timestamps for the
         sake of deadlock prevention alone.
        wait-die scheme — non-preemptive
           older transaction may wait for younger one to release data
            item. Younger transactions never wait for older ones; they
            are rolled back instead.
           a transaction may die several times before acquiring needed
            data item
        wound-wait scheme — preemptive
           older transaction wounds (forces rollback) of younger
            transaction instead of waiting for it. Younger transactions
            may wait for older ones.
           may be fewer rollbacks than wait-die scheme.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            68
        Deadlock prevention (Cont.)

           Both in wait-die and in wound-wait schemes, a rolled
            back transactions is restarted with its original
            timestamp. Older transactions thus have precedence
            over newer ones, and starvation is hence avoided.
           Timeout-Based Schemes :
              a transaction waits for a lock only for a specified amount of
               time. After that, the wait times out and the transaction is
               rolled back.
              thus deadlocks are not possible
              simple to implement; but starvation is possible. Also
               difficult to determine good value of the timeout interval.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                 69
        Deadlock Prevention

           Assign priorities based on timestamps.
            Assume Ti wants a lock that Tj holds. Two
            policies are possible:
                Wait-Die: It Ti has higher priority, Ti waits for Tj;
                 otherwise Ti aborts
                Wound-wait: If Ti has higher priority, Tj aborts;
                 otherwise Ti waits
           If a transaction re-starts, make sure it has its
            original timestamp

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           70
         Dynamic Databases
        If we relax the assumption that the DB is a fixed
         collection of objects, even Strict 2PL will not assure
         serializability:
            T1 locks all pages containing sailor records with rating = 1,
             and finds oldest sailor (say, age = 71).
            Next, T2 inserts a new sailor; rating = 1, age = 96.
            T2 also deletes oldest sailor with rating = 2 (and, say, age =
             80), and commits.
            T1 now locks all pages containing sailor records with rating
             = 2, and finds oldest (say, age = 63).
        No consistent DB state where T1 is “correct”!


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                71
        The Phantom Problem – Solutions?
           T1 implicitly assumes that it has locked the
            set of all sailor records with rating = 1.
              Assumption only holds if no sailor records are
               added while T1 is executing!
              Need some mechanism to enforce this
               assumption. (Index locking and predicate
               locking, or lock entire table…)
           Example shows that conflict serializability
            guarantees serializability only if the set of
            objects is fixed!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   72
        Insert and Delete Operations

           If two-phase locking is used :
              A delete operation may be performed only if the transaction
               deleting the tuple has an exclusive lock on the tuple to be
               deleted.
              A transaction that inserts a new tuple into the database is
               given an X-mode lock on the tuple
           Insertions and deletions can lead to the phantom
            phenomenon.
              A transaction that scans a relation (e.g., find all accounts in
               Perryridge) and a transaction that inserts a tuple in the
               relation (e.g., insert a new account at Perryridge) may
               conflict in spite of not accessing any tuple in common.
              If only tuple locks are used, non-serializable schedules can
               result: the scan transaction may not see the new account, yet
               may be serialized before the insert transaction.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                   73

								
To top
;