Homework 5 Solution

Description

1. We will use the following transaction schedule S for this problem.

T₁ T₂

read(A)

write(A)

read(A)

read(B)

commit

read(B)

write(B)

commit

1. 1. Is S serial?

1. 2. Is S con ict serializable? If so, what are the equivalent serial schedules? Show your work using the swap method or the graph method.

2. Look carefully at these three transactions T₁, T₂ and T₃.

T₁ T₂ T₃

write(A)

read(A)

write(B)

write(B)

write(B)

write(A)

read(B)

read(B)

1. Construct the precedence graph for this schedule S.

2. Is S con ict serializable? Justify the answer.

3. Consider the relation BankAccount(acctno, money) where we store the amount of money in a, well, bank account, and acctno is the key. Suppose we execute the following three transactions.

T₁:

SELECT SUM(money) FROM BankAccount;

COMMIT;

T₂: In this transaction, the bank has lost its mind and gives everyone a $100 bonus to stimulate spending. But your professor (acctno = 7) gets an additional $1000 because he is cheap and the bank wants him to perform some transactions.

UPDATE BankAccount SET money = money + 100;

UPDATE BankAccount SET money = money + 1000 WHERE acctno = 7;

COMMIT;

T₃: We some more money to account 7 in this transaction. We will also set acctno = 42 to $0 because he died and had some liens on his account.

UPDATE BankAccount SET money = money + 1000 WHERE acctno = 7; UPDATE BankAccount SET money = 0 WHERE acctno = 42;

COMMIT;

The BankAccount table originally has two tuples (7, 15000) and (42, 25000). Assume that individual SQL statements execute atomically.

3. 1. If all three transactions execute under the SERIALIZABLE isolation level, list all possible values that can be returned by T₁. Explain your answer.

3. 2. If T₁ executes under the READ UNCOMMITTED isolation level and T₂ under REPEATABLE READ access level, and T₃ under the SERIALIZABLE isolation level, list all possible values that can be returned by T₁. Explain your answer.

2