---

CS554 - Advanced Database Systems
Homework 8

---

• Question 1 (Exercise 17.2.4) --- 15 pts
  1. The following is a sequence of undo-log records written by two transactions T and U:

     <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> <T, E, 50> <C0MMIT T>

     Describe the action of the recovery manager by stating which database elments will be updated with which value if there is a crash and the last log record to appear on disk is:

     <START U> ---- 3 pts <C0MMIT U> ---- 4 pts <T, E, 50> ---- 4 pts <C0MMIT T> ---- 4 pts

  ---

  ---

  ---

  ---

• Question 2: Undo logging (Exercise 17.2.7) --- 15 pts
  1. Suppose that we begin a nonquiescent checkpoint at the position given in the following undo log:

     <START S> <S, A ,60> <START CHKT(S)> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50> <C0MMIT T> <V ,B , 80>

     Questions: --- (3 pts total)

     Indicate in the above figure the location where the <END CKPT> record will be written If the <END CKPT> record cannot have been written, state this as your answer Suppose we found the log in the above state; i.e.: the <END CKPT> was not written Indicate in the above figure the further location back in the log that we must look to find all incomplete transactions when a crash occurs.

     ---

     ---

  2. Suppose that we begin a nonquiescent checkpoint at the position given in the following undo log:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START CHKT(T)> <START U> <U, B, 20> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50> <C0MMIT T> <V ,B , 80>

     Questions: --- (4 pts total)

     Indicate in the above figure the location where the <END CKPT> record will be written If the <END CKPT> record cannot have been written, state this as your answer Suppose we found the log in the above state; i.e.: the <END CKPT> was not written Indicate in the above figure the further location back in the log that we must look to find all incomplete transactions when a crash occurs.

     ---

     ---

  3. Suppose that we begin a nonquiescent checkpoint at the position given in the following undo log:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <START CHKT(T,U)> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50> <C0MMIT T> <V ,B , 80>

     Questions: --- (4 pts total)

     Indicate in the above figure the location where the <END CKPT> record will be written If the <END CKPT> record cannot have been written, state this as your answer Suppose we found the log in the above state; i.e.: the <END CKPT> was not written Indicate in the above figure the further location back in the log that we must look to find all incomplete transactions when a crash occurs.

     ---

     ---

  4. Suppose that we begin a nonquiescent checkpoint at the position given in the following undo log:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <START CHKT(T,V)> <T ,E , 50> <C0MMIT T> <V ,B , 80>

     Questions: --- (4 pts total)

     Indicate in the above figure the location where the <END CKPT> record will be written If the <END CKPT> record cannot have been written, state this as your answer Suppose we found the log in the above state; i.e.: the <END CKPT> was not written Indicate in the above figure the further location back in the log that we must look to find all incomplete transactions when a crash occurs.

  ---

  ---

  ---

  ---

• Question 3 (Exercise 17.3.3) --- 20 pts
  1. The following is a sequence of redo-log records written by two transactions T and U:

     <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> <T, E, 50> <C0MMIT T>

     Describe the action of the recovery manager by stating which database elments will be updated with which value if there is a crash and the last log record to appear on disk is:

     <START U> <C0MMIT U> <T, E, 50> <C0MMIT T>

  ---

  ---

  ---

  ---

• 0uestion 4: Redo logging (Adapted from exercise 17.2.7) ---- 20 pts
  1. Suppose there was a system crash and the content of the redo log is:

     <START S> <S, A ,60> <START CHKT(S)> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <END CHKT> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  2. Suppose there was a system crash and the content of the redo log is:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START CHKT(T)> <START U> <U, B, 20> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50> <END CHKT> <C0MMIT T> <V ,B , 80>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  3. Suppose there was a system crash and the content of the redo log is:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <START CHKT(T,U)> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <T ,E , 50> <C0MMIT T> <END CHKT> <V ,B , 80>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  4. Suppose there was a system crash and the content of the redo log is:

     <START S> <S, A ,60> <C0MMIT S> <START T> <T, A , 10> <START U> <U, B, 20> <T, C, 30> <START V> <U, D, 40> <V, F, 70> <C0MMIT U> <START CHKT(T,V)> <T ,E , 50> <C0MMIT T> <V ,B , 80> <END CHKT>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

  ---

  ---

  ---

  ---

• Question 5 (Exercise 17.4.3) ---- 20 pts
  1. The following is a sequence of undo/redo-log records written by two transactions T and U:

     <START T> <T, A, 10, 11> <START U> <U, B, 20, 21> <T, C, 30, 31> <U, D, 40, 41> <C0MMIT U> <T, E, 50, 51> <C0MMIT T>

     Describe the action of the recovery manager by stating which database elments will be updated with which value if there is a crash and the last log record to appear on disk is:

     <START U> <C0MMIT U> <T, E, 50> <C0MMIT T>

  ---

  ---

  ---

  ---

• Question 6: Undo/Redo logging (Adapted from exercise 17.2.7) ---- 20 pts
  1. Suppose there was a system crash and the content of the undo/redo log is:

     <START S> <S, X ,60, 61> <START CHKT(S)> <C0MMIT S> <START T> <T, A, 10, 11> <START U> <U, B, 20, 21> <END CHKT> <T, C, 30, 31> <START V> <U, D, 40, 41> <V, F, 70, 71> <C0MMIT U> <T ,E, 50, 51>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed and uncommitted transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  2. Suppose there was a system crash and the content of the undo/redo log is:

     <START S> <S, X, 60, 61> <C0MMIT S> <START T> <T, A ,10, 11> <START CHKT(T)> <START U> <U, B, 20, 21> <T, C, 30, 31> <START V> <U, D, 40, 41> <V, F, 70, 71> <C0MMIT U> <T, E, 50, 51> <END CHKT> <C0MMIT T> <V ,Y, 80, 81>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed and uncommitted transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  3. Suppose there was a system crash and the content of the undo/redo log is:

     <START S> <S, X, 60, 61> <C0MMIT S> <START T> <T, A, 10, 11> <START U> <U, B, 20, 21> <START CHKT(T,U)> <T, C, 30, 31> <START V> <U, D, 40, 41> <V, F, 70, 71> <C0MMIT U> <T, E, 50, 51> <C0MMIT T> <END CHKT> <V, Y, 80, 81>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed and uncommitted transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers:

     ---

     ---

  4. Suppose there was a system crash and the content of the undo/redo log is:

     <START S> <S, X, 60, 61> <C0MMIT S> <START T> <T, A, 10, 11> <START U> <U, B, 20, 21> <T, C, 30, 31> <START V> <U, D, 40, 41> <V, F, 70, 71> <C0MMIT U> <START CHKT(T,V)> <T, E, 50, 51> <C0MMIT T> <V, Y, 80, 81> <END CHKT>

     Questions:

     Indicate in the above figure the furthest log record in the log that we must check to find (= identify) all committed and uncommitted transactions when the crash occured. Label this location with an arrow and label the arrow with the letter A Which database element(s) is/are updated to what value (on disk) during the checkpointing operation State the actions performed when the database system recovers: