Phantoms

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.

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
   SET TRANSACTION ISOLATION LEVEL 2
   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:
   • Create a schema file containing the Invest table as in Assignment 3, and the stored procedures you created. The CREATE TABLE statement should have the following form, to let Sybase lock the rows of the table individually:
CREATE TABLE Invest (
...
) lock datarows
   • Create an init file that uses an INSERT statement to initialize the Invest table as in Assignment 3, but use 3000 entries instead of 1000.
   • Create a prototype file containing
int ChangeToStock(Connection con);
int CountStock(Connection con);
   • Create two script files: terminal0.scp contains
>>
ChangeToStock
     and terminal1.scp contains
>>
CountStock
   • Create Java programs ChangeToStockClass.java and CountStockClass.java to invoke the two stored procedures, respectively. In particular, CountStockClass should count the number of times 1 is returned in a static variable and output the value each time it is invoked. The last value printed and the total number of times the transaction is run will give an idea of the likelihood that phantoms occur. Note that you must close the tool between experiments to reinitialize this variable.
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.

Handins

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.

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.