8(A) ISOLATION LEVELS Consider a table Item(name,cost) where name is a key. Supp
ID: 3825117 • Letter: 8
Question
8(A) ISOLATION LEVELS Consider a table Item(name,cost) where name is a key. Suppose initially there are two tuples in Item: (A,20) and (B,30). Consider the following two concurrent transactions, each of which runs once and commits. You may assume there are no other transactions in the system and that individual statements execute atomically. T1: begin transaction S1: insert into Item values (’C’,40) S2: update Item set price = price+30 where name=’A’ commit T2: begin transaction S3: select avg(price) as p1 from Item S4: select avg(price) as p2 from Item commit Suppose that transaction T1 executes with isolation level _serializable_. (a) If transaction T2 also executes with isolation level _serializable_, what are all the possible pairs of values p1 and p2 returned by T2? (b) If transaction T2 executes with isolation level _repeatable read_, what are all the possible pairs of values p1 and p2 returned by T2? (c) If transaction T2 executes with isolation level _read committed_, what are all the possible pairs of values p1 and p2 returned by T2? (d) If transaction T2 executes with isolation level _read uncommitted_, what are all the possible pairs of values p1 and p2 returned by T2?
Explanation / Answer
(a) This isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other
Possible values:
Case 1: If T1 transaction is started before T2
p1 = 40
p2 = 40
Case 2: If T1 transaction is started afterT2
p1 = 25
p2 = 25
=============================================================================
(b) In repeatable read scan retains locks on every row it touches until the end of the transaction.
Possible values:
Case 1: If T1 transaction is started before T2
p1 = 40
p2 = 40
Case 2: If T1 transaction is started afterT2
p1 = 25
p2 = 25
Case 3: If T1 and T2 executes parallely.
p1 = 25 (assuming row C is not yet inserted)
Now as first select query has got a lock on the table and rows A and B, update command of T1 will be locked. As individual commands executes atomically.
p2 = 33.33
Case 4: If T1 and T2 executes parallely.
p1 = 33.33 (assuming row C is inserted)
Now as first select query has got a lock on the table and rows A and B, update command of T1 will be locked. As individual commands executes atomically.
p2 = 33.33
=================================================================================
(c) This is because in READ COMMITTED the read view for the transaction lasts only as long as each statement execution.
Possible values:
Case 1: If T1 transaction is started before T2
p1 = 40
p2 = 40
Case 2: If T1 transaction is started afterT2
p1 = 25
p2 = 25
Case 3: If T1 and T2 executes parallely.
First readable snapshot is created rows: (A,20),(B,30)
p1 = 25
T1 releasses lock on row A and B
As the T1 has inserted row C, second snapshot has rows (A,20), (B,30), (C,40)
p2 = 33.33
==============================================================================
(d) Read uncommitted:This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction.
Possible values:
Case 1: If T1 transaction is started before T2
p1 = 40
p2 = 40
Case 2: If T1 transaction is started afterT2
p1 = 25
p2 = 25
Case 3: If T1 and T2 executes parallely.
If T1 has inserted the row C and is not yet committed
p1: 33.33
If T1 has inserted row C and updated row A but has not committed yet
p2: 40
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.