CS554 - Advanced Database Systems
Homework 8

Question 1 (Exercise 17.2.4)

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>

Undo log is: <START T> <T, A, 10> <START U> Uncommitted transactions: T Undo T: Update A with 10

<C0MMIT U>

Undo log is: <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> Uncommitted transactions: T Undo T: Update A with 10 Update C with 30

<T, E, 50>

Undo log is: <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> <T, E, 50> Uncommitted transactions: T Undo T: Update A with 10 Update C with 30 Update E with 50

<C0MMIT T>

There are no uncommitted transations Do nothing

Question 2: Undo logging (Exercise 17.2.7)

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

Questions:

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 log records used to recover from the crash

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

Questions:

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.

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

Questions:

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.

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> <-------- Go back till here <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> ---- Check point did not finish !!!

Questions:

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
**** Checkpoint did not complete because transaction V has not committed.
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)

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>

<START U>

Redo log is: <START T> <T, A, 10> <START U> Committed transactions: none Do nothing

<C0MMIT U>

Redo log is: <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> Committed transactions: U Redo U: Update B with 20 Update D with 40

<T, E, 50>

Redo log is: <START T> <T, A, 10> <START U> <U, B, 20> <T, C, 30> <U, D, 40> <C0MMIT U> <T, E, 50> Uncommitted transactions: U Redo U: Update B with 20 Update D with 40

<C0MMIT T>

Committed transactions: T, U Redo T: Update A with 10 Update C with 30 Update E with 50 Redo U: Update B with 20 Update D with 40

Question 4: Redo logging (Adapted from exercise 17.2.7)

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

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

No database elements are updated to disk because there are no committed transactions when checkpoint starts

State the actions performed when the database system recovers:

The committed transaction between location A and end of log are: S and U 1. Redo the updated of transaction S and U (S was not committed before checkpointing starts !) A = 60 B = 20 D = 40 2. (Optional answer) Write to log: Abort T Abort V

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

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

S was committed when checkpointing starts Write S's updates to disk: Write A (= 60) to disk

State the actions performed when the database system recovers:

The committed transaction between location A and end of log are: U and T 1. Redo the updated of transactions U and T B = 20 D = 40 A = 10 C = 30 E = 50 2. (Optional answer) Write to log: Abort V

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

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

S was committed when checkpointing starts Write S's updates to disk: Write A (= 60) to disk

State the actions performed when the database system recovers:

The committed transaction between location A and end of log are: U and T 1. Redo the updated of transactions U and T B = 20 D = 40 A = 10 C = 30 E = 50 2. (Optional answer) Write to log: Abort V

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

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

S and U were committed when checkpointing starts Write S's and U's updates to disk: Write A (= 60) to disk Write B (= 20) to disk Write D (= 40) to disk

State the actions performed when the database system recovers:

The committed transaction between location A and end of log are: T 1. Redo the updated of transaction T A = 10 C = 30 E = 50 2. (Optional answer) Write to log: Abort V

Question 5 (Exercise 17.4.3)

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>

<START U>

Undo/Redo log is: <START T> <T, A, 10, 11> <START U> Uncommitted transactions: T Committed transactions: none Answer: (undo T) Update A to 10

<C0MMIT U>

Undo/Redo log is: <START T> <T, A, 10, 11> <START U> <U, B, 20, 21> <T, C, 30, 31> <U, D, 40, 41> <C0MMIT U> Uncommitted transactions: T Committed transactions: U Answer: (undo T and redo U) Update C with 30 Update A with 10 Update B with 21 Update D with 41

<T, E, 50>

Undo/Redo log is: <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> Uncommitted transactions: T Committed transactions: U Answer: (undo T and redo U) Update E with 50 Update C with 30 Update A with 10 Update B with 21 Update D with 41

<C0MMIT T>

Committed transactions: U, T Redo U: Update B with 21 Update D with 41 Redo T: Update A with 11 Update C with 31 Update E with 51

Question 6: Undo/Redo logging (Adapted from exercise 17.2.7)

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

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:

The uncommitted transaction between location A and end of log are: T and V The committed transaction between location A and end of log are: S and U 1. Undo the updates of transaction T and V A = 10 C = 30 E = 50 F = 70 2. Redo the updates of transaction S and U (S was not committed before checkpointing starts !) X = 61 B = 21 D = 41 3. (Not optional because you undone the transactions) Write to log: Abort T Abort V

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

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

X and A are dirty when check point starts Write X (= 61) to disk Write A (= 11) to disk

State the actions performed when the database system recovers:

The uncommitted transaction between location A and end of log are: V The committed transaction between location A and end of log are: T and U (S's updates are writted to disk and don't need to be recovered !) 1. Undo the updates of transaction V F = 70 Y = 80 2. Redo the updated of transactions U and T B = 21 D = 51 A = 11 C = 31 E = 51 3. Write to log: Abort V

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

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

X, A and B are dirty when check point starts Write X (= 61) to disk Write A (= 11) to disk Write B (= 21) to disk

State the actions performed when the database system recovers:

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

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

X, A, B, C, D and F are dirty when check point starts Write X (= 61) to disk Write A (= 11) to disk Write B (= 21) to disk Write C (= 31) to disk Write D (= 41) to disk Write F (= 71) to disk

State the actions performed when the database system recovers:

The uncommitted transaction between location A and end of log are: V The committed transaction between location A and end of log are: T (S and U's updates are writted to disk and don't need to be recovered !) 1. Undo the updates of transaction V F = 70 Y = 80 2. Redo the updated of transaction T A = 11 C = 31 E = 51 3. Write to log: Abort V

CS554 - Advanced Database Systems Homework 8

CS554 - Advanced Database Systems
Homework 8