Spring 2006
Stony Brook
Database Transaction Processing Systems
Annie Liu
Assignment 5
Handout A5
Mar. 24, 2006
Due Apr. 6


Phantoms can be prevented at the SERIALIZABLE isolation level, not at REPEATABLE READ or even lower levels. However, a higher performance price is paid at the SERIALIZABLE level than the lower levels. The purpose of this experiment is to study this tradeoff.

The task is to repeatedly change a specific bond to stock and count the number of stocks until the allotted time runs out, using the same Invest table and data as in Assignment 3. You are to run the same two transactions for the change and count, at SERIALIZABLE and REPEATABLE READ respectively in two experiments, and check for phantoms and measure response time. Two stored procedures are used for the two transactions:

  1. ChangeToStock contains a transaction that changes a particular bond to stock.
  2. CountStock contains a transaction that uses a SELECT statement to count the number of stocks twice, and returns 1 if the two results are different, meaning at least one phantom has been inserted, and returns -1 otherwise.
Each experiment runs the same two scripts, which call the two stored procedures repeatedly for 20 seconds:
  1. Execute the scripts at SERIALIZABLE.
  2. Execute the scripts at REPEATABLE READ, but, since TPPT does not currently support REPEATABLE READ, you will have to set the isolation level yourself within the stored procedures and outside transaction. You can do this using the command
    before you begin the transaction in the stored procedure. This command overrides the isolation level set by TPPT.

What to do

  1. Create the stored procedures ChangeToStock and CountStock.
  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 two experiments for 20 seconds. You need to do experiments on the SingleUser server.
  4. Modify CountStock by inserting a one second WAITFOR statement between the two SELECT statements and rerun the two experiments.
  5. Prepare an analysis of the results: compare the response time for the two transactions and the ratio of the number of phantoms to the total number of times the transaction is run when the experiment is executed at SERIALIZABLE and REPEATABLE READ and with and without the delay statement; discuss your results.


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

  1. all files required to run your experiments in TPPT,
  2. timing results from TPPT (single user mode only), 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.