SLC0-Introduction to Transaction Processing Concepts and Theory

FACULTY OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY

DEPARTMENT OF COMPUTER SCIENCE

SSK4401 DATABASE SYSTEMS

CHAPTER 5:  TRANSACTION MANAGEMENT

SCL 4

Student Learning Time:

The estimated time to complete this task is 2 hours.

Form a group of 4-5 to discuss the solution and prepare for class presentation.

(1) Please identify and explain  whether the given schedule is conflict-serializable or not

(2) Please indicate TRUE or FALSE

    (I) The following schedule  is serializable ?

     R1(A) -> W1(A) -> R2(B) -> W2(B) ->R1(A)->W2(A)-R1(C)->W1(C)

    (II) SERIALIZABILITY IS THE PROPERTY THAT A (POSSIBLY INTERLEAVED) EXECUTION OF A GROUP OF TRANSACTIONS HAS THE SAME EFFECT ON THE DATABASE AND PRODUCE THE SAME OUTPUT AS SOME SERIAL EXECUTION OD THOSE TRANSACTION

3.    Add the operation commit at the end of each of the transactions T1 and T2 from Figure 1; then list all possible schedules for the modified transactions. Determine which of the schedules are recoverable, which are cascadeless, and which are strict.

Figure 1

4.    Which of the following schedules is (conflict) serializable? For each serializable schedule, determine the equivalent serial schedules.

a)    r1(X); r3(X); w1(X); r2(X); w3(X);

b)    r1(X); r3(X); w3(X); w1(X); r2(X);

c)    r3(X); r2(X); w3(X); r1(X); w1(X);

d)    r3(X); r2(X); r1(X); w3(X); w1(X);

5.    Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. Draw the serializability (precedence) graphs for S1 and S2, and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).

T1: r1(X); r1(Z); w1(X);

T2: r2(Z); r2(Y); w2(Z); w2(Y);

T3: r3(X); r3(Y); w3(Y);

S1: r1(X); r2(Z); r1(Z); r3(X); r3(Y); w1(X); w3(Y); r2(Y); w2(Z); w2(Y);

S2: r1(X); r2(Z); r3(X); r1(Z); r2(Y); r3(Y); w1(X); w2(Z); w3(Y); w2(Y);

6.    Consider schedules S3, S4, and S5 below. Determine whether each schedule is strict, cascadeless, recoverable, or nonrecoverable.

S3: r1(X); r2(Z); r1(Z); r3(X); r3(Y); w1(X); c1; w3(Y); c3; r2(Y); w2(Z); w2(Y); c2;

S4: r1(X); r2(Z); r1(Z); r3(X); r3(Y); w1(X); w3(Y); r2(Y); w2(Z); w2(Z); c1; c2; c3;

S5: r1(X); r2(Z); r3(X); r1(Z); r2(Y); r3(Y); w1(X); c1; w2(Z); w3(Y); w2(Y); c3; c2;

Change transaction T 2 in Figure 21.2b to read:

read_item(X);

X:= X+M;

if X > 90 then exit

else write_item(X);

Discuss the final result of the different schedules in Figure 21.3 (a) and (b), where M = 2 and N = 2, with respect to the following questions. Does adding the above condition change the final outcome? Does the outcome obey the implied consistency rule (that the capacity of X is 90)?