IT360 Lab 13 Transactions and
Concurrency
DUE: April 25, 2013, before 2359
(paper copy due BEFORE start of lab nest day)
This lab should get you familiarized with transactions and concurrency-related problems.
Write the answers to each of the following exercises in a file called yourlastname_yourfirstname_Lab13.txt
Part A:
Connect to your MySQL database on cardhu.cs.usna.edu using MySQL Workbench.
1) When using transactions, the effects of the current transaction should not be visible outside the transaction until the transaction terminates (commits). Also, if a transaction aborts (rollback), the effects of the transaction are “undone”. The default behavior of the MySQL Workbench editor is to use auto-commit mode: each statement is a transaction in itself, and it is committed automatically. We will change that default behavior, so we can control the start/end transaction time and experiment with how transactions act:
INSERT INTO Product(BarCode, PName, Price, QuantityInStoc) VALUES(5, ‘Bagel’, 0.99, 100) would insert one Product with BarCode 5 in my Product table. If needed, change the insert statement appropriately to insert into your table.
SELECT * FROM Product
SELECT * FROM Product
a) Do you see the product that you just inserted in the other session? (write the answer in yourlastname_yourfirstname_Lab13.txt)
b) Why? (write the answer in yourlastname_yourfirstname_Lab13.txt)
SELECT * FROM Product
c) Do you see the product that you inserted in the other session? (write the answer in yourlastname_yourfirstname_Lab13.txt)
d) Why? (write the answer in yourlastname_yourfirstname_Lab13.txt)
Part B:
The questions in this part are related to possible operations in the Online Store database. Of special interest for this lab are the following three tables you are familiar with:
Product(BarCode, PName, Price, QuantityInStock)
Sale (SaleID, DeliveryAddress, CreditCard)
SaleItem (SaleID, BarCode, Quantity)
Assume that the following two types of stored procedures can run concurrently in your application (could also have multiple calls to the same procedure running concurrently):
spInsertProduct: creates new rows in Product table for the new products that will be sold in the store. This procedure uses only the Product table to insert new products.
spInsertModifySale: records new or modifies existing sales information.
This procedure writes to the
2) Give, in plain English, and optionally by using R (for read), and W (for write) notation that we used in class, an example of a schedule for this group of stored procedures that would lead to a dirty read, or explain why a dirty read cannot happen in this group of stored procedures. Write your answer in yourlastname_yourfirstname_Lab13.txt
3) Give, in plain English, and optionally by using R (for read), and W (for write) notation that we used in class, an example of a schedule for this group of stored procedures that would lead to a non-repeatable read, or explain why a non-repeatable read cannot happen in this group of stored procedures. Write your answer in yourlastname_yourfirstname_Lab13.txt
4) Give, in plain English, and optionally by using R (for read), and W (for write) notation that we used in class, an example of a schedule for this group of stored procedures that would lead to a phantom read among this group of stored procedures, or explain why a phantom read cannot happen in this group of stored procedures. Write your answer in yourlastname_yourfirstname_Lab13.txt
5) What transaction isolation level would you use for each of the two procedures above, and why? For each procedure you should use the least restricted transaction isolation level that ensures correctness. Write your answer in yourlastname_yourfirstname_Lab13.txt
Part C (extra credit)
6) Modify the body of the spInsertProduct procedure you wrote for Lab 12 (or the one provided as the solution) to use transactions. You need to first declare the transaction isolation level you determined appropriate in Exercise 5 and then declare the transaction boundaries.
To set the transaction isolation level in a MySQL stored procedure, you should use
SET TRANSACTION ISOLATION LEVEL xxx
Where xxx is one of the following:
READ UNCOMMITTED
READ COMMITTED
REPETABLE READ
SERIALIZABLE
The commands used to start, abort or commit a transaction in MySQL stored procedures are:
START TRANSACTION
ROLLBACK
COMMIT
Write the code to create the new spInsertProduct stored procedure in yourlastname_yourfirstname_Lab13.txt.
Turn in. Both
electronic and paper copies are required:
Electronic (due
before 2359 on April 25, 2013):
Hard-copies (due
before start of lab on April 26, 2013):