GATE CSE 2006 | Question: 20, ISRO2015-17

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. 

1. T1 start 
2. T1 B old $=12000$ new $=10000$
3. T1 M old $=0$ new $=2000$
4. T1 commit
5. T2 start
6. T2 B old $=10000$ new $=10500$
7. 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

Comments

https://drive.google.com/file/d/1bPUx7LD3pW0rBiQa4bs_el2CS6SYjg7C/view

---

Steps after database crashes:-

Step 1) UNDO the uncommitted transactions. Even UNDO the uncommitted records which are before the checkpoint. UNDO happens in reverse order (from bottom to top).

Step 2) REDO the committed transactions. No need to REDO the records which are already committed before the last checkpoint. REDO happens in original order (from top to bottom).

With these simple steps any questions on deferred database (recovery) can be solved very easily.

Similar qs:-

Databases: GATE CSE 2015 Set 2 | Question: 46 (gateoverflow.in)

Databases: GATE CSE 2021 Set 1 | Question: 13 (gateoverflow.in)

---

@Abhrajyoti00 Great Man :) 

Answer 1

For recovery, we'll be having 2 choices, either Deferred updation or Immediate updation techniquee.

Now If we take Deferred Updation which says only REDO and no UNDO then no option is matching.Then we'll take Immediate Updation, which is having two choices further as UNDO/No-REDO and UNDO/REDO.

If we follow UNDO/No-REDO then we'll have to UNDO active transaction T2 only (no REDO on T1 as changes are immediate to DB and T1 already committed).Again no option matches with this choice.

So we'll follow UNDO/REDO which says that all committed transactions up to checkpoint need not be REDO, committed but not checkpointed are need to be REDO and active transaction need to be UNDO.Hence T2 needs UNDO and then T1 needs REDO. Thus, answer is option B

Comments

"Now If we take Deferred Updation which says only REDO and no UNDO then no option is matching."  Here how option A is not matching? 

---

Okay. I think I got it. Is it because if it was deferred then record 2 and 3 needed to be redone and not record 6 ?

---

@MiNiPanda, Yes, you are ryt...and REDO must be applied to committed operations only...

Answer 2

The log file given in question is example of  type deferred update mode. In this case, old value and new value of a variable is stored. If a transactions is not committed, its operations need to be undone and if a transaction is committed, its operations need to be redone. But first we need to undone the uncommitted transaction and then redone the committed transaction. Because if we undo the uncommitted transaction later, the value updated by committed transaction may be lost. So the correct option is B.

Source :https://www.techtud.com/example/recovarability-using-log-files

Good explanation

Answer 3

Up until the checkpoint, do nothing.

For the logs after the checkpoint (which is the case here, we see no checkpoints) undo all uncommitted transactions, and redo all committed transactions.

A is wrong, because we have to undo record 6, not redo.

B is correct.

C is wrong, because we redo committed transactions that are not checkpointed. (We see no checkpoint here)

D is just extremely stupid. Would lead to race condition / data inconsistency.

Answer 4

Points to be noted:

1. Commit Transfers he changes to redo Log Buffer and 
2. Checkpoint stores whatever present in the “Redo Log Buffer” to “Disk Blocks” Therefore T1 committed and we can perform redo and T2 has to undo.