Concurrency Control III

1
Concurrency Control III
2
General Overview

• Relational model - SQL
• Formal commercial query languages
• Functional Dependencies
• Normalization
• Physical Design
• Indexing
• Query Processing and Optimization
• Transaction Processing and CC

3
Review ACID

• Isolation
• Concurrent xctions unaware of each other
• How?
• We discussed locking protocols
• 2PL protocol and its variants
• Graph-base locking protocols
• Multiple-granularity protocol (continue)

4
Multiple-Granularity Locks

• Hard to decide what granularity to lock (tuples
  vs. pages vs. tables).
• Shouldnt have to decide!
• Data containers are nested

contains
5
Intention Lock Modes

• In addition to S and X lock modes, there are
  three additional lock modes with multiple
  granularity
• intention-shared (IS) indicates explicit locking
  at a lower level of the tree but only with shared
  locks.
• intention-exclusive (IX) indicates explicit
  locking at a lower level with exclusive or shared
  locks
• shared and intention-exclusive (SIX) the subtree
  rooted by that node is locked explicitly in
  shared mode and explicit locking is being done at
  a lower level with exclusive-mode locks.

6
Solution New Lock Modes, Protocol

• Allow Xacts to lock at each level, but with a
  special protocol using new intention locks

• Before locking an item, Xact must set intention
  locks on all its ancestors.
• For unlock, go from specific to general (i.e.,
  bottom-up).
• SIX mode Like S IX at the same time.

7

• Parent Child can be
• locked in locked in
• IS
• IX
• S
• SIX
• X

P
IS, S IS, S, IX, X, SIX S, IS not necessary X,
IX, SIX none
C
8
Example
R1
t1
t4
t2
t3
9
Multiple Granularity Locking Scheme

• Transaction Ti can lock a node Q, using the
  following rules
• (1) Follow multiple granularity comp function
• (2) Lock root of tree first, any mode
• (3) Node Q can be locked by Ti in S or IS only if
  
• parent(Q) can be locked by Ti in IX or IS
• (4) Node Q can be locked by Ti in X,SIX,IX only
• if parent(Q) locked by Ti in IX,SIX
• (5) Ti uses 2PL
• (6) Ti can unlock node Q only if none of Qs
  
• children are locked by Ti
• Observe that locks are acquired in root-to-leaf
  order, whereas they are released in leaf-to-root
  order.

10
Examples
T1(IX)
T1(IS)
R
R
T1(IX)
t3
T1(S)
t4
t2
t1
t3
t4
t2
t1
T1(X)
f4.2
f4.2
f2.2
f2.1
f4.2
f4.2
f2.2
f2.1
T1(SIX)
Can T2 access object f2.2 in X mode? What locks
will T2 get?
R
T1(IX)
t3
t4
t2
t1
T1(X)
f4.2
f4.2
f2.2
f2.1
11
Examples

• T1 scans R, and updates a few tuples
• T1 gets an SIX lock on R, then repeatedly gets an
  S lock on tuples of R, and occasionally upgrades
  to X on the tuples that needs to update.
• T2 uses an index to read only part of R
• T2 gets an IS lock on R, and repeatedly gets an
  S lock on tuples of R.
• T3 reads all of R
• T3 gets an S lock on R.
• OR, T3 could behave like T2 can
• use lock escalation to decide which.

12
Examples

• T1 scans R, and updates a few tuples
• T1 gets an SIX lock on R, then repeatedly gets an
  S lock on tuples of R, and occasionally upgrades
  to X on the tuples.
• T2 uses an index to read only part of R
• T2 gets an IS lock on R, and repeatedly gets an S
  lock on tuples of R.
• T3 reads all of R
• T3 gets an S lock on R.
• OR, T3 could behave like T2 can
  use lock escalation to decide
  which.

13
Locking in B Trees

• How can we efficiently lock a particular leaf
  node?
• Btw, dont confuse this with multiple granularity
  locking!
• One solution Ignore the tree structure, just
  lock pages while traversing the tree, following
  2PL.
• This has terrible performance!
• Root node (and many higher level nodes) become
  bottleneck because every tree access begins at
  the root.

14
Two Useful Observations

• Higher levels of the tree only direct searches
  for leaf pages.
