February 15, 2006
Coverage: [DBCB] Chapter 18, pp. 917-921
Non-serializable Schedules
- Example (courtesy [DBCB] figure 18.6, p. 922):
consistency: A = B
| T1 | T2 | A | B |
| | 25 | 25 |
| READ(A,t) | | | |
| t := t+100 | | | |
| WRITE(A,t) | | 125 | |
| READ(A,s) | | |
| s := s*2 | | |
| WRITE(A,s) | 250 | |
| READ(B,s) | | |
| s := s*2 | | |
| WRITE(B,s) | | 50 |
| READ(B,t) | | | |
| t := t+100 | | | |
| WRITE(B,t) | | | 150 |
- every serial schedule is serializable
- there are serializable schedules which are not serial
- there are schedules which are not serializable and they must be avoided
Minor Modification, Enormous Effect
-
Example (courtesy [DBCB] example 18.4, p. 922-923):
consistency: A = B
| T1 | T2 | A | B |
| | 25 | 25 |
| READ(A,t) | | | |
| t := t+100 | | | |
| WRITE(A,t) | | 125 | |
| READ(A,s) | | |
| s := s*1 | | |
| WRITE(A,s) | 125 | |
| READ(B,s) | | |
| s := s*1 | | |
| WRITE(B,s) | | 25 |
| READ(B,t) | | | |
| t := t+100 | | | |
| WRITE(B,t) | | | 125 |
- details of each action can have a profound effect
on the preservation of consistency
- unfortunately scheduler cannot concern itself with the
details of each individual action
- sometime operations are not transparent to scheduler
- consider use of general-purpose programming languages
- scheduler does see reads and writes
and thus is aware of what might change
- scheduler assumes the worst
A Shorthand Notation
- READ(A,t); t:= t+100; WRITE(A,t) =>
r_T(X); w_T(X) => r_i(X); w_i(X)
- T1: r_1(A); w_1(A); r_1(B); w_1(B);
- T2: r_2(A); w_2(A); r_2(B); w_2(B);
- omit local variables
- again, we assume the worst
Algorithm to Detect a Serial Schedule
- use linear search
- sound and complete
- sufficient and necessary
Bernstein's Conditions
In general, conflicts do not arise between adjacent actions
unless
- they access the same data element, AND
- at least one is a write
Algorithm to Detect a Serializable Schedule
- swap non-conflicting adjacent actions until you arrive at
some serial schedule
- Example (courtesy [DBCB] example 18.6, pp. 926):
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B) w2(B)
r1(A) w1(A) r2(A) r1(B) w2(A) w1(B) r2(B) w2(B)
r1(A) w1(A) r2(A) r1(B) w2(A) w1(B) r2(B) w2(B)
r1(A) w1(A) r1(B) r2(A) w2(A) w1(B) r2(B) w2(B)
r1(A) w1(A) r1(B) r2(A) w1(B) w2(A) r2(B) w2(B)
r1(A) w1(A) r1(B) w1(B) r2(A) w2(A) r2(B) w2(B)
- two schedules are said to be conflict equivalent if
you can transform one into the other using the swapping algorithm
- if one is a serial schedule, the other is referred to
as conflict-serializable schedule
- same effect does not mean correct effect
- sound, but incomplete
- sufficient, but is it necessary?
- we would like a sufficient and necessary algorithm,
- but
better to settle for `a sufficient, but unnecessary' algorithm than
`an insufficient, but necessary' one
- too rigid
- Jim Gray's contribution: said some serializable schedules will go undetected
Conflict-Serializable Schedule Undetectable by Swapping
(courtesy [DBCB])
- transactions containing only writes, no reads
| T1 |
T2 |
T3 |
| w1(X) |
w2(X) |
w3(Y) |
| w1(Y) |
w2(Y) |
- serial schedule: T1T2T3 = w1(X) w1(Y) w2(X) w2(Y) w3(Y)
- serializable schedule: w1(X) w2(X) w2(Y) w1(Y) w3(Y)
- w2(Y) blocks the w1(Y) from moving left
- w1(Y) blocks the w2(Y) from moving right
A Better Method for Identifying Conflict-Serializable Schedules
- swapping works, but is so indefinite
- instead, create a precedence graph
- vertices are transactions
- directed edge from T1 to T2
indicates that some operation in T1 must
take precedence over some operation in T2
- search for a cycle in it
- no cycles => conflict-serializable
- at least one cycle => not conflict-serializable
- presence of a cycle implies a contradiction in the constraints
- how to search for a cycle?
- do a topological sort
- used in software project management
- only exists if graph has no cycles (i.e., is acyclic)
- use depth-first search (DFS)
- O(|V| + |E|), assuming an adjacency-list representation
- any topological order of the vertices in a conflict-equivalent
serial order
Building Precedence Graphs
Example (courtesy [DBCB] example 18.7, p. 928):
- schedule: r2(A) r1(B) w2(A) r3(A) w1(B) w3(A) r2(B) w2(B)
- intuition: r2(A) precedes w3(A), w2(A)
precedes both r3(A) and w3(A), and
r2(B) precedes w2(B)
- precedence graph:
(courtesy [DBCB] Fig. 18.9, p. 928)
- Example (courtesy [DBCB] example 18.8, p. 929):
- schedule: r2(A) r1(B) w2(A) r2(B) r3(A) w1(B) w3(A) w2(B)
- intuition: r2(A) precedes w3(A), w2(A)
precedes both r3(A) and w3(A), and
r2(B) precedes w2(B), but r2(N) precedes w2(B)
- precedence graph:
(courtesy [DBCB] Fig. 18.10, p. 929)
Relationship of Various Types of Schedules
Summary
- a conflict-serializable schedule is conflict-equivalent to
some serial schedule
- conflict-serializability is a stronger condition than serializability
- most schedulers err on the side of caution and require
conflict-serializability
| Algorithm to |
Sound? |
Complete? |
Sufficient? |
Necessary? |
| detect a serial schedule |
yes |
yes |
yes |
yes |
| detect a serializable schedule: swapping algorithm |
yes |
no |
yes |
no |
| detect a conflict-serializable schedule |
yes |
yes |
yes |
yes |
References
[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems:
The Complete Book. Prentice Hall, 2002.
