CS360: Lab Transactions – Extra Credit

Due: Friday, June 10, at 11:59PM

This lab is not required, but if you complete it and get a perfect score, you will receive extra credit worth ½ of a normal lab. You must submit it by the deadline to receive any credit.

Goals:

Understand transactions and mysql isolation levels.

Requirements:

Login to your mysql database on webpages.sou.edu, download the script ./labTransactions.sql and execute it to set up the tables for this lab.

Start two multiple simultaneous putty command line sessions, as we did in class. This lab is a series of steps that must be performed in sequence to get the right results. If necessary, go back to a ‘clean’ starting point and re-execute all steps from that point. Clean starting points are established by executing a series of bolded steps below.

Copy the text between the lines of XXXXXXXX’s into a MS Word or compatible doc. (It makes the lab much easier to do if you can maintain the table formatting show below.) Edit this document and:

Include your name at the top
Write an answer below each question in a color other than black

XXXXXXXXXXXX – start copy XXXXXXXXXXXX

Serializable isolation level:

Execute the statements as specified in the following time, actor, action table.

Time	Actor	Action
1	Session 1 (S1)	set autocommit=0; every statement is considered part of one transaction until a commit or rollback, which finishes the previous transaction and starts a new one. set session transaction isolation level serializable;
2	Session 2 (S2)	set autocommit=0; set session transaction isolation level serializable;
3	S1	select * from l7stu;
4	S2	select * from l7stu;
5	S1	update l7stu set l7stuID=5 where l7stuid=1;

What happens when S1 attempts this select?

Why?

Repeatable read isolation level:

Suppose the following actions take place:

Time	Actor	Action
1	Session 1 (S1)	set autocommit=0; every statement is considered part of one transaction until a commit or rollback, which finishes the previous transaction and starts a new one. set session transaction isolation level repeatable read;
2	Session 2 (S2)	set autocommit=0; set session transaction isolation level repeatable read;
3	S1	select * from l7stu; Establishes the read ‘snapshot’ for S1
4	S2	select * from l7stu; Establishes the read ‘snapshot’ for S2
	S1	insert l7stu values(4, 'S1four');
	S2	delete from l7stu where l7stuID=2; select * from l7stu;
	S1	select * from l7stu;

Explain why S1 and S2 show different results?

commit;

select * from l7stu;

Establishes a new read ‘snapshot’ for S1

select * from l7stu;

Why does S2’s select not show the data committed by S1?

5	S1	update l7stu set name='S1 update' where l7stuID=1;


6	S2	update l7stu set name='S2 update' where l7stuID=3; s2 is able to do this without blocking. If either S1 or S2’s ‘where’ clause specifies name=<some value>, S2 blocks, because there is no index on the name field, so the scan for name= scans the whole table, thus locks all records in the table.
7	S1	commit; select * from l7stu; Establishes a new repeatable read for S1’s new transaction.
8	S2	select * from l7stu; commit; select * from l7stu;

Why do the last two selects in S2’s session show different data?

Why is the data shown by the last select in S1 different from the data shown by the last select in S2?

update l7stu set l7stuID=2 where l7stuID=1;

select * from l7stu;

Why was this operation allowed when the last select in S1 showed there was already a record (2, JaneDoe) in l7stu?

At this point do the following:
1) rollback S1;
2) use S1 to re-execute labTransactions.sql,
3) commit S1,
4) rollback S2

Time	Actor	Action
1	S1	updates l7stu to change l7stuID 2 to 4
2	S2	select * from l7stu;

What are the contents of l7stu displayed by S2?

Notice S1 cannot display the contents of l7stu.

Why not?

What must happen in order for S1 to successfully execute this statement without timing out?

If S1 has timed out, execute the update command again. Do the action specified as the answer to the last problem before S1 times out again.

What are the contents of l7stu when displayed by S2?

What are the contents of l7stu when displayed by S1?

Explain why the results are different in the two sessions?

At this point do the following:
1) rollback S1;
2) use S1 to re-execute labTransactions.sql,
3)commit S1,
4) rollback S2

Do the following actions:

Time	Actor	Action
1	S1	updates l7stu to change l7stuID 2 to 4
2	S2	Deletes the record from l7sch where l7stuID is 2

What happens?

Time	Actor	Action
3	S1	Rolls back

What are the contents of l7sch when displayed by S1?

What are the contents of l7sch when displayed by S2?

At this point do the following:
1) rollback S1;
2) use S1 to re-execute labTransactions.sql,
3)commit S1,
4) rollback S2

Show a Time, Actor, Action sequence similar to those in the problems above showing what happens when 3 sessions transitively lock resources. Fill in your table with actors S1, S2, S3. This question intentionally left vague. See hints below. ?

What happens after the 3^rd session deadlocks?

At this point do the following:
1) rollback S1;
2) use S1 to re-execute labTransactions.sql,
3)commit S1,
4) rollback S2

Using the tables in lab7Load.sql, Show a Time, Actor, Action sequence that demonstrates a dirty read. Below your sequence table, explain how your sequence shows a dirty read. (Hint: you need to set the transaction isolation level in one of your sessions.) ?

XXXXXXXXXXXX – end copy XXXXXXXXXXXX

Submit your document showing these questions and their answers on moodle.

Hints:

Here is the command to start mysql on the command line – replace ‘nordquip’ with your user name:

mysql -u nordquip -D usr_nordquip_0

Transitivity in general means A affects B affects C.