Hi

I'm using the v8 JDBC driver to access a PSQL v8.6 database. I have
set up a test to see whether a connection with an isolation level of
serialable will prevent phantom reads, as specified by the ANSI/ISO
SQL standard.

Unfortunately, it does not appear to perform as advertised :

"Supports transactions isolation levels supported by the
Pervasive.SQL engine, for example READ_COMMITTED, serializable " -
lifted from PSQL 8 JDBC driver documentation.

The code used and the specification of the test table is listed below.
Any help would be appreciated!

package crossflight;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import crossflight.dao.hibernate.SQLTestUtils;

### Code ###
/**
*
* @author Ben Morgan
*
*/
public class LockingTest {

@Test
public void serializableNoPhantomRead() throws Exception {

// set up
Class.forName( "com.pervasive.jdbc.v2.Driver" );
Connection connection1 = null;
Connection connection2 = null;

try {

connection1 = DriverManager.getConnection( "jdbcervasive://server/
database" , "" , "" );
connection2 = DriverManager.getConnection( "jdbcervasive://server/
database" , "" , "" );


connection1.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );

connection1.setAutoCommit( false );
connection2.setAutoCommit( false );

ResultSet rs1 = connection1.createStatement().executeQuery( "select
* from testIsolation where criterionField='test'" );
int setCounter1 = 0;
while( rs1.next() ) setCounter1++;

connection2.createStatement().execute( "insert into testisolation
(criterionfield) values ( 'test' )" );
connection2.commit();

ResultSet rs2 = connection1.createStatement().executeQuery( "select
* from testisolation where criterionfield='test'" );
int setCounter2 = 0;
while( rs2.next() ) setCounter2++;

// verify
assertEquals( "Should be equal" , setCounter1 , setCounter2 );

connection1.rollback();

} catch( Exception ex ) {
throw ex;
}
finally {

connection1.close();
connection2.close();

}

}

}

### Table definition ###

create table testIsolation (
id identity not null,
criterionField varchar(4) not null
)#

I'm no Java expert, but if you want the counts to be the same,
shouldn't you REMOVE the connection2.commit() call???

My understanding is that the commit there will allow the data to be
viewable by anyone else immediately thereafter.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***

Ben Morgan wrote:

I've picked up the following transaction schedule from the SQL-92
specification (http://www.contrib.andrew.cmu.edu/~shadow/sql/
sql1992.txt) that helps helps define a phantom read.

- Transaction T1 reads a set of data items satisfying some <search
condition>
- Transaction T2 then creates data items that satisfy T1's <search
condition> and commits
- If T1 then repeats its read with the same <search condition>, it
gets a set of data items different from the first read.

I believe the code attempts to recreate the above schedule and that
the commit is an requisite part of that. According to the
specification, a transaction with a serializable isolation level,
should not allow phantom reads, but my code creates one.

Either my code is incorrect, or the Pervasive JDBC driver doesn't
support the serializable isolation as it says. I hope it is the
former!

Incidentally, we have tried this with the latest jdbc driver on PSQL
v9 and that doesn't work either.

Thanks

Ben

On Feb 8, 1:16 pm, "Bill Bach" <golds... (AT) speakeasy (DOT) net> wrote:

Whew. Well beyond MY expertise, I can say.

I would recommend logging a formal incident with Pervasive technical
support. They would need to have a code sample and mini database -- if
you can duplicate with simple code and the DEMODATA database, that
would be best. However, they would be in the best position to analyze
and address this issue, if needed.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***

Ben Morgan wrote:

Thanks Bill. Will do.

On Feb 9, 3:01 am, "Bill Bach" <golds... (AT) speakeasy (DOT) net> wrote: