Isolation Levels, Lost Updates, and Deadlocks

Lost updates can occur when locking is short term, as with the isolation level of READ COMMITTED. Deadlocks can occur when concurrent transactions request a same set of locks, each holding some of the locks and waiting for the other locks. The purpose of the experiment is to study the effect of different transaction code and isolation levels on lost updates and deadlocks.

The task is to repeatedly increase a specific stock by $10 until the allotted time runs out, using the same Invest table and data as in Assignment 2. You are to implement the task in two ways, by calling two different stored procedures, and then do two sets of experiments with each implementation using TPPT. Both stored procedures do a single $10 increase:

1. IncreaseStock1 uses a single UPDATE statement to do the $10 increase.
2. IncreaseStock2 uses a SELECT statement to fetch the stock amount, increases it by $10, and then uses an UPDATE statement to store the result back in the table.

1. All scripts call IncreaseStock1 and execute at the default isolation level (SERIALIZABLE).
2. All scripts call IncreaseStock1 and execute at READ UNCOMMITTED (isolation level 1).
3. All scripts call IncreaseStock2 and execute at READ COMMITTED (isolation level 2).
4. All scripts call IncreaseStock2 and execute at SERIALIZABLE (isolation level 8).

What to do

1. Create the stored procedures IncreaseStock1 and IncreaseStock2.
2. Prepare to run TPPT; you may start with the example files from Assignment 3, but the following is all you need to set up:
   • Create a schema file containing the Invest table and the Insert statements as in Assignment 2, and the stored procedures you created for this assignment.
   • Let the initdata file be empty.
   • Let the file prototype.txt contain only "int IncreaseStock(Connection con);"
   • Let the script files, terminal0.scp to terminal9.scp, each contain "
     >>
     IncreaseStock"
   • Create a Java program IncreaseStockClass.java that defines "int IncreaseStock(Connection con)" and invokes the stored procedure IncreaseStock1 or IncreaseStock2 depending on the experiment.
3. Run each of the four experiments with 10 terminals for 10 seconds. You only need to do experiments on the UnderMultiUser server.
4. Modify IncreaseStock2 by inserting a one second WAITFOR statement between the SELECT and the UPDATE and repeat the two experiments with IncreaseStock2.
5. Prepare an analysis of the results: create a table presenting the number of lost updates and deadlocks for each of the experiments; discuss the relationships among different transaction code, isolation levels, lost updates, and deadlocks; why is there a difference between performing the update in a single statement and in two statements, and why does the WAITFOR statement affect the result?

Handins

Hand in your description and code electronically, using Blackboard, by midnight on the due date. Your handin should include

1. your Java/JDBC source code and the schema file,
2. timing results produced by the tool and the final state of the Invest table, and
3. your analysis of the results.

Grading

This homework will be graded based on 100 points, allocated in proportion to the required amount of work for each part. You may do this assignment in a team of two people; the two people will receive the same points.