Thursday, 2 April 2020

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 S1 is serializable because all the instructions are executed in a serial order (first T1 then T2). It is recoverable because T1 commits before T2.



  • 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 S2 is serializable because all the instructions are executed in a serial order (first T1 then T2). It is not recoverable because T2 commits before T1. 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 S3 into two possible serial schedules as follows;
      • 1: W1(B); W1(A); W2(A); R2(B);
      • 2: W2(A); R2(B); W1(B); W1(A);
    • Schedule S3 is not equivalent to serial schedule 1. In S3, T1 writes the final value of A whereas in serial schedule 1, T2 writes the final value of A.
    • Schedule S3 is not equivalent to serial schedule 2. In S3, T2 consumed the value of data item B which was written by T1 whereas in serial schedule 2, T2 read the value of data item B which was written by another transaction part of another old schedule.
    • Due to these reasons, S3 is not serializable.
    • Though the transaction T2 performs a dirty read in S3, it commits well after T1 commits. Hence, the schedule S3 is recoverable.

  • A schedule can be not serializable and not recoverable
    • Swapping commit instructions in schedule S3 will result in an example for this type.


***********







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




No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents