- Serializable schedule:
- A schedule is
serializable if:
- the effect of the execution of the actions in the schedule is equivalent to a serial schedule
(We also looked at a stronger notion of serializability known as conflict serializability)
- A schedule is
serializable if:
- Recoverable schedule:
- A schedule is
recoverable if:
- each transaction commits only after all transactions from which it has read has committed
Graphically:
The transaction Tk must commit only after the transactions T1, T2 and T3 have committed
- A schedule is
recoverable if:
- Observation:
- In a recoverable schedule,
a transaction Tk
can only
commit if:
- transaction Tk has not read (= used) dirty data
- In a recoverable schedule,
a transaction Tk
can only
commit if:
- Important fact:
- If a schedule is
recoverable, then:
- A logging technique (undo, redo or undo/redo) will restore the database to a consistent state !!!
(Because each transaction will have used consistent data)
- If a schedule is
recoverable, then:
- We
introduce a
new action into the
schedule:
- ci = indicates that the transaction Ti commits
Previously, we have already defined:
- ri(A) = the transaction Ti reads the DB element A
- wi(A) = the transaction Ti writes the DB element A
- Example of
recoverable schedule:
- Consider the
following schedule:
- Transaction T2 has
used (read) dirty data
written by
transaction T1:
- In order for the
schedule to be a
recoverable schedule:
- T1 must commit before T2 !!!
Let's check in the schedule:
Verified !!!
Therefore:
- The schedule is recoverable.
- Consider the
following schedule:
- Important fact:
-
The concepts:
- Serializability and
- Recoverability
are orthogonal concepts !!!!
-
The concepts:
- In other words:
- A schedule can be:
- not serializable and not recoverable
- Not serializable and recoverable
- Serializable and not recoverable
- Serializable and recoverable
- A schedule can be:
- Our goal:
- Identify all serializable and recoverable schedules !!!
- Example of a
serializable
and
recoverable
schedule:
- Schedule S1:
- S1 is
serializable because:
- Schedule S1 is
a serial schedule:
(The criteria for serializabilty does not care about the commit ordering of transactions !!!)
- Schedule S1 is
a serial schedule:
- Schedule S1 is
recoverable because:
- Transaction T2 has
used data
written by
the (uncommitted) transaction
T1:
- And transaction T2
the commits
after
the
transaction T1
The schedule satisfies the definition of a recoverable schedule
- Transaction T2 has
used data
written by
the (uncommitted) transaction
T1:
- Schedule S1:
- Example of a
non-serializable
and
recoverable
schedule:
- Schedule S2:
- S2 is
not serializable because:
- There are 2 possible
serial schedules:
Question:
- Is schedule S2 equivalent to one of the serial schedules ???
- Schedule 2 is
not equivalent to
serial schedule #1 because:
- In
schedule S2,
transaction T1
made the final update to
DB element A:
But in the serial schedule #1, transaction T2 made the final update to DB element A:
Therefore, schedule S2 is not equivalent to the serial schedule #1
- In
schedule S2,
transaction T1
made the final update to
DB element A:
- Schedule 2 is
not equivalent to
serial schedule #2 because:
- In
schedule S2,
transaction T2
used DB element B that was
written by
transaction T1:
But in serial schedule #2, transaction T2 used B that was written by another (the previous) transaction:
Therefore, schedule S2 is not equivalent to the serial schedule #2
- In
schedule S2,
transaction T2
used DB element B that was
written by
transaction T1:
- Therefore:
- There is no equivalent
serial schedule and thus...
Schedule S2 is not serializable !!!
- There is no equivalent
serial schedule and thus...
- There are 2 possible
serial schedules:
- Schedule S2 is
recoverable because:
- Transaction T2
has used dirty data from
the uncommitted transaction
T1:
- And transaction T2
the commits after
the transaction
T1:
Schedule S2 satisfies the definition of a recoverable schedule.
- Transaction T2
has used dirty data from
the uncommitted transaction
T1:
Notice that:
- The schedule S2 is
non-serializable
That means:
- The resulting (final) DB state after executing schedule S2 is an inconsistent DB state !!!
- Schedule S2:
-
What is
the effect of a
recoverable but
not-serializable
schedule:
-
A recoverable schedule
means:
- We can restore the database to a state that was achieved by committed transactions
- In a recoverable
but not-serializable schedule:
- We will (unfortunately)
recover the
database to
the state that
was
achieved by
a
non-serializable execution
(achieved by the
committed transactions)
!!!
(We will recover to the inconsistent state achieve by the execution of the non-serializable schedule S2)
- We will (unfortunately)
recover the
database to
the state that
was
achieved by
a
non-serializable execution
(achieved by the
committed transactions)
!!!
-
A recoverable schedule
means:
- Example of a
serializable
and
non-recoverable
schedule:
- Schedule S3:
- Schedule S3 is
serializable because:
- Schedule S3 is
a serial schedule:
(A serial schedule is always serializable)
- Schedule S3 is
a serial schedule:
- Schedule S3 is
not recoverable because:
- Transaction T2
has
used (read) dirty data
from
the uncommitted transaction
T1
However: transaction T2 the commits before the transaction T1:
This violates the definition of a recoverable schedule
- Transaction T2
has
used (read) dirty data
from
the uncommitted transaction
T1
- Schedule S3:
- Effect of a
serializable but
non recoverable schedule:
- A serializable schedule
means that:
- When there is no system failures, the (serializable) schedule will completes/results in consistent database state
- A non recoverable schedule means:
- When there is a system failures, we may not be able to recover to a consistent database state
Example:
- Suppose the
system
crashes after
c2:
Notice that:
- Transaction T2 has used (read) dirty data written by transaction T1
- During recovery,
the uncommitted transaction
T1
will be rolled back (= undone):
The dirty data is illegal (= an inconsistent DB state)
Therefore, the transaction T2 has used (= read) a non-existing (inconsistent) value in its execution:
Result:
- The DB state after the recovery operation (using any logging technique) will be an inconsistent database state !!!
- A serializable schedule
means that: