Concurrency Control

1

• Concurrency Control

2
Concurrency Control Techniques

• Protocols that guarantee serializability     
  1. Locking      2. Timestamps      3.
  Multiversion      4. Optimistic - validation or
  certification

3
Locking

• Locking - main technique to control concurrent
  execution
• execution based on concept of locking data items
• locks granted to a specific transaction for a
  particular data item
• a lock forces mutual exclusion on data items
• lock manager subsystem to keep track of and
  control access to locks

4
Binary locks

• binary lock - 2 states or values
• locked, unlocked
• distinct lock for each data item
• Suppose
• transaction T must issue a lock request before R,
  W
• issue unlock after R, W
• Too restrictive, what if want transactions to
  read at same time?

5
Multiple-mode lock

• multiple-mode lock - 3 types, indivisible
• R_lock - share lock
• W_lock - exclusive lock
• Un_lock - unlock  a lock needs 3 fields 
• ltdata, lock(R,W), of readsgt

6
Locking Rules for multiple-mode (theoretical)

• Rules
• 1.  T must issue request for R_ or W_lock before
  any R(X)
• 2.  T must issue request for W_lock before any
  W(X)
• 3.  T must issue Un_lock(X) after R or W
  completed
• 4.  If  issue request for W_lock(X) and hold
  R_lock on X, must upgrade R_lock(X) to W_lock(X)
• 5.  If issue R_lock(X) and hold W_lock on X,
  must downgrade W_lock(X) to R_lock(X)
• 6.  T will not issue Un_lock(X) unless hold
  R_lock(X) or W_lock(X)

7
Dirty Write example

•    R1(Y) R2(X) W2(Y) C2 R1(X) W1(X) C1
• Will locking prevent dirty write? 
• Request R1(Y) Unlock
• Request R2(X) Unlock
• Request W2(Y) Unlock
• C2
• Request R1(X)
• Upgrade W1(X) Unlock
• C1
• Locks do not guarantee serializability
• need 2PL

8
Two-Phase Locking 2PL

• 2PL has a        growing phase - locks acquired
         shrinking phase - locks released
• cannot request new lock during this phase
• Advantage
• Guarantees serializability
• Disadvantage
• 2PL limits concurrency

9
Requesting/Releasing locks

• Can upgrade R_lock to W_lock - must be done in
  growing phase
• Can downgrade W_lock to R_lock - must be done in
  shrinking phase
• If R(X) then W(X), we will assume request lock as
  W_lock(X)
• Theoretically, can release locks whenever done
  with item, as long as don't request new locks on
  any other data items
• We assume will release when transaction commits

10
Lock conflicts

• If lock conflict, force requesting transaction to
  block (BL) wait for transaction holding lock,
  proceed with other transactions
• W1(Y) R2(Y) C1 C2
• Request W1(Y) (Request and BL2 on R2(Y)) C1
  Unlock R2(Y) C2

11
Lost update problem

• R1(A) R2(A) W1(A) C1 W2(A) C2
• R1(A) (BL2 on R2(A)) W1(A) C1 (Un_lock(A)) R2(A)
  W2(A) C2
• T2 blocked, eventually both commit no lost
  update

12
Any problems with 2PL?

• R1(Y) R2(X) W2(Y) C2 W1(X) C1
• R1(Y) R2(X) (BL2 on W2(Y)) (BL1 on W1(X))
• T2 blocked then T1 blocked
• no lost update, but DEADLOCK

13
Types of 2PL

• 2PL is most common type of concurrency control in
  commercial systems
• Basic 2PL
• enforces serializability but deadlocks
• 2. Strict 2PL
• does not release any locks until commits or
  aborts
• what most commercial system assume
• Not deadlock free (But easier to recover from)
• Conservative 2PL
• requires transactions to lock all data items
  before begin executing
• prevents deadlock 
• Declare readset, writeset
• or request data items in order

14
2PL

• So what about deadlock?

15
Strategies for deadlock in 2PL

