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,
) 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.
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
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:
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.