Hi,

I am very perplexed! I am trying to establish the following transaction /
isolation properties on connections on different Java threads, and am
either missing something, or encountering some wierdness!!
ACCESS MODE: READ WRITE
ISOLATION LEVEL: SNAPSHOT
LOCK RESOLUTION: WAIT
TABLE RESERVATION: PROTECTED WRITE
ie I want to lock the table for writing at the beginning of the
transaction, stopping other transactions with the same settings from
reading the table, but allowing other connections with read only access the
ability to read the table.

In isql I can simply type the following in 2 consoles, and achieve this
behaviour:

SQL> set transaction read write wait isolation level snapshot
reserving test_lock for protected write;
Thus, if I execute it in one console, it gets the lock, and when I execute
it in the other, it waits. If I switch back to the original console and
commit, then the other console gets the lock.

I have set up several test cases to try and do this programmatically, and
that's where the wierdness enters. I modified the testLockTable() testcase
in TestFBConnection.java, setting the TBP for both connections to:

//set up transaction params
TransactionParameterBuffer tpb =
fbConnection.createTransactionParameterBuffer();

//SNAPSHOT Isolation level
tpb.addArgument(TransactionParameterBuffer.CONCURRENCY);
//READ WRITE access
tpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
tpb.addArgument(TransactionParameterBuffer.WAIT);
//PROTECTED WRITE Reservation on Test_Lock table
tpb.addArgument(TransactionParameterBuffer.PROTECTED);
tpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

fbConnection.setTransactionParameters(tpb);

//turn autocommit off
fbConnection.setAutoCommit(false);

I then ran the test case and it failed. However, if I add a
Thread.sleep(10000) when the connection is supposed to have the lock, and
then enter the command above in the isql window, it does indeed have the
lock and isql connection has to wait for the 10+ seconds, and then gets the
lock after the commit.

So, it seems that the above parameters will produce the desired lock, but
not between connections created in the same testcase. I also created a
testcase using 2 threads, each identical, creating a connection, setting
the tbp, issuing a select (which should give it the lock, per my last
question) and an insert. Again, I see the same thing. I can pause it and
see it have the lock and isql have to wait, but when run by itself, both
threads seem to have the same lock. If I start with a lock in isql, both
threads run as far as trying to execute the Select statement, and then when
isql lock is released, both threads seem to get the lock and both threads
proceed!

The modified code from testLockTable() is below. I can supply code for the
2 threads testcase if required.

Any ideas? What am I missing?

Help much appreciated!!

cheers,

David


modified testLockTable:

public void testFirebirdsLockTable() throws Exception
{
FirebirdConnection fbConnection =
(FirebirdConnection)getConnectionViaDriverManager();

try {
Statement stmt = fbConnection.createStatement();
try {
stmt.execute("CREATE TABLE test_lock(col1 INTEGER)");
} catch(SQLException ex) {
// ignore
}
} finally {
fbConnection.close();
}

fbConnection = (FirebirdConnection)getConnectionViaDriverManager();
try {

Statement stmt = fbConnection.createStatement();

try {

//set up transaction params
TransactionParameterBuffer tpb =
fbConnection.createTransactionParameterBuffer();

//SNAPSHOT Isolation level
tpb.addArgument(TransactionParameterBuffer.CONCURRENCY);
//READ WRITE access
tpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
tpb.addArgument(TransactionParameterBuffer.NOWAIT);
//PROTECTED WRITE Reservation on PrinterGroup table
tpb.addArgument(TransactionParameterBuffer.PROTECTED);
tpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

fbConnection.setTransactionParameters(tpb);

//turn autocommit off
fbConnection.setAutoCommit(false);
System.out.println(fbconnection - autocommit set to false");


FirebirdConnection anotherConnection =
(FirebirdConnection)getConnectionViaDriverManager();
anotherConnection.setAutoCommit(false);

try {
//set up transaction params
TransactionParameterBuffer atpb =
anotherConnection.createTransactionParameterBuffer();

//SNAPSHOT Isolation level
atpb.addArgument(TransactionParameterBuffer.CONCURRENCY);
//READ WRITE access
atpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
atpb.addArgument(TransactionParameterBuffer.NOWAIT);
//PROTECTED WRITE Reservation on PrinterGroup table
atpb.addArgument(TransactionParameterBuffer.PROTECTED);
atpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

anotherConnection.setTransactionParameters(atpb);

Statement anotherStmt =
anotherConnection.createStatement();
try {
anotherStmt.execute("INSERT INTO test_lock
VALUES(1)");
System.out.println("inserted ok via
anotherConnection");
} finally {
anotherStmt.close();
}

try {
stmt.execute("INSERT INTO test_lock VALUES(2)");
System.out.println("inserted ok via fbConnection");
fail("Should throw an error because of lock
conflict.");
} catch(SQLException ex) {
ex.printStackTrace();
assertEquals(ISCConstants.isc_lock_conflict,
ex.getErrorCode());
}

} finally {
anotherConnection.close();
}

} finally {
stmt.close();
}
} finally {
fbConnection.close();
}
}