Hi,

I DO need full table lock, and have changed to use the Firebird connection
pool.

I've also looked at the TPB settings required, but am confused as they
don't seem to match up with SNAPSHOT TABLE STABILITY as per Firebird book.

Could you enlighten me as which attributes I need to set to accomplish full
table lock, with no read access from another tx?

cheers,

David
x54680


|---------+----------------------------->
| | "Roman Rokytskyy" |
| | <rrokytskyy@...|
| | g> |
| | Sent by: |
| | Firebird-Java@yaho|
| | ogroups.com |
| | |
| | |
| | 01/12/2006 06:36 |
| | PM |
| | Please respond to |
| | Firebird-Java |
| | |
|---------+----------------------------->

>------------------------------------------------------------------------------------------------------------------------------|

| |
| To: <Firebird-Java@yahoogroups.com> |
| cc: |
| Subject: Re: [Firebird-Java] Setting Isolation Level with Hibernate |

>------------------------------------------------------------------------------------------------------------------------------|




> We're using Hibernate (and Spring) with Firebird as our database.
>
> I need to specify the isolation level, but Firebird doesn't seem to
> implement the standard sql isolation levels, except READ_COMMITTED.
>
> I can get the connection that Hibernate is using, but I'm guessing that I
> can't set the isolation level of SNAPSHOT TABLE STABILITY through that?

The question is what instance do you get. If you do not use connection
pooling and Session.getConnection() returns the original JDBC connection,
you can cast it to the FirebirdConnection interface and use appropriate
methods to set the desired TPB. This is not possible with connection
pooling
unless dynamic proxies are used and all interfaces are exported by the
dynamic proxies (btw, our connection pool does this).

On the other hand you don't need it. JDBC isolation levels specify what
anomalies can and what cannot appear on that isolation level. Jaybird
mapped
its TPBs to meet those specifications in the following way:

- TRANSACTION_READ_COMMITTED is mapped to READ COMMITTED (no surprise)

- TRANSACTION_REPEATABLE_READ is mapped to SNAPSHOT (isc_tbp_concurrency,
isc_tpb_write, isc_tpb_wait).

- TRANSACTION_SERIALIZABLE is mapped to SNAPSHOT TABLE STABILITY without
table reservation. This mapping is questionable, since SNAPSHOT isolation
in
Firebird fulfills are requiremenets to SERIALIZABLE isolation level in
JDBC,
however provides much more better concurrency than traditional locking
systems. However, we just tried to keep the "order" between isolation,
where
next level is stronger than the previous one.

So, in your case if you need just SNAPSHOT TABLE STABILITY without table
reservation, you simply have to specify the JDBC isolation level
TRANSACTION_SERIALIZABLE.

The actual mapping is stored in the .properties file in jaybird-2.0.1.jar,
but you can override this by setting the connection property tpbMapping to
point to your file (format is the one used in ResourceBundle and specifies
path relative to the classloader root - "com.mycompany.tpbMapping" is
translated into "/com/mycompany/tpbMapping.properties" path).

If you want to use table reservations, you can only use JDBC extensions in
the FirebirdConnection interface, there you get full control over the TPB,
you can set it for next transaction only or to override the mapping for
some
specific isolation level.

Roman




Yahoo! Groups Links