• 1.  No waiting - if Ti unable to obtain a lock,
  aborted and restarted after a time delay without
  checking if deadlock can occur.  T's can abort
  and restart needlessly.
• Cautious waiting - if Ti tried to lock X and X is
  already locked by Tj and if Tj is not blocked
• Ti waits else Tj aborts
• Deadlock free - illustrate through the total
  ordering of blocking times
• Timeouts - if Ti waits gt some system defined
  time-out, assume Ti is deadlocked, Ti is aborted

16
Strategies contd

• Deadlock detection - useful if T's rarely access
  the same items and each T only locks a few items
• construct a waits for graph (maintained by lock
  scheduler)
• deadlock when cycle in graph
• Problems
• victim selection - which to abort
• Livelock - if repeatedly choose the same victim
  to abort and restart
• if wait indefinite period of time, need a fair
  waiting scheme FCFS

17
Strategies contd

• 5.  Timestamps to prevent deadlocks
• Transactions can be assigned timestamps, TS(Ti)
    if T1 starts before T2,  TS(T1) lt TS(T2)   if
  T1 starts after T2, TS(T1) gt TS(T2)
• older Ti has a smaller TS
• Timestamp can be  a counter, or the current
  value of the system clock
• wait-die or wound-wait strategies use TSs

18
Wait-die

• Suppose Tj tries to lock X, and a CONFLICTING
  lock is already held by Ti
•   Wait-die  (aborts Transaction requesting
  lock)          if TS(Tj) lt TS(Ti)    // Tj is
  older              then Tj waits          else
  Tj aborts and restart with the same timestamp 
  // Tj is younger
• R1(Y) R2(X) W2(Y) C2 R1(X) W1(X) C1 where
  TS(T1)ltTS(T2)
• R1(Y) R2(X) A2 on W2(Y) R1(X) W1(X) C1 R2(X)
  W2(Y) C2

19
Wound-wait

• Wound-wait  (aborts Transaction holding lock)
    if TS(Tj) lt TS(Ti)   //  Tj is older
       then abort Ti       // if does not
  finish // in the mean time restart with same
  // timestamp    else Tj waits      //  Tj is
  younger
• R1(Y) R2(X) W2(Y) C2 R1(X) W1(X) C1 where
  TS(T1)ltTS(T2)
• R1(Y) R2(X) BL2 on W2(Y) A2 on R1(X) R1(X) W1(X)
  C1 T2 restarts and commits

20
Comparisons

• Wait-die - older waits on younger, else younger
  is aborted and restarted
• favors younger lock holder
• Wound-wait - younger waits on older, else older T
  requesting items held by younger preempts younger
  by abort
• favors older requester
• T's aborted and restarted even if not deadlocked.
  
• Wait-die  can abort Tj and restart many times
  in a row
• Wound-wait can be aborted even if obtain all of
  its locks (not true for wait-die, lock holder not
  aborted)

21
Strategies Useful?

• Classic concurrency control for Real-time
  Transactions
• Assume T1 holds lock, T2 wants it, dl is
  deadline
• If T1(dl) lt T2(dl) // T1 earlier deadline
• T2 waits
• else abort T1
• T2 gets lock // T2 has earlier deadline

22
Problems with serializability

• Scheduling that guarantees perfect
  serializability can be intrusive on performance
• Too many transaction in wait state
• If increase number of threads, can reduce the
  number of transactions active
• CPU never fully utilized

23
Alternatives to serializability

• Weakened forms of 2PL locking in SQL
• levels of isolation
• Used instead of degrees of isolation
• Can set the isolation level with set transaction
  statement (can specify R only, W only)     1) 
  read uncommitted    
• 2)  read committed     3)  repeatable read
      4)  serializable

24
Isolation levels

• Lock types used to implement
• Short-term lock
• guarantees R, W, is atomic
• long-term lock
• held until Transaction commits or aborts

25
Read uncommitted

• Read uncommitted (for read only)
• no long-term locks used
• allow for Read only operations
• no dirty writes (since only read)
• but dirty reads can occur

