CSCI466Assignment9 - Solved

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?