IT360 Lab 9 Locks and Transactions

 

DUE: April 21, 2009, BEFORE start of lab

 

This lab should get you familiarized with transactions and concurrency-related problems.

 

The first part of this lab requires you to use transactions in MySQL and answer some questions. The second part requires you to just answer questions on paper. Write the answers to each of the following exercises in a file called yourlastname_Lab9.txt

 

Part 1:

Connect to your MySQL database on schooner.cs.usna.edu using MySQL Query Browser. For this exercise, we are going to work with the Products table that you created earlier.

 

Preamble: MySQL supports several types of storage engines, or table types. This means that you have a choice in the underlying implementation of the tables. The default storage engine is MyISAM, which does not support foreign keys (it ignores the foreign key declaration, instead of enforcing the foreign key referential integrity constraint) and does not support the ACID transactions we covered in class. InnoDB is another storage engine. InnoDB supports transactions and foreign key constraints. For this lab, we will convert the Products table to use the InnoDB storage engine.

 

1)     Convert your Products table to use InnoDB engine instead of MyISAM engine by executing the following command in MySQL Query Browser (you can convert the table back to MyISAM later, of you want):

 

ALTER TABLE Products Engine = InnoDB;

 

2)     Remember that 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 Products table, by executing an insert statement appropriate for your table. For example,

 

INSERT INTO Products(BarCode, PName, Price, QuantityInStoc) VALUES(5, ‘Bagel’, 0.99, 100) would insert one Product with BarCode 5 in my Products table. 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 Products

 

    • Open now a new MySQL Query Browser session by starting MySQL Query Browser and connecting to your database on schooner.cs.usna.edu

 

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

 

SELECT * FROM Products

 

a)     Do you see the product that you just inserted in the other session?  (write the answer in yourlastname_lab9.txt)

 

b)     Why? (write the answer in yourlastname_lab9.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 thecontent of the Product table executing a Select statement, for example

 

SELECT * FROM Products

 

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

 

d)     Why? (write the answer in yourlastname_lab9.txt)

 

 

Part 2:

The questions in this part are related to possible operations in the Online Store database. Of special interest for this lab are the following four tables (slightly modified for this lab only):

 

PRODUCT(BarCode, PName, Price, QuantityInStock)

CUSTOMER (CustomerID, Phone, Email, FirstName, LastName)

SALE (SaleID, DeliveryAddress, CreditCard, SaleDate, CustomerID, SaleTotal)

SALE_PRODUCT (SaleID, BarCode, Quantity)

 

Assume that the following three stored procedures can run concurrently in your application:

 

create_product:  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.

 

create_modify_sale: records new or modifies existing customer sale and sale-product information. This procedure writes to the SALE and SALE_PRODUCT tables, updates the PRODUCT table, and may be reading from the CUSTOMER and PRODUCT tables.

create_customer: records new customer data. This procedure uses only the CUSTOMER table to insert new customers.

 

1) Give a scenario that leads to a possible dirty read among this group of stored procedures, or explain why a dirty read cannot happen in this group of stored procedures.

 

2) Give a scenario that leads to 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.

 

3) Give 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.

 

4) What transaction isolation level would you use for each of the three procedures above, and why?   For each procedure you should use the least restricted transaction isolation level that ensures correctness.

 

5)     Which of the given problems can occur if the following transactions run concurrently?

 

T1:  X(A), R(A), W(A), X(B), R(B), W(B), Commit

T2:  X(B), R(B), W(B),  S(A), R(A), Abort

 

X - acquire exclusive lock, S – acquire shared lock, R – read, W – write. All locks are released during commit or abort.

 

YES     NO      Why?

Deadlock                                 ____    ____    ____________________________________

Dirty read in T1                      ____    ____    ____________________________________

Nonrepeatable read in T2       ____    ____    ____________________________________

Lost update in T1                    ____    ____    ____________________________________

 

 

Turn in (due before start of lab on April 21, 2009):

Electronic:

  1. Upload yourlastname_Lab9.txt with all your answers to the Lab 9 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_Lab9.txt containing the answers for each exercise.