Important facts about database schedules in DBMS

Important facts about database schedules in DBMS, Recoverable schedule, non-recoverable schedules, serializable schedule, non-serializable schedule, properties of database schedules

Important facts about database serializability and schedules

• Serializable schedule - A schedule is serializable if the execution of all instructions in the schedule is equivalent to a serial schedule. That is, if the database instance that results from executing the given schedule is identical to the database instance that result from executing the transactions in some serial order.

• Serial schedule – It is a schedule in which the complete set of instructions of one transaction is completed before the other. That is, no interleaved instructions present in the schedule.

• Example: serial schedule

Transaction 1	Transaction 2
Read(A) Write(A)	Read(A) Write(A)

• Recoverable schedule – It is a schedule where each transaction commits only after all transactions from which it has read has committed.

• What is recoverable schedule? – We will be able to restore the database to a consistent state after failure.

• A schedule can be both serializable and recoverable

Schedule S1
Transaction 1	Transaction 2
Read(A) Write(A) Commit	Read(A) Write(A) Commit

• Schedule S₁ is serializable because all the instructions are executed in a serial order (first T₁ then T₂). It is recoverable because T₁ commits before T₂.

• A schedule can be serializable and not recoverable

Schedule S2
Transaction 1	Transaction 2
Write(A) Write(B) Commit	Write(A) Read(B) Commit

• Schedule S₂ is serializable because all the instructions are executed in a serial order (first T₁ then T₂). It is not recoverable because T₂ commits before T₁. Actually, the read operation Read(B) is a dirty read, that is, reading a value which is produced by another transaction which is not yet committed.

• What is non-recoverable schedule? – We may not be able to recover to a consistent database state after a failure happened.

• A schedule can be not serializable and recoverable

Schedule S3
Transaction 1	Transaction 2
Write(B) Write(A) Commit	Write(A) Read(B) Commit

• We can make S₃ into two possible serial schedules as follows;

• 1: W₁(B); W₁(A); W₂(A); R₂(B);

• 2: W₂(A); R₂(B); W₁(B); W₁(A);

• Schedule S₃ is not equivalent to serial schedule 1. In S₃, T₁ writes the final value of A whereas in serial schedule 1, T₂ writes the final value of A.

• Schedule S₃ is not equivalent to serial schedule 2. In S₃, T₂ consumed the value of data item B which was written by T₁ whereas in serial schedule 2, T₂ read the value of data item B which was written by another transaction part of another old schedule.

• Due to these reasons, S₃ is not serializable.

• Though the transaction T₂ performs a dirty read in S₃, it commits well after T1 commits. Hence, the schedule S₃ is recoverable.

• A schedule can be not serializable and not recoverable

• Swapping commit instructions in schedule S₃ will result in an example for this type.

***********

Go to Transaction management in DBMS page

Go to Important keywords in DBMS page

Go to Why do we need recoverable schedules page 

Go to Define cascadeless schedules page

Recoverable schedules in concurrent schedules

what is recoverable schedule

define recoverable schedules

recoverable schedules example

how to identify a recoverable schedule

check for recoverability in concurrent schedules

serializable and recoverable schedule
serializable and not recoverable schedule
not serializable and recoverable schedule
not serializable and not recoverable schedule