SOLUTION IT360 Lab 13 Transactions and Concurrency SOLUTION

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:

• Start a transaction by clicking the “Toggle auto-commit mode” button in the MySQL Workbench tool bar.

• Insert one product in the Product table, by executing an insert statement appropriate for your table. For example,

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.

• Check that the item was inserted by executing a Select statement, for example

SELECT * FROM Product

• Open now a new MySQL Workbench session by starting MySQL Workbench and connecting to your database on cardhu.cs.usna.edu

• Check the content of the Product table by executing a Select statement, for example

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)

Answer: No

b) Why?

Answer: Because the transactions are “isolated”. We cannot see the effects of a transaction until the transaction is committed.

• Go back to your first MySQL workbench session and click on the green “Check” (Commit Transaction) button.

• Go now to the second MySQL Workbench session and check the content of the Product table executing a Select statement, for example

SELECT * FROM Product

c) Do you see the product that you inserted in the other session? (write the answer in yourlastname_yourfirstname_Lab13.txt)

Answer: Yes

d) Why?

Answer: The transaction was committed; the effects are durable and visible to all

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 procedures of the same type 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 Sale and SaleItem tables, updates the Product table, and reads from the Product table, possibly multiple times.

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

Solution: A dirty read means reading data that was not committed. It could happen for two concurrent runs of the spInsertModifySale, if one modifies a product, and the other one reads the modified information before it is committed.

IP: W(Product) Abort

IMS: R(Product)…

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

Solution: A nonrepeatable read means reading the same row twice and getting different values for some column. It would occur if two users run the spInsertModifySale procedure in the same time, to modify the sale of a particular product. The first user could read the value of QuantityInStock for example, find some value, and then read again the QuantityInStock. If the second user in the meantime changed the value of QuantityInStock (for example some customer bought an item), the first user sees a value for QuantityInStock different than the value previously read. This would be a non-repeatable read.

IMS1: R(Product) R(Product) ….

IMS2: … R(Product) W(Product) Commit

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

Solution: A phantom read happens when a transaction reads twice, and gets different rows back. It would occur when spInsertModifySale reads from PRODUCT all the products satisfying some search criteria, and then reads from PRODUCT again after spInsertProduct inserted a new product matching the criteria and committed, so new products appear in the second read.

IMS: R(Product) R(Product) …

IP: W(Product) Commit

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

Solution:

Use read uncommitted for spInsertProduct since that procedure just inserts rows and is not reading from any table.

Use serializable isolation level for spInsertModifySale, since this procedure reads and modified tables, and all inconsistent read problems (dirty read, non-repeatable read, and phantom read) are possible. We therefore want the database to prevent those problems, so customers do not see incorrect results at any time.

Part C (Extra credit)

6) Modify the spInsertProduct procedure you wrote for Lab 11 (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.

delimiter //

create procedure spInsertProduct(insBarCode int,

insPName varchar(50),

insPrice double,

insQuantityInStock double)

begin

SET TRANSACTION LEVEL READ UNCOMMITTED;

START TRANSACTION;

if (insPrice > 0 and insQuantityinStock >= 0) then

insert into Products(BarCode, PName, Price, QuantityInStock)

values (insBarCode, insPName, insPrice, insQuantityInStock);

end if;

COMMIT;

end //

delimiter ;