February 22, 2006

Coverage: [DBCB] Chapter 18, pp. 932-946

Demo of COMMON Lisp Code Implementing Greedy Algorithm for Join Ordering

How to Enumerate the Powerset of a Set?

helpful in implementing the dynamic programming algorithm for determining optimal join order

code in SML

fun insert_in_each_set (a, nil) = nil
|   insert_in_each_set (a, x::xs) = (a::x)::insert_in_each_set (a, xs);

fun powerset(nil) = [nil]
|   powerset(x::xs) =
       let
           val powersettail = powerset (xs)
       in
           powersettail@insert_in_each_set (x, powersettail)
       end;

notice :: associates right to left

an interactive session:

val it = [[]] : ?.X1 list list
- powerset ([1]);
val it = [[],[1]] : int list list
- powerset ([1,2]);
val it = [[],[2],[1],[1,2]] : int list list
- powerset ([1,2,3]);
val it = [[],[3],[2],[2,3],[1],[1,3],[1,2],[1,2,3]] : int list list
- powerset ([1,2,3,4,5]);
val it =
  [[],[5],[4],[4,5],[3],[3,5],[3,4],[3,4,5],[2],[2,5],[2,4],[2,4,5],...]
  : int list list

Counting Various Types of Schedules

a combinatorial problem
given a set of transactions, count the total number of
- schedules
- serial schedules
- serializable schedules
- conflict-serializable schedules
what assumptions do you make?
example
- consider the following transactions
  - T1: r_1(A,t) t+=2 w_1(A,t) r_1(B,t) t*=3 w_1(B,t)
  - T2: r_1(B,s) s*=2 w_2(B,s) r_2(A,s) s+=3 w_2(A,s)
- notice that the order in which each transaction modifies each data element is irrelevant to the preservation of database consistency, why?
- number of schedules: 8C4
  - why is 8! incorrect?
- number of serial schedules: 2
- number of serializable schedules: ?
- number of conflict-serializable schedules: ?

Recap

end-goal: a `legal schedule' of `consistent transactions' that is conflict-serializable
conflict-serializability is not a test for serializability
is there a test for conflict-serializability?
- yes, build a precedence graph and try to detect a cycle
how is conflict-serializability enforced?
- locks

Review of Two-Phase Locking

a greedy approach to enforcing conflict-serializability
- analogy to checking out all the books you will ever need throughout the entire school year from the library in August!
schematic:
(courtesy [DBCB] Fig. 18.14, p. 937)
- called an envelope
  - analogy to `music envelopes'
- 2PL schedule can only have this shape
schedules with envelopes like the following are not allowed:
necessary and sufficient condition for producing a conflict-serializable schedule
- but only sufficient for serializability

Schematic of Deadlock (Automobile Analogy)

(adapted from [OSIDP] Fig. 6.1(b), p. 256)

Is Two-Phase Locking Foolproof?

of course not
what could go wrong?
- deadlock
  
  T1 T2
  
  l_1(A)
  
  l_2(A)
  
  r_1(A)
  
  w_1(A)
  
  r_2(A)
  
  w_2(A)
  
  l_1(B) DENIED!
  
  l_2(A) DENIED!
solution: introduce different types of locks

Types of Locks

main idea: we want locks, as opposed to some algorithm, to determine if deadlock is possible
read lock (shared lock)
- denoted with `S'
- permits concurrent reads
write lock (exclusive lock)
- denoted with `X'
now a simple relation, such as LockTable, does not suffice to manage locks
compatibility matrix for shared and exclusive locks

Lock requested

Lock held

S X

S yes no

X no no
- notice A=A^T, i.e., A equals A transpose
- gives rise to deadlock
  - solution: introduce an update lock
    - solution to deadlock created out of 2PL
compatibility matrix for shared, exclusive, and update locks

Lock requested

Lock held

S X U

S yes no yes

X no no no

U no no no
- notice now A!=A^T, i.e., A does not equal A transpose

Summary

a 2PL schedule does not preclude deadlock
types of locks
- read lock
- write lock
- update lock
granularity of locks
- tuples or whole relations?
2 jobs of scheduler
- insert locks
- order actions

References

Database Systems: The Complete Book

Operating Systems: Internals and Design Principles

T1	T2

l_1(A)
	l_2(A)
r_1(A)
w_1(A)
	r_2(A)
	w_2(A)
l_1(B) DENIED!
	l_2(A) DENIED!