System restarted before transaction is committed

Question

Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest.

T1 start 
T1 B old=1200 new=10000
T1 M old=0 new=2000
T1 commit
T2 start
T2 B old=10000 new=10500
T2 commit

Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?

(a) We must redo log record 6 to set B to 10500

(b) We must undo log record 6 to set B to 10000 and then redo log records 2 and 3

(c) We need not redo log records 2 and 3 because transaction T1 has committed

(d) We can apply redo and undo operations in arbitrary order because they are idempotent

According to me, the answer should be (a), as transaction T1 has been committed and has been written back into memory. But my solution says that the answer should be (b).

Am I right or what am I doing wrong?

Thanks in advance!

Answer 1

The correct answer is (b) under the assumption that the recovery algorithm used is the undo-redo algorithm, which is one of the most commonly used, and which can be used if the DBMS adopts both the immediate update policy and the immediate commit policy (instead of the deferred update and the deferred commit policies).

The immediate update policy allows that updated pages can be written to the database before the transaction has committed; The immediate commit policy allows the commit record to be written to the log before all updated pages have been written to the database. Those policies require the use of Write Ahead Log (WAL) rule (write the updates to the log file before writing to disk) and of Commit rule (the after-images of the updates are written to the log file before the commit of the transaction).

Under this hypothesis, when a failure occurs, the recovery algorithm must undo all the transactions non committed, and redo all the transaction committed, going back in the log file until a checkpoint record is found or until the beginning of the log file.

So, the correct answer is (b) since also T1 must be redone. In fact, we do not know if its updates to the database have been written or not (as I said, assuming that we are in the case of immediate commit policy).

Answer 2

Regardless of the commit policy of the database, (a) cannot be right. After the restart, the log contains

T2 start
T2 B old=10000 new=10500

Since there is no commit record for T2, there is no way to know that T2 was finished. Maybe T2 should have included another part whose record was not written. Therefore it cannot be correct to perform any part of T2.