Solution #6

CS245 PROBLEM SET #6
Solutions

Problem 1: Interleaving of Transactions (20 points)

Two transactions are not interleaved in a schedule if every action of one transaction precedes every action of the other. We say transaction T1 precedes T2 if they are not interleaved, and all actions of T1 precede actions of T2. Give an example of a conflict serializable schedule H that has the following properties:

transactions T1 and T2 are not interleaved in H;
T1 precedes T2 in H; and
in any serial schedule conflict equivalent to H, T2 precedes T1.

The schedule may include more than 2 transactions and you do not need to consider locking actions. Please use as few transactions and read or write actions as possible.

Solution:

H = w3(A) r1(A) r2(B) w3(B)

In H, all actions of T1 precedes all actions of T2, so T1 precedes T2. The only serial schedule that is conflict-equivalent to H is

H' = r2(B) w3(A) w3(B) r1(A)

in which T2 precedes T1. So, H is an example schedule that satisfies all the above requirements.

NOTE: H = r1(A) r2(A) is not a correct answer as explained in the FAQ.

Problem 2: Undo/Redo Logging and Two Phase Locking (20 points)

In this problem, you are given the current contents of the log of a DBMS. The DBMS uses UNDO/REDO logging with non-quiescent checkpoints (with checkpoint starts and checkpoint ends). The log contents are shown below (sequence is ordered left to right, top to bottom, so < T1, start> is the first entry in the sequence and < T3, Z, 40, 80> is the last). Assume the log entries are in the format <Tid, Variable, Old value, New value>.

        < T1 start>              < T1, X, 5, 10>       < T2 start>
        < T2, X, 10, 20>         < T1 commit>          < T2, Y, 30, 60>
        < checkpoint start>      < T2, W, 35, 70>      < T3 start>
        < T3, W, 70, 40>         < checkpoint end>     < T2, Z, 20, 40>
        < T2 commit>             < T3, Z, 40, 80>

You are also given that the concurrency mechanism used by the DBMS is two phase locking, and there are only read and write locks.

(a): Is it possible for the log to have the above contents, given our assumptions about the system? Explain briefly. If the answer is no, which is the first "impossible" log entry? Why? Remove that entry. Is it possible for the log to contain the new sequence? Again explain why or why not. Repeat until you get a possible sequence of log entries.
(b): For the sequence of entries you got in (1), what are the possible values of X,Y,W,Z after the last of these log records is written to disk and before recovery? Briefly explain why.

Solution:

(a)

The log is possible. The following sequence of actions is one possible schedule based on the log.

T1 starts
T1 locks X
T1 changes X from 5 to 10
T1 unlocks X
T2 starts
T2 locks X
T2 changes X from 10 to 20
T1 commits
T2 locks Y
T2 changes Y from 30 to 60
checkpoint start
T2 locks W
T2 changes W from 35 to 70
T2 locks Z
T2 unlocks X, Y, W
T3 starts
T3 locks W
T3 changes W from 70 to 40
checkpoint end
T2 changes Z from 20 to 40
T2 unlocks Z
T2 commits
T3 locks Z
T3 changes Z from 40 to 80

The schedule satisfies the basic locking rules (#1 and #2) and the two-phase locking rule. Therefore, it can be generated using the 2PL protocol.

(b)

Since the "checkpoint end" has been written to the log, we know all data value logged before the "checkpoint start" has been flushed. So, the only possibility for X and Y is X = 20, Y = 60.

For the other variables, since undo/redo logging does not have any restrictions on whether to flush data before or after commit, the new values of W and Z (after the last "checkpoint start" record) may or may not be flushed. So, W = 35/70/40, Z = 20/40/80.

Problem 3: Conflict Serializability and Locking Rules (20 points)

(a): Give a simple schedule where (exactly) one transaction in the schedule is not well formed, and this causes the schedule to be non-conflict-serializable. Show why your schedule is not conflict-serializable.
(b): Give a simple non-legal schedule that is not conflict-serializable. Show why your schedule is not conflict-serializable.
(c): Give a simple schedule where (exactly) one transaction in the schedule is not using two phase locking, and that causes the schedule to be non-conflict-serializable. Show why your schedule is not conflict-serializable.
(d): Give a simple conflict serializable schedule that cannot be produced by a two phase locking scheduler. Show why your schedule is conflict serializable, and why it cannot be produced by a two phase locking scheduler. For this particular question, your example schedule should contain only read and write actions. All the lock and unlock actions are hidden.

Use as few transactions and actions as possible in your examples.

Solution:

(a)

S = l1(A) r1(A) w2(A) w1(A) u1(A)

In this schedule, T2 is not well-formed; that is it writes A before locking A. Also, since w2(A) conflicts with r1(A) and w1(A), H is not conflict-serializable.

(b)

S = l1(A) r1(A) l2(A) w2(A) u2(A) w1(A) u1(A)

This schedule is not legal because T2 obtains lock on A while T1 is still locking A. Also, since w2(A) conflicts with r1(A) and w1(A), H is not conflict-serializable.

(c)

S = l1(A) r1(A) u1(A) l2(A) w2(A) u2(A) l1(A) w1(A) u1(A)

In this schedule, T1 does not satisfy 2PL; that is it first unlocks A and later locks A again. Also, since w2(A) conflicts with r1(A) and w1(A), H is not conflict-serializable.

(d)

H = w1(A) r2(A) r1(A)

This schedule could not have been generated using 2PL, because T1 must release the exclusive lock on A before T2 can lock and read A, and T1 must lock A again when it reads A later. On the other hand, schedule H is conflict-serializable. It is conflict equivalent to serial schedule

H' = w1(A) r1(A) r2(A)

Problem 4: Group Modes in Lock Tables (20 points)

Consider the shared/exclusive/warning scheme described in class and in the lecture notes. In the table below you are given six different cases of simultaneously held locks on some object. In each case, give the group mode that is stored in the lock table, or explain briefly why the situation is impossible.

Case Locks

(a) S, S, IS

(b) S, IS, SIX

(c) IX, IS, IS

(d) IX, X

(e) SIX, IX

(f) SIX, IS

Case	Locks
(a)	S, S, IS
(b)	S, IS, SIX
(c)	IX, IS, IS
(d)	IX, X
(e)	SIX, IX
(f)	SIX, IS

Solution:

Case Locks Group Mode

(a) S, S, IS S

(b) S, IS, SIX Impossible: SIX and S are incompatible

(c) IX, IS, IS IX
(d) IX, X Impossible: X and IX are incompatible
(e) SIX, IX Impossible: IX and SIX are incompatible
(f) SIX, IS SIX

Case	Locks	Group Mode
(a)	S, S, IS	S
(b)	S, IS, SIX	Impossible: SIX and S are incompatible
(c)	IX, IS, IS	IX
(d)	IX, X	Impossible: X and IX are incompatible
(e)	SIX, IX	Impossible: IX and SIX are incompatible
(f)	SIX, IS	SIX