TransactionsinMariaDB(75pts)
Purpose
The purpose of this assignment is to experiment with the behavior of transactions using a MariaDB database. You will need to use morethanone 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;
Question1.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;
Question1.5) What is the result of the SELECT statement above?
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;
Question2.4) What is the result of the SELECT statement, and why?
5) Issue the following MariaDB statements:
ROLLBACK;
SELECT * FROM Fall;
Question2.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,
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;
Question3) What happened here?
