Consider the following database table Account Consider also

Consider the following database table:

Account

Consider also the following two transactions that are running concurrently on Account:

Begin;

select *

from account;

Begin;

insert into Account

values (3,6000);

select sum(balance) into @sum

from account;

insert into account

values (4, @sum)

commit;

a) Show contents of table Account after the serial execution of T1T2.

b) Show contents of table Account after the serial execution of T2T1.

c) Show contents of table Account after executing the given schedule while assuming READ UNCOMMITTED transaction isolation level.

d) Show contents of table Account after executing the given schedule while assuming READ COMMITTED transaction isolation level.

e) Did changing the transaction isolation level from READ UNCOMMITTED to READ COMMITTED affect the final table contents? Explain why it did or did not.

accNum	balance
1	2000
2	4000

Solution

a) Starting the execution from T1 is:

select * from account; shows the Account table as

1 2000

2 4000

select sum(balance) into @sum from account; adds the balance and saves to @sum variable as 6000.

insert into account values (4, @sum)

commit;

Insert the sum as 3rd row and commits the changes. Hence the Account table looks like

1 2000

2 4000

4 6000

Then, moving to the T2:

insert into Account values (3,6000);

Inserts the row as 4th row with accNum as 3.

1 2000

2 4000

4 6000

3 6000

rollback command rolls back the transaction. Hence the row will not be saved to Account table.

Therefore, Account table looks like below

1 2000

2 4000

4 6000

b) Starting the execution from T2 is:

1 2000

2 4000

3 6000

rollback removes the row from the table.

Hence the Account table is:

1 2000

2 4000

Then, moving to T1 is:

Now the Account table has 2 rows. Hence the sum would be 6000.

Therefore, the final Account table looks like below:

1 2000

2 4000

4 6000

c) READ UNCOMMITTED transaction level, reads the data which are not saved.

So starting the execution from the first statement of T1 is

select * from account;

1 2000

2 4000

Then the first statement of T2 is executed. It inserts the row into Account table.

1 2000

2 4000

3 6000

Now the second statement of T1 will be executed. So when the Sum is calculated, the 3rd row will also be counted. Hence the sum would be 12000. Sum variable is 12000.

Then the second statement of T2 will be executed. it rollsback the changes. Hence the 3rd row will be removed from Account. But the sum variable still has a value of 12000.

Finally, the last statement of T1 is executed. It inserts the sum row into Account table.

Final Account table will be,

1 2000

2 4000

4 12000

d) READ COMMITTED doesn\'t read non saved data.

select * from account; is

1 2000

2 4000

insert into Account values (3,6000); tries to insert the row to Account table.

But when select sum(balance) into @sum from account; is executed, it doesn\'t look for the last transaction which was not committed. Hence the sum would be 6000.

The final Account table is

1 2000

2 4000

4 6000

e) Yes. The contents are affected. READ UNCOMMITTED does read every transaction, whereas READ COMMITTED reads only committed transaction.