Sunday, 15 April 2018

Recoverable schedules in database transaction

Recoverable schedules in database transaction

A schedule is recoverable if every transaction in that
schedule commits only after all the transactions whose changes they read have
committed.

A schedule where, for each pair of transactions Ti
and Tj, if Tj reads a data item previously written by Ti,
then the commit operation of Ti precedes the commit operation of Tj.

For example, assume a schedule S with two transactions T1,
and T2. If T2 consumes a data item written by T1
then T1 must commit before T2. If this happens then the
schedule S is said to be recoverable schedule.

If a schedule should be recoverable, then there shouldn’t
be any dirty reads [reading of uncommitted data].

Example:

Is the following transaction is recoverable or not?

Instruction

T1

T2

1

2

3

4

5

6

R(x)

W(y)

Commit

R(x)

W(y)

Commit

This schedule is recoverable because there are no dirty
reads. Here, T1 commits before T2 but T2 is
not consuming any data that is written by T1. Hence, this schedule
is recoverable. Also, the order of commit is not an issue as neither T1
nor T2 has consumed data written by the other transactions.