Thanks for the idea. Yes, that would be possible, but less than ideal.

The table reservation should work - my question is am I missing
something, or is it a bug?

cheers,

David

--- In firebird-support@yahoogroups.com, "mikcaau" <macomp@s...> wrote:
>
> Use a token in your app.
> Have a table of tokens for me they are generally a Guid and a
description.
> First transaction gets an update lock on the token,
> does what it needs then rolls back the update.
>
> Other threads fail to get update lock on token and exit
> Mick
>
--- In firebird-support@yahoogroups.com, "hay77772000" <dhay@l...> wrote:
>
> 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;
> }
> }
>