Hi,

I have several threads trying to update a table at once, let's say
called TEST_LOCK. They all use the same method (this is in a
clustered environment however), so they are all using the same
transaction parameters. I need the first thread to get a lock on the
table which prevents the other threads from even reading it. There
will be other threads using read-only transactions to be able to read
the table, but they do not need to see the updates until the
transaction finishes.

So, from ch 26 & 27 of the Firebird book, I deduced that I need the
following:
ACCESS MODE: READ WRITE
ISOLATION LEVEL: SNAPSHOT
LOCK RESOLUTION: WAIT
TABLE RESERVATION: PROTECTED WRITE

To verify this, I entered the following in the isql console:

SQL> set transaction read write wait isolation level snapshot
reserving test_lock for protected write;

This gets the correct lock, as far as I can tell, and if I enter the
same command in another console window, I wait until I go back to the
original one and issue a commit.

I am actually connecting to Firebird via Jaybird, but do not see the
same expected behaviour when I pass the equivalent parameters across.
I have already posted this on the Jaybird list, but wanted to
confirm whether this is a Jaybird or Firebird issue, and whether the
behaviour I am seeing is incorrect or not.

When I specify the equivalent settings for the transaction via
Jaybird, and issue a SELECT statement to begin the transaction, I do
not get the correct lock (I created a simple test program which I run
in 2 console windows). The settings I use, along with TPB Constant
equivalent:

JAYBIRD TPB Constant
Access mode READ WRITE isc_tpb_write
Isolation level SNAPSHOT isc_tpb_concurrency
Lock resolution WAIT isc_tpb_wait
Table Reservation PROTECTED isc_tpb_protected
WRITE isc_tpb_lock_write


After much wall banging, it became apparent that there are 2 issues:
a) I have to use SNAPSHOT TABLE STABILITY, and b) I have to issue an
INSERT statement to begin the transaction.

I find both of these are unexpected! First of all, the same kind of
transaction lock should be created whether I do a SELECT or INSERT as
the first statement, right? Using Table Reservation Protected Write,
no other transaction with the same table reservation setting should be
even able to read the table, if I am reading The Firebird Book correctly.

Second, the Firebird Book also states that Table Reservation should
not be used with SNAPSHOT TABLE STABILITY, so SNAPSHOT along with the
PROTECTED WRITE table reservation, should give me what I need.

This post is already long enough, so I will post code snippet below.

Is this a bug? The Jaybird driver should just be calling the
corresponding isc_tpb_xxx constants, right? If so, is it a bug in
Firebird?

Please help!!

cheers,

David


Here's my test program which you can run in 2 console windows. This
version does not work, but changing to use
TransactionParameterBuffer.CONSISTENCY and the insert statement does:

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.firebirdsql.gds.TransactionParameterBuffer;
import org.firebirdsql.jdbc.FBDriver;
import org.firebirdsql.jdbc.FirebirdConnection;

/**
* Test isolation in Firebird extensions.
*
* @author dhay
*/
public class GetFirebirdLock
{

private static BufferedReader stdin = new BufferedReader(new
InputStreamReader( System.in ) );

public static void main(String args[])
{

GetFirebirdLock gfl = new GetFirebirdLock();
gfl.getLock();
}

/*
* Get lock on test table
private void getLock()
{
try
{
//get connection
FirebirdConnection fbConnection =
getConnectionViaDriverManager();
System.out.println(Thread.currentThread().getId() + "
connection obtained for this thread: " + fbConnection);

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

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

//SNAPSHOT Isolation level - only seems to work with
SNAPSHOT TABLE STABILITY ie CONSISTENCY param
tpb.addArgument(TransactionParameterBuffer.CONCURRENCY);
//READ WRITE access
tpb.addArgument(TransactionParameterBuffer.WRITE);
//WAIT lock resolution
tpb.addArgument(TransactionParameterBuffer.WAIT);
//PROTECTED WRITE Reservation on PrinterGroup table
tpb.addArgument(TransactionParameterBuffer.PROTECTED);
tpb.addArgument(TransactionParameterBuffer.LOCK_WRITE,
"TEST_LOCK");

fbConnection.setTransactionParameters(tpb);

Statement stmt = fbConnection.createStatement();
try
{
String sql = "SELECT * FROM TEST_LOCK"; //doesn't work!
//String sql = "INSERT INTO test_lock VALUES(1)";

System.out.println(Thread.currentThread().getId() + "
about to execute sql: " + sql);
stmt.execute(sql);
System.out.println(Thread.currentThread().getId() + "
SHOULD HAVE TABLE LOCK - executed sql " + sql);
stmt.close();

fbConnection.commit();
System.out.println(Thread.currentThread().getId() + "
COMMIT PERFORMED");
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
System.out.println(Thread.currentThread().getId() + "
closing statement");
stmt.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
fbConnection.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}

protected FirebirdConnection getConnectionViaDriverManager() throws
SQLException
{
try {
Class.forName(FBDriver.class.getName());
} catch(ClassNotFoundException ex) {
throw new SQLException("No suitable driver.");
}

return (FirebirdConnection)
DriverManager.getConnection(getUrl(),
getDefaultPropertiesForConnection());
}

protected Properties getDefaultPropertiesForConnection()
{
final Properties returnValue = new Properties();

returnValue.setProperty("user", "username");
returnValue.setProperty("password", "password");
returnValue.setProperty("lc_ctype",
System.getProperty("test.db.lc_ctype", "NONE"));

return returnValue;
}

protected String getUrl()
{
String url="jdbc:firebirdsql:localhost/3050:DATABASE_NAME";
return url;
}
}