IT360 Lab 11 Locks and Transactions

 

DUE: April 25, 2011, BEFORE start of lab

 

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_Lab11.txt

 

 

Part A:

Connect to your MySQL database on cs-mysqlsrvr.cs.usna.edu using MySQL Query Browser.

 

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”. For this exercise you will experiment with this behavior.

    • Start a transaction by clicking the “Transaction à” button in the MySQL Query Browser 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 Query Browser session by starting MySQL Query Browser and connecting to your database on cs-mysqlsrvr.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_lab11.txt)

 

b)      Why? (write the answer in yourlastname_lab11.txt)

 

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

 

    • Go now to the second MySQL Query Browser 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_lab11.txt)

 

d)     Why? (write the answer in yourlastname_lab11.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 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 possible dirty read among this group of stored procedures, or explain why a dirty read cannot happen in this group of stored procedures. Write your answer in yourlastname_lab11.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 possible non-repeatable read among this group of stored procedures, or explain why a non-repeatable read cannot happen in this group of stored procedures. Write your answer in yourlastname_lab11.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 possible 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_lab11.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_lab11.txt

 

 

Part C

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

READ COMMITED

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_lab11.txt;

 

Turn in (due before start of lab on April 25, 2011):

Electronic:

  1. Upload yourlastname_lab11.txt with all your answers including the code for spInsertProduct stored procedure to the Lab 11 assignment on the blackboard.

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of your yourlastname_Lab11.txt containing the answers for each exercise in PartA, B, and C.