Spring 2006
Stony Brook
Database Transaction Processing Systems
Annie Liu
Assignment 4
Handout A4
Mar. 2, 2006
Due Mar. 21

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.
Each experiment runs 10 terminals concurrently, using a same script, which calls a stored procedure repeatedly for 10 seconds:
  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).
Since IncreaseStock1 and IncreaseStock2 each does a $10 increase, we can calculate the number of lost updates based on the number of times IncreaseStock has been executed with the actual increase given to the stock at the end of the experiment. The number of deadlocks can be read off the results from TPPT.

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:
  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?


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.


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.