3.2) Consider the following Account table accNum bal 1 1000 2 1000 Assume the fo
ID: 3853763 • Letter: 3
Question
3.2) Consider the following Account table
accNum
bal
1
1000
2
1000
Assume the following two transactions are submitted:
T1: adds a new account where accNum = 3 and balance is the sum of account 1 and account 2 balances. T2: account 1 balance = account 2 balance = 2000
a) What are the final database contents for each of the following possible scenarios
accNum 1
accNum 2
accNum 3
Neither transaction completes its work
Only T1 completes its work
Only T2 completes its work
Serial execution T1 T2
Serial execution T2 T1
a) What are the final database contents after executing the following schedule for each of the following isolation levels? Explain why or why not the two isolation levels results in different final values in the database.
accNum 1
accNum 2
READ COMMITTED
REPEATABLE READ
T1
T2
begin;
select bal into @bal1 from account where accNum = 1;
update account set bal = 2000 where accNum = 1;
update account set bal = 2000 where accNum = 2;
commit
select bal into @bal2 from account where accNum = 2;
insert into account values
(3,@bal1+@bal2);
Commit
accNum
bal
1
1000
2
1000
Explanation / Answer
a) We can see the two transation as:
T1: adds a new account where accNum = 3 and set balance as the sum of account 1 and account 2 balances
T2: account 1 balance = account 2 balance = 2000
1)If neither Transaction completes then
Account 1=1000
Account 2=1000
Account 3=does not exist (Since its created as part of T2)
2) Only T1 completes its work
Account 1=1000
Account 2=1000
Account 3=2000
3) Only T2 completes its work
Account 1=2000
Account 2=2000
Account 3=does not exist (Since its created as part of T2)
4) Serial execution T1 T2
Account 1=2000
Account 2=2000
Account 3=2000
5) Serial execution T2 T1
Account 1=4000
Account 2=2000
Account 3=2000
b) The two isolation levels results in same final values in the database.
accNum 1 accNum 2
READ COMMITTED 2000 2000
REPEATABLE READ 2000 2000
-When the two transaction run as READ COMMITTED then that Transaction has complete control on the resource for the read operation(in this case the two Account) and any other transaction that needs to read on the shared resource has to wait for its turn.
-We can observe that in the two transaction only T2 performs write operation on Account 1 and Account 2, so that means that if both the transaction are run as READ COMMITTED or REPEATABLE READ do result is same final values for Account 1 and Account 2.
-
In other words no two transaction are performing simultaneous write that can result in corrupted values in database. Also the two transactions do run as two different modes of read and not write.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.