Subject Re: [Firebird-Java] Re: Transaction concurrency
Author Roman Rokytskyy
> //java pseudo code:
> lock table
> call stored proc
> commit
> unlock table
> What I found in the documentation:
> "
> locks entire tables for write access, although concurrent reads by
> other SNAPSHOT and
> READ COMMITTED transactions are permitted.
> "

That's exactly what you need.

> My questions:
> locking a table the way I want to is aquired by setting the
> transaction isolation level (SET TRANSACTION ISOLATION LEVEL...)?

Not 100% correct. You have to specify the table to reserve in so-called TPB.
JayBird hides Firebird specifics under transaction isolation, however you
cannot reserve the table via JDBC API yet (please fill feature request).

However, if I understand correctly, the first who modifies the table gets
the lock for the table. So you have to ensure that the first statement
modifies the table you want to lock. The lock should be automatically
released on commit or rollback. More info you get in Firebird-Support list.

> What's the difference between TRANSACTION_SERIALIZABLE and SNAPSHOT

They are equivalent. Firebird has three isolation levels: read committed,
concurrency and consistency. They are respectively mapped into
TRANSACTION_SERIALIZABLE. You can change the mapping yourself.

> Can I set the transaction isolation level in the SP,

No. Isolation level is set on the transaction start by the client.

> or do I have to turn autocommit off and set it in my application?
> (setting it in sp would be wiser)

Autocommit is nothing else as

start transaction
execute statement

JayBird uses the specified transaction isolation level. The following code
will work correctly:


Statement stmt = connection.createStatement();
stmt.execute("EXECUTE PROCEDURE ...");

where there first statement in the procedure locks the table.

> What colour is my hair?

This is not JayBird-related question, try Firebird-general list instead :))