Chapter 7: Relational Database Design

1
Concurrency control II
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-based locking protocols

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
MGL multiple granularity locking

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

13
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
  (private workspace.) 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

14
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!

15
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

16
Example of what validation must prevent

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

?
?
T1 start
T1 validated
T2 validated
T2 start
time
T2 validation will fail!
17
Example of what validation must allow
?

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

?
T1 start
T1 validated
T2 validated
T2 start
T1 finish phase 3
T2 start
time
18
Another thing validation must prevent

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

T1 validated
T2 validated
finish T1
finish T2
time
19
Another thing validation must allow

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

T1 validated
T2 validated
finish T1
finish T1
time
20
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

21
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 ?
22
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

23
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
24
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

25
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.

26
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 (log)! This and restarts represent
  overheads.

U writes O
T reads O
T start
U start
27
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
28
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.