Description
1. We will use the following transaction schedule S for this problem.
T1 T2
read(A)
write(A)
read(A)
read(B)
commit
read(B)
write(B)
commit
(a) Is S serial?
(b) Is S conflict 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 T1, T2 and T3.
T1 T2 T3
write(A)
read(A)
write(B)
write(B)
write(B)
write(A)
read(B)
read(B)
(a) Construct the precedence graph for this schedule S.
(b) Is S conflict serializable? Justify the answer.
1 CS 143
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.
T1:
SELECT SUM(money) FROM BankAccount;
COMMIT;
T2: 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;
T3: 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.
(a) If all three transactions execute under the SERIALIZABLE isolation level, list all possible values that can
be returned by T1. Explain your answer.
(b) If T1 executes under the READ UNCOMMITTED isolation level and T2 under REPEATABLE READ access level,
and T3 under the SERIALIZABLE isolation level, list all possible values that can be returned by T1.
Explain your answer.
2 CS 143