• For inserts, a node on a path from root to
  modified leaf must be locked (in X mode, of
  course), only if a split can propagate up to it
  from the modified leaf. (Similar point holds
  w.r.t. deletes.)
• We can exploit these observations to design
  efficient locking protocols that guarantee
  serializability even though they violate 2PL.

15
A Simple Tree Locking Algorithm

• Search Start at root and go down repeatedly, S
  lock child then unlock parent.
• Insert/Delete Start at root and go down,
  obtaining S locks and keep them. When is needed
  you obtain an X lock. Once child is locked,
  check if it is safe
• As you go, if child is safe, release all locks on
  ancestors.
• Safe node Node such that changes will not
  propagate up beyond this node.
• Inserts Node is not full.
• Deletes Node is not half-empty.

16
ROOT
Do 1) Search 38 2) Insert 45 3) Insert 25
A
20
B
35
C
F
38
44
23
H
D
E
G
I
20
22
23
24
35
36
38
41
44
17
A Better Tree Locking Algorithm

• Search As before.
• Insert/Delete
• Set locks as if for search, get to leaf, and set
  X lock on leaf.
• If leaf is not safe, release all locks, and
  restart Xact using previous Insert/Delete
  protocol.
• Gambles that only leaf node will be modified if
  not, S locks set on the first pass to leaf are
  wasteful. In practice, better than previous alg.

18
ROOT
Do 1) Search 38 2) Delete 38 3) Insert
45 4) Insert 25
A
20
B
35
C
F
38
44
23
H
D
E
G
I
20
22
23
24
35
36
38
41
44
19
Hybrid Algorithm

• The likelihood that we really need an X lock
  decreases as we move up the tree.
• Hybrid approach

Set S locks
Set SIX locks
Set X locks
20
Optimistic CC

• Locking is a conservative approach in which
  conflicts are prevented. Disadvantages
• Lock management overhead.
• Deadlock detection/resolution.
• Lock contention for heavily used objects.
• If conflicts are rare, we might be able to gain
  concurrency by not locking, and instead checking
  for conflicts before Xacts commit.

21
Timestamp-Based Protocols

• Idea
• Decide in advance ordering of xctions
• Ensure concurrent schedule serializes to serial
  order decided
• Timestamps
• TS(Ti) is time Ti entered the system
• Data item timestamps
• W-TS(Q) Largest timestamp of any xction that
  wrote Q
• R-TS(Q) Largest timestamp of any xction that
  read Q
• Timestamps -gt serializability order

22
Timestamp CC

• Idea If action pi of Xact Ti conflicts with
  action qj of Xact Tj, and TS(Ti) lt TS(Tj), then
  pi must occur before qj. Otherwise, restart
  violating Xact.

23
When Xact T wants to read Object O

• If TS(T) lt W-TS(O), this violates timestamp order
  of T w.r.t. writer of O.
• So, abort T and restart it with a new, larger TS.
  (If restarted with same TS, T will fail again!)
• If TS(T) gt W-TS(O)
• Allow T to read O.
• Reset R-TS(O) to max(R-TS(O), TS(T))
• Change to R-TS(O) on reads must be written to
  disk! This and restarts represent overheads.

U writes O
T reads O
T start
U start
24
When Xact T wants to Write Object O

• 1) If TS(T) lt R-TS(Q), then the value of Q that T
  is producing was needed previously, and the
  system assumed that that value would never be
  produced. write rejected, T is rolled back and
  restarts.
• 2) If TS(T) lt W-TS(Q), then T is attempting to
  write an obsolete value of Q. Hence, this write
  operation is rejected, and T is rolled back.
• 3) Otherwise, the write operation is executed,
  and W-TS(Q) is set to TS(T).

Another approach in 2) is to ignore the write and
continue!! Thomas Write Rule
U reads Q
T writes Q
T start
U start
25
Timestamp CC and Recoverability
T1 T2 W(A) R(A) W(B)
Commit

• Unfortunately, unrecoverable schedules are
  allowed

• Timestamp CC can be modified to allow only
  recoverable schedules
• Buffer all writes until writer commits (but
  update WTS(O) when the write is allowed.)
• Block readers T (where TS(T) gt WTS(O)) until
  writer of O commits.
• Similar to writers holding X locks until commit,
  but still not quite 2PL.

26
Validation-Based Protocol

• Execution of transaction Ti is done in three
  phases.
