CS245 PROBLEM SET #5 Solutions

Problem 1: (20 points)

In the table below you are given the contents of the log and the state of (a part of) the database after several system crashes. For each case, indicate what type of logging might have been used.

case	A	B	Log
1	6	0	[Start T],[T,A,5],[Commit T]
2	6	5	[Start T],[T,A,6],[T,B,6],[Commit T]
3	6	6	[Start T],[T,A,6],[T,B,6],[Commit T]
4	5	5	[Start T],[T,A,6],[T,B,6]
5	5	5	[Start T],[T,A,6],[T,B,6],[Abort T]

Your answer, for each case, should be one of the following:

• Redo
• Undo
• Either
• Neither

Briefly, explain you reasoning.

Solution:

We assume T does not write the same value it reads.

1. Either. Might be Redo with changes not yet written to disk or Undo with changes written to disk.
2. Redo. Might be Redo with some changes written to disk. With Undo the new value of A must have been written to disk before [COMMIT T] is written to disk.
3. Redo. Might be Redo with changes written to disk. With Undo the new values must have been written to disk before [COMMIT T] is written to disk.
4. Either. Might be Redo with changes not yet written to disk or Undo with changes written to disk.
5. Redo. Might be Redo with all values restored on disk. With Undo the values of A and B must have been restored to 5 before [ABORT T] is written to disk.

Problem 2: (20 points)

Consider the following transaction log from the start of the run of a database system that is capable of running undo/redo logging with checkpointing:

1)  <START T1>
2)  <T1, A, 50, 25>
3)  <T1, B, 250, 25>
4)  <START T2>
5)  <T1, A, 75, 50>
6)  <T2, C, 55, 25>
7)  <COMMIT T1>
8)  <START T3>
9)  <T3, E, 65, 25>
10) <T2, D, 35, 25>
11) <START CKPT (T2,T3)>
12) <T2, C, 45, 55>
13) <COMMIT T2>
14) <START T4>
15) <T4, F, 120, 25>
16) <COMMIT T3>
17) <END CKPT>
18) <T4, F, 150, 120>
19) <COMMIT T4>

1.    if the system crashes just before line 10 is written to disk?
2.    if the system crashes just before line 13 is written to disk?
3.    if the system crashes just before line 14 is written to disk?
4.    if the system crashes just before line 19 is written to disk?
5.    if the system crashes just after line 19 is written to disk?

Solution:

1.

A     B     C     D     E     F
==================================
75    250   25    25    25    25                


2.

A     B     C     D     E     F
==================================
75    250   25    25    25    25                


3.

A     B     C     D     E     F
==================================
75    250   45    35    25    25        


4.

A     B     C     D     E     F
==================================
75    250   45    35    65    25        


5.

A     B     C     D     E     F
==================================
75    250   45    35    65    150       

Problem 3: (40 points)

See HW #4 for the problem.

Solution:

Procedure Recovery()
  Begin
    << obtain free pages >>
      px <- a free page in main memory;
      py <- a free page in main memory;
    << check all blocks in the log >>  
      For i = 1 to n do
        << get the log block >>
          Input( a(i) -> px );
        << check if block is valid >>    
          If ( px(1) > 0 )
          Begin
            << get number of modifications to do >>
              m <- px(2);
            << loop for each modification >>
              For j = 1 to m do
                (p, b, r, v) <- px(j+2);
              << get the block to be modified >>
                Input( b -> py );
              << make the change >>
                py(r) <- v;
              << output that block >>
                Output( py -> b );
              End For
            << clear page >>
              px <- all zeros;  
            << zero out the corresponding log block >>
              Output( px -> a(i) );
          End If
        << Free up that position in the MAP >>
          MAP( i ) <- 0
      End For
      Release page px, py ;
  End;

Note: The bit array MAP needs to be restored after recovery. Many students forgot this step.