11.1 Assume a DBMS that uses immediate updates has the following log entries. FIGURE 11.18 shows a timeline depicting each transaction’s starting and ending time, along with the checkpoint. A start and a commit are indicated by a large dot, while an abort is indicated by an X. Assuming a system crash occurs as indicated immediately after the <S commits> log record:
Which transactions, if any, need to be redone?
Which transactions, if any, need to be undone?
Which transactions, if any, are not affected by the crash?
FIGURE 11.18 Transaction Timeline for Exercise 11.1
Assuming the variables in different transactions refer to database items with the same names, what are the final values of w, x, y, and z?
If a second system failure occurs while the first recovery is in progress, what needs to be done after the system recovers for the second time?
11.2 Assume that the same transactions and operations as shown in Exercise 11.1 are being done by a system that uses the deferred update protocol.
Rewrite the log entries for the transactions in Exercise 11.1 for this logging method.
Which transactions, if any, need to be redone?
Which transactions, if any, need to be undone?
Which transactions, if any, are not affected by the crash?
What will be the final values of w, x, y, and z?
If a second system failure occurs while the first recovery is in progress, what needs to be done after the system recovers for the second time?
11.3 Suppose the log in Exercise 11.1 contained the entry <S aborts> in place of the entry <S commits>.
If the system is using immediate updates and the crash occurred before the rollback took place, what changes, if any, would you make in the recovery process?
If the system were using deferred updates, what changes would you make in the recovery process of Exercise 11.2?
11.4 Assume the following transactions are to be performed.
![A listing of Transactions S and T.
Transaction S.
Line 1. read open parentheses a, close parentheses, semicolon.
Line 2. a equals a plus 10, semicolon.
Line 3. write open parentheses a close parentheses semicolon.
Line 4. read open parentheses b close parentheses, semicolon.
Line 5. b equals b times 5, semicolon.
Line 6. write open parentheses b close parentheses semicolon.
Transaction T.
Line 1. read open parentheses a close parentheses semicolon.
Line 2. a equals a times 2 semicolon.
Line 3. write a, semicolon.](https://imgdetail.ebookreading.net/2023/10/9781284231595/9781284231595__9781284231595__files__images__9781284236354_CH11_PRE20.jpg)
If the initial value of a is 10 and the initial value of b is 20, what are their final values if we perform the transactions serially, using order S,T?
Using the same initial values, what are the final values of a and b if the order of execution is T,S?
Does this result have any implications for serializability?
11.5 Write a concurrent schedule for transactions S and T in Exercise 11.4 that illustrates the lost update problem.
11.6 Apply the standard two-phase locking protocol to the schedule you devised in Exercise 11.5. Will the protocol allow the execution of that schedule? Does deadlock occur?
11.7 Apply the standard timestamping protocol to the schedule you devised in Exercise 11.5. In the columns for the attributes, add Read- and Write-Timestamps, assuming both were last set by a transaction with timestamp t0, as shown in Figure 11.15. Will the protocol allow the execution of that schedule? Are there any rollbacks? If so, are they cascading rollbacks?
11.8 Apply the standard timestamping protocol to the schedule shown in Figure 11.3. Will the protocol allow the execution of that schedule? Are there any rollbacks? If so, are they cascading rollbacks?
11.9 Apply the standard two-phase locking protocol to the schedule shown in Figure 11.4. Does deadlock occur?
11.10 Apply the standard timestamping protocol to the schedule shown in Figure 11.4. Will the protocol allow the execution of that schedule? Are there any rollbacks? If so, are they cascading rollbacks?
11.11 Apply the standard two-phase locking protocol to the schedule shown in Figure 11.5. Does deadlock occur?
11.12 Apply the standard timestamping protocol to the schedule shown in Figure 11.5. Will the protocol allow the execution of that schedule? Are there any rollbacks? If so, are they cascading rollbacks?
11.13 Let T1, T2, and T3 be transactions that operate on the same database items, A, B, and C. Let r1(A) mean that T1 reads A, w1(A) mean that T1 writes A, and so on for T2 and T3. Each of the following shows the order of the reads and writes in a schedule for T1, T2, and T3. In each case, draw a precedence graph with nodes for T1, T2, and T3 and draw edges for conflicts. Determine if each schedule is serializable by examining the precedence graph. If it is serializable, give an equivalent serial schedule.
r1(A); r2(A); w1(A); r3(A); w3(A); w2(A)
r1(A); r1(B); r2(A); r3(B); r3(C); w2(A); w2(B)
r1(A); r3(C); r2(C); w3(A); r2(B); r3(B); w2(B); w2(A)
11.14 In this exercise you will use the University database to demonstrate locking in Oracle. Code for creating and populating this database can be found at go.jblearning.com/Ricardo4e.
Open two instances of SQL*Plus, signing into both using the same userId.
In the first instance, write a statement to update the major of S1020 to Chemistry.
In the second instance write a statement to update the major of S1020 to Biology. Describe the problem you observe and explain why it occurred.
Return to the first instance and write COMMIT. Describe what happens.
Returning to the second instance, describe and explain any changes. Then write COMMIT.
Write a query to find S1020’s major in both instances and explain the results.
Open two instances of SQL*Plus, signing into both using the same userId.
In the first instance, write a statement to update the major of S1001 to Biology.
In the second instance, write a statement to update the major of S1002 to Finance. Explain the result.
In the first instance, write a statement to update the major of S1002 to Economics. Describe what happens and explain why it occurred.
In the second instance, write a statement to update the major of S1001 to Physics. Describe what happens and explain why it occurred.
Explain how to resolve the problem that arose and execute your solution.
11.15 In this exercise you will use the University database to demonstrate transaction control in Oracle. Code for creating and populating this database can be found at go.jblearning.com/Ricardo4e.
Use SQL*Plus to demonstrate ROLLBACK and COMMIT:
Write and execute a command to add 10 credits to all Student records and then display the results using a SELECT command.
Write a ROLLBACK command and display the results.
Repeat the command in part (i).
Write a COMMIT command and display the results.
Use SQL*Plus to demonstrate savepoints in a transaction:
Write a SET TRANSACTION statement, giving the transaction a name, but do not specify an isolation level.
Write and execute a command to change all major values to Art and then display the result.
Write a command to create a savepoint named AllArt.
Write and execute a command to change all major values to CSC and then display the result.
Write a rollback command to roll back to the savepoint AllArt and display the result.
Write a second rollback command and display the results. What point does this command roll back to?
Use SQL*Plus to demonstrate default and user-specified isolation levels:
Write a SET TRANSACTION statement, giving the transaction a name, but do not specify an isolation level.
Write and execute SELECT command to display all students’ last names and credits.
Write a command to subtract 10 credits from all student records and display the results.
Commit the previous transaction and write a new SET TRANSACTION statement, specifying a READ ONLY isolation level.
Repeat steps (ii) and (iii) for the new transaction and explain the results.
Roll back the previous transaction and write a new SET TRANSACTION statement, specifying a READ WRITE isolation level.
Repeat steps (ii) and (iii) and explain the results. Roll back the transaction.