IT360 Lab 12 Locks and Transactions
DUE: May 5, 2010, BEFORE start of class
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_Lab12.txt
For this lab, we will use the MySQL database server on cardhu.cs.usna.edu instead of schooner.cs.usna.edu. Your username and password is the same as the one you have for schooner.
Connect to your MySQL database on cardhu.cs.usna.edu using MySQL Query Browser.
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 use the Product table, but with the InnoDB storage engine.
1) Re-create the Product table in your new database on cardhu.cs.usna.edu, but this time the table will use the InnoDB table engine instead of the default MyISAM engine.
create table Product(
BarCode int not null,
PName varchar(50) null,
Price double not null,
QuantityInStock double not null,
Constraint pk_product PRIMARY KEY(BarCode)
)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.
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_lab12.txt)
b) Why? (write the answer in yourlastname_lab12.txt)
SELECT * FROM Product
c) Do you see the product that you inserted in the other session? (write the answer in yourlastname_lab12.txt)
d) Why? (write the answer in yourlastname_lab12.txt)
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)
SaleItem (SaleID, BarCode, Quantity)
Assume that the following three types of transcations can run concurrently in your application (could even have multiple transactions of the same type running concurrently):
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
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.
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 class on May 5, 2010):