26
Read committed

• Read committed (no dirty reads)
• WL long term, RL short term
• Can only read data that has been written by
  committed transactions
• Unrepeatable reads can occur
• Lost update can still occur
• R1(A)R2(A)W1(A) C1 W2(A) C2
• uses cursor stability to prevent this (as long as
  cursors are used for update)
• lock held on each row fetched by cursor (R
  short, W long)
• Hold lock until move to next row      affects
  Ri(A) -gt Wj(A)
• Repeatable read till problem if read and update
  in separate fetch statements

27
Repeatable read

• Repeatable read
• WL, RL long term
• Repeatable reads, no lost update
• But, predicate locking is not guaranteed
• Predicate locking lock only rows that satisfy
  specified condition (e.g. major CS)
• therefore can have phantom updates due to
  inserting new rows
• e.g. if branch totals in branch table, and insert
  while computing total

28
Serializability

• Serializable requires RL long term on all data
  satisfying predicate
• lock entire table

29
Serializability

• Do we always have to use locking to ensure
  serializability?

30
Timestamp Ordering

• No - Timestamp Ordering
• concurrency control techniques based on TS's - do
  not use locks
• Can deadlock occur?

31
Timestamps

• Use timestamp ordering (TO)
• in 2PL schedule, serializable by being equivalent
  to some serial schedule allowed by locking
  protocols
• In TO schedule, equivalent to particular order
  that corresponds to order of transaction TS's

32
Timestamps (TO)

• See if interleaved operation are consistent with
  some serial order of execution in time
• Basic TO algorithm
• associated with each X, 2 TS values
• R_TS(X) - largest TS that has successfully read
  X
• W_TS(X) - largest TS that has successfully
  written X
• If T is aborted, it is restarted with LATER
  timestamp

33
TO Algorithms

• If T issues W(X)    if R_TS(X) gt TS(T) or
  W_TS(X) gt TS(T)      then abort and rollback T
• // reject operation request      else W(X)
  and set W_TS(X) TS(T)
• If T issues R(X)      if W_TS(X) gt TS(T)      
  then abort and reject operation request         
  else //W_TS(X) TS(T)
• R(X) and set R_TS(X) Max(TS(T),
  R_TS(X))
• R1(X)R2(X)W1(X)W2(X)

34
Example

• R1(X)R2(X)W1(X)W2(X) Assume TS(T1)1 and
  TS(T2)2
•                         X
• R_TS        W_TS
• 0                0
• 1                0  R1(X)
• 2                0  R2(X)
• W1(X), rollback,
• restart with T1 3
• 2               2  W2(X)
• C2
• 3                2   R1(X)
• 3                3   W1(X)
• C1

35
TO vs. 2PL

• TO and 2PL guarantee serializability
• Some schedules possible under each, not allowed
  under the other
• If T is aborted and rolled back, any value
  written by T also must be rolled back      (Can
  have cascading rollback)
• Schedules produced are not recoverable, does not
  ensure recoverable and cascade- less or strict
  schedules
• Will revisit this topic in more detail later

36
Recoverable?

• Strict TO - ensures strict schedules and conflict
  serializability
• if T issues R(X) or W(X),
• s.t. TS(T) gt W_TS(X)
• R,W operation delayed until T that wrote to
  value of X committed or aborted
• to implement, must simulate locking of X, but
  doesn't cause deadlocks

37
Multiversion Concurrency Control

• Multiversion Concurrency Control
• Oracle uses multiversions to enforce levels of
  isolation
• Useful for temporal DBs and RTDBs
• Keep old values when item is updated - several
  versions maintained
• When operation accesses item, appropriate version
  chosen to ensure serializability
• Read older version of item instead of abort
• Write new version, keep old one
• View serializability not conflict
  serializability is ensured

38
Multiversion

• Disadvantage - more storage
• however, may keep older versions for recovery
  anyway

39
Multiversion Algorithm

