The purpose of this assignment is to experiment with behaviors of transactions i

Question

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;

exit;

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

select * from movie;

exit;

What is result of the select statement, and why?

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?

exit;

**********please post screenshots******

Explanation / Answer

2) The result of this select query is empty i.e. nothing can be seen because even if the data is inserted in session1 it will be not be seen in session2 untill the session2 reconnects or given commit command.

Even if you commit in session1 rows will not display in session2 either you have to reconnect the session2 or execute commit to see the rows inserted.

Output:- Query OK, 0 rows affected

4) Here in this we will see 6 rows which are previously inserted 4 rows through session1 and now inserted 2 rows through session2. Because we executed commit in session1 we can see the rows which are inserted through session2 otherwise not.

Output:- Query OK, 6 rows affected

5) since we didnot issued commit command yet it will show only 2 rows inserted from session2;

Output:- Query OK, 2 rows affected

Because this is a new session it will get all rows then after doing all the given updates and delete operations table will be like this.

The rollback operation will take you to state where the last commit was executed. So here the last commit was happened after inserting 6 rows in the table. So because of rollback all the previous changes are roll backed.

pk name 1 Das Boot 2 The Godfather 3 Jaws 4 The Black Stallion 5 Shrek 6 StarTrek

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.