- Serializable schedule:
- A schedule S
is serializable schedule
iff:
- There is
a serial schedule S'
such that:
- The effect of the execution of S and S' are identical for every DB state
I.e.:
- Starting in
any DB state,
executing
the schedules
S/S'
will
result in the
same DB state
- There is
a serial schedule S'
such that:
- A schedule S
is serializable schedule
iff:
- Example:
serializable schedule
- Effect of a
serial schedule:
T1 T2 A B ======================================================= | READ(A, t) c c | t = t + 100 | WRITE(A, t) --------------------> c+100 | READ(B, t) | t = t + 100 | WRITE(B, t) ------------------------------> c+100 | READ(A, s) | s = 2×s | WRITE(A, s) ----> 2c+200 | READ(B, s) | s = 2×s | WRITE(B, s) -------------> 2c+200 V time Final state: 2c+200 2c+200
- Interleaved execution:
T1 T2 A B ======================================================= | READ(A, t) c c | t = t + 100 | WRITE(A, t) --------------------> c+100 | READ(A, s) | s = 2×s | WRITE(A, s) ----> 2c+200 | READ(B, t) | t = t + 100 | WRITE(B, t) ------------------------------> c+100 | READ(B, s) | s = 2×s | WRITE(B, s) -------------> 2c+200 V time Final state: 2c+200 2c+200
Observe that:
- The final state of
the interleaved execution (schedule)
is equivalent to the
final state of a
serial schedule
- The interleaved schedule is
therefore:
- serializable
- Effect of a
serial schedule:
- Consider the following schedule:
T1 T2 ================================= READ(A, t) t = t + 100 WRITE(A, t) READ(A, s) s = 2×s WRITE(A, s) READ(B, s) s = 2×s WRITE(B, s) READ(B, t) t = t + 100 WRITE(B, t)
- The execution will
result in an
inconsistent database state:
T1 T2 A B ======================================================= | READ(A, t) c c | t = t + 100 | WRITE(A, t) --------------------> c+100 | | READ(A, s) | s = 2×s | WRITE(A, s) ----> 2c+200 | | READ(B, s) | s = 2×s | WRITE(B, s) -------------> 2c | | READ(B, t) | t = t + 100 | WRITE(B, t) ------------------------------> 2c+100 V timeAt the end of the execution, we have:
A = 2c + 100 B = c + 100 (A ≠ B, which is not possible in a serial execution of T1 and T2)Therefore:
- The (interleaved) schedule is not serializable !!!