CSCI 466 Assignment 9 Transactions in MariaDB

CSCI 466 Assignment 9
Transactions in MariaDB (75pts)
Purpose
The purpose of this assignment is to experiment with the behavior of transactions using a MariaDB database.
You will need to use more than one MariaDB session for these to work. To do this, just open up two separate
putty sessions to turing or hopper and log into MariaDB on each of them. Use the same name for the output
file for all of the sessions – \T assign9out.txt – so that the output from all of your sessions ends up in the
same file. When znnnnnnn is used, replace it with your own z-id.
Part I - The Power of COMMIT (25pts)
1) Start your first MariaDB seesion, issue the following SQL queries:
\T assign9out.txt
USE znnnnnnn;
CREATE TABLE Fall(
pk INT PRIMARY KEY,
data CHAR(15));
START TRANSACTION;
INSERT INTO Fall
VALUES(1, 'dataA');
INSERT INTO Fall
VALUES(2, 'dataB');
INSERT INTO Fall
VALUES(3, 'dataC');
2) Start your second MariaDB session, and run the following SQL queries in it.
\T assign9out.txt
USE znnnnnnn;
SELECT * from Fall;
Question 1.2) What is the result of running the SELECT statement. Why?
3) In that second session, run the following:
INSERT INTO FalI VALUES(4, 'dataD');
INSERT INTO Fall VALUES(5, 'dataE');
4) Switching back to the first MariaDB session, issue the following queries:
COMMIT;
SELECT * FROM Fall;
\t
exit;
5) Switch back to the second MariaDB instance, and run the following queries:
SELECT * FROM Fall;
\t
exit;
Question 1.5) What is the result of the SELECT statement above?
CSCI 466 Assignment 9 2 of 3
Part II -- The Power of ROLLBACK (25pts)
1) Start another MariaDB session, issue following MariaDB statements:
\T assign9out.txt
USE znnnnnnn;
START TRANSACTION;
DELETE FROM Fall WHERE pk = 3;
SELECT * FROM fall;
2) Then
UPDATE Fall
SET Data = 'changed'
WHERE pk = 2;
3) Then
UPDATE Fall
SET Data = 'changed 2'
WHERE pk = 4;
4) Then
INSERT INTO Fall VALUES(6, 'dataF');
SELECT * FROM Spring;
Question 2.4) What is the result of the SELECT statement, and why?
5) Issue the following MariaDB statements:
ROLLBACK;
SELECT * FROM Fall;
Question 2.5) What is the result of the SELECT statement, and why?
\t
exit;
Part III: Be Aware of Deadlock (25pts)
Using another two sessions of MariaDB, do the following in the order specified:
1) In session 1,
\T assign9out.txt
USE znnnnnn;
START TRANSACTION;
2) In session 2,
\T assign9out.txt
USE znnnnnn;
START TRANSACTION;
3) In session 1,
CSCI 466 Assignment 9 3 of 3
UPDATE Fall
SET data = 'data1A'
WHERE pk=1;
4) In session 2,
UPDATE Fall
SET data= 'data2B'
WHERE pk = 2;
5) In session 1,
UPDATE Fall
SET data = 'data5E'
WHERE pk = 5;
6) In session 2,
UPDATE Fall
SET DAta = 'data12B'
WHERE pk = 1;
Question 3) What happened here?
Notes
There may be typos in these statements. If there is a syntax error, fix it. These errors were made to encourage
you to pay close attention, and to type the statements in yourself as practice.
What to turn in?
Turn in, via Blackboard, the following:
• The output generated by running the commands in both of the sessions.
• The answers to the questions asked, (1.2, 1.5, 2.4, 2.5, and 3), in a separate text file.