• 1. Read and execution phase Ti reads all
  values and makes copies to local variables. Ti
  writes only to temporary local variables. No
  locking.
• 2. Validation phase Transaction Ti performs a
  validation test''
• to determine if local variables can be
  written without violating
• serializability.
• 3. Write phase If Ti is validated, the
  updates are applied to the
• database otherwise, Ti is rolled back.
• optimistic concurrency control transaction
  executes fully in the hope that all will go well
  during validation

27
Validation-Based Protocol (Cont.)

• Each transaction Ti has 3 timestamps
• Start(Ti) the time when Ti started its
  execution
• Validation(Ti) the time when Ti entered its
  validation phase
• Finish(Ti) the time when Ti finished its write
  phase
• Serializability order is based on Validation(Ti).
• Key idea validation is atomic!

28
Validation-Based Protocol

• To implement validation, system keeps the
  following sets
• FIN transactions that have finished phase 3
  (and are all done)
• VAL transactions that have successfully
  finished phase 2 (validation)
• For each transaction the Read and Write Sets

29
Example of what validation must prevent

• RS(T2)B RS(T3)A,B
• WS(T2)B,D WS(T3)C

?
?
T2 start
T2 validated
T3 validated
T3 start
time
30
Example of what validation must allow
?

• RS(T2)B RS(T3)A,B
• WS(T2)B,D WS(T3)C

?
T2 start
T2 validated
T3 validated
T3 start
T2 finish phase 3
T3 start
time
31
Another thing validation must prevent

• RS(T2)A RS(T3)A,B
• WS(T2)D,E WS(T3)C,D

T2 validated
T3 validated
finish T2
finish T3
time
32
Another thing validation must allow

• RS(T2)A RS(T3)A,B
• WS(T2)D,E WS(T3)C,D

T2 validated
T3 validated
finish T2
finish T2
time
33
Validation rules for Tj

• (1) When Tj starts phase 1
• IGNORE(Tj) ? FIN
• (2) at Tj Validation
• if check (Tj) then
• VAL ? VAL U Tj
• do write phase
• FIN ? FIN U Tj

34
All transactions that either validated or
finished after the start of Tj

• Check (Tj)
• For Ti ? VAL - IGNORE (Tj) DO
• IF WS(Ti) ? RS(Tj) ? ? OR
• Ti ? FIN THEN
  RETURN false
• RETURN true

Is this check too restrictive ?
35
Improving Check(Tj)

• For Ti ? VAL - IGNORE (Tj) DO
• IF WS(Ti) ? RS(Tj) ? ? OR
• (Ti ? FIN AND WS(Ti) ? WS(Tj) ? ?)
• THEN RETURN false
• RETURN true

36
Example
start validate finish
U RS(U)B WS(U)D
W RS(W)A,D WS(W)A,C
V RS(V)B WS(V)D,E
T RS(T)A,B WS(T)A,C
U,T,V successful W abort and roll back
37
Serial Validation

• Multistage serial validation Validate in stages,
  at each stage validating T against a subset of
  the Xacts that committed after Begin(T).
• Only last stage has to be inside critical
  section.
• Space for WriteSets To validate Tj, must have
  WriteSets for all Ti where Ti lt Tj and Ti was
  active when Tj began. There may be many such
  Xacts, and we may run out of space.
• Tjs validation fails if it requires a missing
  WriteSet.
• No problem if Xact ids assigned at start of Read
  phase.

38
Overheads in Optimistic CC

• Must record read/write activity in ReadSet and
  WriteSet per Xact.
• Must create and destroy these sets as needed.
• Must check for conflicts during validation, and
  must make validated writes global.
• Critical section can reduce concurrency.
• Scheme for making writes global can reduce
  clustering of objects.
• Optimistic CC restarts Xacts that fail
  validation.
• Work done so far is wasted requires clean-up.

39
Optimistic 2PL

• If desired, we can do the following
• Set S locks as usual.
• Make changes to private copies of objects.
• Obtain all X locks at end of Xact, make writes
  global, then release all locks.
• In contrast to Optimistic CC as in Kung-Robinson,
  this scheme results in Xacts being blocked,
  waiting for locks.
• However, no validation phase, no restarts (modulo
  deadlocks).

40
Transaction Support in SQL-92

• Each transaction has an access mode, a
  diagnostics size, and an isolation level.