In this experiment we investigate
phantoms. Phantoms can be prevented at the SERIALIZABLEisolation level, but a performance price is
paid. To investigate this we run the same pair of transactions at SERIALIZABLE
and REPEATABLE READ,
check for phantoms and measure response time.
Table Student will be
used:
CREATE TABLE Student (
Id char(9) NOT NULL,
Name char(50) NOT NULL,
Password char(20) NOT NULL,
Validity char(1) NOT NULL,
Town char(20)
) lock datarows
Two stored procedures (transactions) will
be used: ChangeTown and SelectInfo.
1.
ChangeTown
contains a transaction that changes the town of a particular student to 'stony
brook' :
update
Student set town = 'stony brook'
where
studId =
(select min(studId)
from Student where town != 'stony brook')
2.
SelectInfo contains a transaction that uses a
SELECT statement to count the number of students living in 'stony brook' twice and compares the
result.If the result is different at least one phantom
has been inserted.
Steps:
Here is the files you may need:
ClientJava.zip.
1. Prepare a schema file that creates the table Student and the stored procedures ChangeTown and SelectInfo. You may refer to the schema file in previous experiments.
l The CREATE TABLE statements should have the form:
CREATE TABLE t
( ...)
lock datarows
This causes
Sybase to lock the rows of the table individually.
l The SelectInfo stored procedure should return 0 or 1 to your java program depending on whether a phantom has or has not been detected. (returning 1 means that there is a phantom.) You should create two versions of this stored procedure: one with the statement
Waitfor delay '00:00:01'
and one without.
2. Prepare an init file that initializes the Student table with 3000 rows by following command:
INSERT INTO Student
SELECT * FROM tppt.tppt.initStudent WHERE studId <='100003000'
3. Prepare a prototype file containing two entries:
int ChangeTown(Connection con);
int SelectInfo(Connection con);
4. Prepare two script files:
terminal0.scp:
>>
ChangeTown
terminal1.scp:
>>
SelectInfo
5. Prepare java programs ChangeTownClass.java and SelectInfoClass.java to invoke the stored procedures. SelectInfoClass 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. (You must close the tool between experiments to reinitialize this variable)
6. Execute the two scripts at SERIALIZABLE for 20 seconds with and without the delay statement.
7. Since the tool 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 stored procedure and rerun the experiment with and without the delay statement. The command overrides the isolation level set by the tool.
Compare the response time for the two transactions (don't include transactions other than the above during comparision) 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. Submit a zip file in blackboard containing the complete report, timing results from TPPT (single user mode only), java output (if any) and all files required to run the code in TPPT.