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;
Assignment 9 1 of 3
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;
Assignment 9 2 of 3
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
