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
Each experiment runs the same two scripts, which call the two
stored procedures repeatedly for 20 seconds:
- ChangeToStock contains a transaction that changes a particular
bond to stock.
- 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
- Execute the scripts at SERIALIZABLE.
- 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
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
- Create the stored procedures ChangeToStock and CountStock.
- 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
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
- Create a prototype file containing
int ChangeToStock(Connection con);
int CountStock(Connection con);
- Create two script files:
and terminal1.scp contains
- 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.
- Run each of the two experiments for 20 seconds. You need to do
experiments on the SingleUser server.
- Modify CountStock by inserting a one second WAITFOR statement between the two SELECT statements and
rerun the two experiments.
- 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
all files required to run your experiments in TPPT,
timing results from TPPT (single user mode only), and
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.