Review Questions
17.1 Explain what is meant by a transaction. Why are transactions important units of operation in a
DBMS?
Transaction: An action or series of actions, carried out by a single user or application program, which accesses or changes the contents of the database (Section 17.1).
A logical unit of work that transforms the database from one consistent state to another. Also the unit of concurrency and recovery control.
17.2 The consistency and reliability aspects of transactions are due to the "ACIDity" properties of
transactions. Discuss each of these properties and how they relate to the concurrency control and recovery mechanisms. Give examples to illustrate your answer.
ACID properties discussed in Section 17.1.1.
17.3 Discuss, with examples, the types of problems that can occur in a multiuser environment when
concurrent access to the database is allowed.
Lost update problem, the uncommitted dependency problem and the inconsistent analysis problem (Examples 17.1 - 17.3).
17.4 Give full details of a mechanism for concurrency control that can be used to ensure the types of
problems discussed in 17.3 cannot occur. Show how the mechanism prevents the problems illustrated from occurring. Discuss how the concurrency control mechanism interacts with the transaction mechanism.
Should discuss 2PL, timestamping, or an optimistic technique. Solutions to above problems for 2PL given in Examples 17.6 - 17.8.
Transaction is the unit of concurrency control.
17.5 Discuss the types of problems that can occur with locking-based mechanisms for concurrency
control and the actions that can be taken by a DBMS to prevent them.
Deadlock/livelock (see end of Section 17.2.3 and Section 17.2.4.)
17.6 Explain the concepts of serial, non-serial, and serializable schedules. State the rules for
equivalence of schedules.
See Section 17.2.2.
17.7 Discuss the difference between conflict serializability and view serializability.
See Section 17.2.2.
17.8 Discuss the types of failure that may occur in a database environment. Explain why it would be
unreasonable for a multiuser DBMS not to provide a recovery mechanism.
Failures given in Section 17.3.1. Answer to second part is based on the amount of work that potentially could be lost following a failure.
17.9 Discuss how the log file (or journal) is a fundamental feature in any recovery mechanism. Explain
what is meant by forward and backward recovery and describe how the log file is used in forward and backward recovery. What is the significance of the write-ahead log protocol? How do checkpoints affect the recovery protocol?
50
Log file contains before and after-images of updates to the database. Before images can be used to undo changes to the database; after-images can be used to redo changes.
Log file also contains a checkpoint record, which can speed up the time for recovery following a failure.
17.10 Discuss the following advanced transaction models: (a) nested transactions,
(b) sagas,
(c) multi-level transactions,
(d) dynamically restructuring transactions.
See Section 17.4. Exercises
17.11 Analyze the DBMSs that you are currently using. What concurrency protocol does the DBMS use? What type of recovery mechanism is used? What support is provided for the advanced transaction models discussed in Section 17.4?
This is a small student project, the result of which is dependent on the system analyzed.
17.12 (a) Explain what is meant by the constrained write rule, and explain how to test whether a schedule is serializable under the constrained write rule. Using the above method, determine whether the following schedule is serializable:
S = [R1(Z), R2(Y), W2(Y), R3(Y), R1(X), W1(X), W1(Z), W3(Y), R2(X), R1(Y), W1(Y),
W2(X), R3(W), W3(W)]
where Ri(Z)/Wi(Z) indicates a read/write by transaction i on data item Z.
Constrained write rule: transaction updates a data item based on its old value, which is first read by the transaction. A precedence graph can be produced to test for serializability.
Cycle in precedence graph, which implies that schedule is not serializable.
(b) Would it be sensible to produce a concurrency control algorithm based on
serializability? Give justification for your answer. How is serializability used in standard concurrency control algorithms?
No - interleaving of operations from concurrent transactions is typically determined by operating system scheduler. Hence, it is practically impossible to determine how the operations will be interleaved beforehand to ensure serializability. If transactions are executed and then you test for serializability, you would have to cancel the effect of a schedule if it turns out not to be serializable. This would be impractical!
17.13 Produce a wait-for-graph for the following transaction scenario and determine whether deadlock exists.
T1 T2
Transaction Data items locked by transaction Data items transaction is waiting for T1 X2 X1, X3 T2 X3, X10 X7, X8 T3 X8 X4, X5 T4 X7 X1 T5 X1, X5 X3 T6 X4, X9 X6 T7 X6 X5
Cycles in graph implies that deadlock exists.
17.14 Write an algorithm for shared and exclusive locking. How does granularity affect this algorithm?
read_lock(X): B: if LOCK (X) = "unlocked" then begin LOCK (X) = "read-locked"; no_of_reads(X) = 1 end
else if LOCK(X) = "read-locked"
then no of_reads(X) = no_of _reads(X) + 1 else begin
wait (until LOCK (X) = "unlocked" and
the lock manager wakes up the transaction); goto B
end write_lock (X):
B: if LOCK (X) = "unlocked"
then LOCK (X) = "write-locked" else begin
wait (until LOCK(X) = "unlocked" and
the lock manager wakes up the transaction); goto B end; T2 T3 T1 T4 T5 T6 T7
52 unlock_item (X):
if LOCK (X) = "write-locked" then begin
LOCK (X) = "unlocked";
wakeup one of the waiting transactions, if any end
else if LOCK(X) = "read-locked" then begin
no of_reads(X) = no_of_reads(X) - 1; if no of reads(X) = 0
then begin
LOCK (X) = "unlocked";
wakeup one of the waiting transactions, if any end;
end;
Algorithm 1 Locking and unlocking operations for two-mode (read-write or shared-exclusive) locks
17.15 Write an algorithm that checks whether the concurrently executing transactions are in deadlock.
Boolean function deadlock_detection
Input: A table called Wait_for_Table containing
Transaction_id; Data_Item_Locked; Data_Item_Waiting_For Output: Boolean flag indicating whether system is deadlocked.
begin
Deadlock = FALSE; Transaction_stack = NULL;
for next transaction in Wait_for_Table while not Deadlock begin
push next transaction_id into Transaction_stack;
for next Data_Item_Waiting_For of transaction on top of stack and not Deadlock and not Transaction_stack = NULL begin
D_next = next Data_Item_Waiting_For;
find Tran_id of transaction which has locked D_next; if Tran_id is in stack
then
Deadlock = TRUE; else
push Tran_id to Transaction_stack; end
pop Transaction_stack; end
return deadlock; end
17.16 Explain why stable storage cannot really be implemented. How would you simulate stable storage?
Information residing on stable storage is never lost. Theoretically, this cannot be guaranteed. To implement an approximation of stable storage, we need to replicate information in several nonvolatile storage media with independent failure modes and update the information in a controlled manner. Although a large number of copies reduces the probability of a failure, it is usually reasonable to simulate stable storage with only two copies.
Block transfer can result in: successful completion, partial failure (destination block has incorrect information) and total failure (destination blocks not written to).
If a data transfer occurs, the system must detect it and recover the block to a consistent state. To do so, the system maintains two physical blocks for each logical database block, written as follows:
1. Write information to first physical block.
2. When first is successfully complete, write same information to second physical block. 3. Output is complete, only after the second write successfully completes.
17.17 Would it be realistic for a DBMS to dynamically maintain a wait-for-graph rather than create it each time the deadlock detection algorithm runs? Explain your answer.
Yes, could do this by maintaining the WFG in memory and only update directed edges that change.
54