Database miscellaneous
- Which of the following scenarios may lead to an irrecoverable error in a database system?
-
View Hint View Answer Discuss in Forum
A transaction reads a data item after it is written by an uncommitted transaction. As the errors cannot recovered in this situation.
Correct Option: D
A transaction reads a data item after it is written by an uncommitted transaction. As the errors cannot recovered in this situation.
- Consider three data items D1, D2 and D3 and the following execution schedule of transactions T1, T2 and T3. In the diagram, R(D) ans W(D) denote the actions reading and writing the data item D respectively.
Which of the following statements is correct?
-
View Hint View Answer Discuss in Forum
Step 1: Create a node for each transaction.
Step 1:
Step 2 : Find the conflict pairs (RW, WR, WW) on same variable by different transactions.
W1(D1); W2(D1) [1a]
W1(D1); R2(D1) [1d]
R2(D3); W3(D3) [3e]
W2(D2); W3(D2) [42]
W2(D2); R3(D2) [4f]
W3(D2); W1(D2) [2b]
W3(D2); R1(D2) [2c]
Step 3 : Draw an edge for each conflict pair.
As Cycle formed, Therefore schedule is not a conflict serializable schedule.Correct Option: D
Step 1: Create a node for each transaction.
Step 1:
Step 2 : Find the conflict pairs (RW, WR, WW) on same variable by different transactions.
W1(D1); W2(D1) [1a]
W1(D1); R2(D1) [1d]
R2(D3); W3(D3) [3e]
W2(D2); W3(D2) [42]
W2(D2); R3(D2) [4f]
W3(D2); W1(D2) [2b]
W3(D2); R1(D2) [2c]
Step 3 : Draw an edge for each conflict pair.
As Cycle formed, Therefore schedule is not a conflict serializable schedule.
- Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortagage payment and then apply a 5% interest.
1. T1 start
2. T1 B old = 1200 new = 10000
3. T1 M old = 0 new = 2000
4. T1 commit
5. T1 start
6. T2 B old = 10000 new = 10500
7. T2 commit
Suppose the database system crashes just before log record is written. When the system is started, which one statement is true of the recovery procedure?
-
View Hint View Answer Discuss in Forum
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3 because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1) Note: Here we are not using checkpoints.
Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.
Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.
So Answer is B We must redo log records 2 and 3 and undo log record 6.Correct Option: B
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3 because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1) Note: Here we are not using checkpoints.
Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.
Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.
So Answer is B We must redo log records 2 and 3 and undo log record 6.
- Consider the following schedules involving two transactions. Which one of the following statements is true?
S1 : r1 (X); r1 (Y); r2 (X); r2 (Y); w2 (Y); w1 (X)
S2 : r1 (X); r2 (X); r2 (Y); w2 (Y); r1 (Y); w1 (X)
-
View Hint View Answer Discuss in Forum
Lets construct the S1 and S2 with the time to check the serializable conflict.
S1 → No serializable conflict
S2 → Serializable conflict in w1 (x)Correct Option: C
Lets construct the S1 and S2 with the time to check the serializable conflict.
S1 → No serializable conflict
S2 → Serializable conflict in w1 (x)
- Consider two transactions T1 and T2, and four schedules S1 , S2 , S3 , S4 of T1 and T2 as given below :
T1 : R1 [X] W1 [X] W1 [y]
T2 : R2 [x] R2 [y] W2 [y]
S1 : R1 [x] R2 [x] W1 [x] W1 [y] W2 [y]
S2 : R1 [x] R2 [x] R2 [y] W1 [x] W2 [y] W1 [y]
S3 : R1 [x] W1 [x] R2 [x] W1 [y] R2 [y] W2 [y]
S4 : R2 [x] R2 [y] R1 [x] W1 [x] W1 [y] W2 [y]
Which of the above schedules are conflict-serializable ?
-
View Hint View Answer Discuss in Forum
Let us construct the schedule and transition diagram to determine the conflict :
Schedule S2
Schedule S3
Here, we can see that S1 and S4 are not conflict serializable but S2 and S3 are conflict serializable.Correct Option: B
Let us construct the schedule and transition diagram to determine the conflict :
Schedule S2
Schedule S3
Here, we can see that S1 and S4 are not conflict serializable but S2 and S3 are conflict serializable.