• If T issues  R(X), find version i of X with
  largest W_TS     s.t. W_TS(Xi) TS(T)     
  set R_TS(Xi) max(TS(T), R_TS(Xi))
• If T issues
• W(X), find version i of X with largest W_TS
  s.t. W_TS (Xi) TS(T) if TS(T) gt R_TS(Xi)
• create new version Xj with R_TS(Xj)
  W_TS(Xj) TS(T)
• else abort     // TS(T) lt R_TS(Xi) so must
  abort
• T1  W1(X)   T2  R2(X)W2(X)   T3  W3(X)   T4 
  W4(X)
•      W1(X)R2(X)W3(X)W2(X)W4(X)

40
Multiversion

• W1(X)R2(X)W3(X)W2(X)W4(X)
• Assume TS(T1)1, TS(T2)2, etc.
•      Version         R_TS    W_TS         
  X0                0            0            W0(X)
           X1                1           
  1            W1(X)                              
  2                          R2(X)         
  X2                3            3            W3(X)
           X3                2           
  2            W2(X)          X4               
  4            4            W4(X)

41
Summary of Multiversion (Multiversion Timestamp
Ordering)

• Several versions of X X1, X2, ... Xk
• For each version Xi, keep the timestamps
• R_TS(Xi) - largest of all TS's of transactions
  that successfully read version Xi
• W_TS(Xi) - TS of transaction that wrote values of
  version Xi
• Want to read version closest to, but less than
  your TS
• Always write a new version
• Only abort when a version with W_TS TS(T) has a
  R_TS gt TS(T)

42
Compare multiversion to TO

• Will this be the same in TO?
• W1(X)R2(X)W3(X)W2(X)W4(X)

43
Example (using TO)

• W1(X)R2(X)W3(X)W2(X)W4(X)
• R_TS    W_TS         0            0
          0            1     W1(X)        
  2                   R2(X)        
  3     W3(X)                        W2(X)
  aborts    
• //  W_TS gt TS(T)          4     W4(X)

44
View Equivalence and View Serializability

• Less restrictive than conflict equivalence
• Premise
• As long as each read reads a result of the same
  write
• the W operations produce the same results
• the read operations see the same view
• If the final write operations are the same, the
  database state is the same

45
View equivalence

• View equivalent if given 2 schedules, S and S
• same set of transactions in S and S' (same
  operations)
• for any operation on X in S, if value X read has
  been written by Wj(X), same condition must hold
  in S'
• If the operation Wk(Y) is the last operation to
  write to Y in S, then Wk(Y) must also be the
  last in S'

46
Multiversion

• How is view equivalence less restrictive?
• constrained vs. unconstrained write
• constrained - any W1(X) preceded by R1(X)
• unconstrained - no read before the W1(X) -
  independent of any previous value, also called
  blind write
• If the write is constrained, view and conflict
  equivalence are the same
• The difference occurs with blind writes
• There is an algorithm to test for view
  serializability - test is NP-complete
• Conflict seralizability is a subset of view
  seralizability

47
Multiversion

• Is the example schedule serializable?
• W1(X)R2(X)W3(X)W2(X)W4(X)
•      T1-gtT2-gtT3-gt(T3-gtT2)-gt T4
•    
• View equivalent but not conflict equivalent

48
Multiversion

• Does multiversion ever abort?
• W0(X)R1(X)W1(X)R2(X)R3(X)W3(X)W2(X)
• Under what circumstances will it abort?

49
Example (multiversions)

• W0(X)R1(X)W1(X)R2(X)R3(X)W3(X)W2(X)  
•   Version         R_TS    W_TS  
  V0                0            0            W0(X)
                        1                         
  R1(X) V1                1           
  1            W1(X)                       
  2                          R2(X)
                         3                         
  R3(X) V2                3           
  3            W3(X)                               
                       W2(X) aborts,
• version X1 has W_TS lt TS(T2)      but
  R_TS(X1) gt T2(T2)
• because T3 should have read values written by
  T2