Assignment-9 Transactions solution

Description

The purpose of this assignment is to experiment with behaviors of transactions in MySQL databases. You will be using more than one MySQL session. To do this, just open 2 putty sessions, logon to hopper, and then logon to MySQL. Use the same name for the output file for all sessions –\T Assgn7out.txt – so that all of your MySQL commands go to the same file. Turn in the print out of your MySQL statements and the results along with your answers to the questions.

Part I: Power of COMMIT (25 points)

1) Start your first MySQL session, issue the following MySQL statements setting the output file:

\T Assgn9out.txt

use znnnnnnn;

create table movie(

pk int auto_increment primary key,

name char(15));

start transaction;

insert into movie (name)

values(‘Das Boot’);

insert into movie (name)

values(‘The Godfather’);

insert into movie (name)

values(‘Jaws’);

insert into movie (name)

values(‘The Black Stallion’);

2. Start your second MySQL session, issue following MySQL statements:

\T Assgn7out.txt use znnnnnnn; select * from movie;

What is result of the select statement, and why?

3)Then in the same session

insert into movie (name)

values(‘Shrek’);

insert into movie (name)

values(‘StarTrek’);

4) Switch back to your first MySQL session, issue following MySQL statement:

commit;

select * from movie;

\t

exit;

5) Switch back to your second MySQL session, issue following MySQL statements:

select * from movie;

\t

exit;

What is result of the select statement, and why?

Part II: Power of Rollback (25 points)

1. Start another MySQL session, issue following MySQL statements:

\T Assgn9out.txt use znnnnnnn; start transaction;

delete from movie where pk = 3; select * from movie;

2)Then

Update movie set name = ‘Iron Man’ where pk = 2;

3)Then

Update movie set name = ‘Con Air’ where pk = 4;

4)Then

insert into movie (name)

values(‘Grease’);

select * from movie;

What is result of the select statement, and why?

5. Issue the following MySQL statements: rollback;

select * from movie;

What is result of the select statement, and why?

\t

exit;

Part III: Be Aware of Deadlock (25 points)

Using another two sessions of MySQL do the following in order specified:

Session I	Session II
\T Assgn7out.txt
use znnnnnn;
start transaction;
	switch to session II
	\T Assgn7out.txt
	use znnnnnn;
	start transaction;
switch back to session I
update movie
set name = ‘The Red Shoes’
where pk=1;
	switch to session II
	update movie
	set name = ‘The Graduate’
	where pk = 5;
switch back to session I
update movie
set name = ‘Shrek 2’
where pk = 2;
	switch to session II
	update movie
	set name = ‘Dr. StrangeLove’
	where pk = 6;

You may get different answers depending on how quickly you do this. Most likely every one should get slightly different answers. If you just copy and paste from this document you may have syntax errors, so please use this as a guide and type the SQL statements in yourself. You actually learn more.

Assignment 9 3 of 3