SERIALIZABLE is not isc_tpb_consistency [JDBC21]

[firebird-automations]

Submitted by: @samofatov

Assigned to: David Jencks (d_jencks)

Attachments: FBTpb.java.diff

SFID: 575397#⁠ Submitted By: skidder

New Firebird SQL Type 4 JDBC driver uses isc_tpb_consistency transation flag for SERIALIZABLE isolation mode. This is wrong. InterClient correctly used isc_tpb_concurrency (SNAPSHOT mode) flag for both REPEATABLE_READ and SERIALIZABLE isolation levels. isc_tpb_consistency is equialent to SNAPSHOT TABLE STABILITY transaction mode, which is very restictive and does not exploit huge benefits of FireBird versioning engine. This bug prohibits reading of records which are modified by another uncommited transaction.

I use current CVS version (29 Apr 2002) which has all bugs I previously submitted fixed.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-06-10 20:53 Sender: rrokytskyy Logged In: YES user_id=356832

JayBird 2.0 allows overriding the TPB mapping on all levels (JCA/DriverManager/pool). We keep old mapping since it is expected that SERIALIZABLE is stronger than REPEATABLE READ. We let the application developer to determine appropriate mapping for the application.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2002-09-30 23:29 Sender: rrokytskyy Logged In: YES user_id=356832

I think we should accept these changes (even I was opposing them initially). TRANSACTION_SERIALIZABLE is defined as "dirty reads, non-repeatable reads and phantom reads are prevented". isc_tpb_concurrency satisfies these conditions, but is weaker than isc_tpb_consistency. We should use it, simply because we can reduce number of conflicts without compromising data consistency.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2002-07-30 12:17 Sender: skidder Logged In: YES user_id=495356

One more comment: isc_tbp_consistency+isc_tpb_write is weaker isolation mode than isc_tpb_concurrency. It still conforms to java and ANSI SERIALIZABLE definition (it prevents phantom records for repeated execution of queries - in fact more, any queries accessing the same tables) and its behavior is the same as MSSQL SERIALIZABLE with TABLOCK hint. But snapshot transaction sees all tables data in state it was when transaction started (like Oracle serializable isolation). Snapshot table stablility transaction instead can see changes commited to a table after this transaction started but before this table was accessed by this transaction (MSSQL-like behavior). This can lead to problems if you rely on global order of timestamp values or order of values created by shared generator - this is sometimes used in distributed systems for replication purposes.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2002-07-30 11:18 Sender: skidder Logged In: YES user_id=495356

I was in a hurry writing last statement. It had to be: --- The patch I submitted is quick and dirty fix to map JDBC REPEATABLE READ and SERIALIZABLE to snapshot mode. If somebody need snapshot table stability mode he can use it via Firebird Connection object. My opinion is that standard flags should provide standard semantics at as low price as possible. Best regards, Nickolay Samofatov

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2002-07-30 10:54 Sender: skidder Logged In: YES user_id=495356

A little background: I manage development of large distributed system (state budget controlling system for russian regions - it contains >>1000 DB tables). It is cross-platform and allows to use many database engines (Firebird, Oracle and Microsoft SQL Server at least). So we need JDBC driver to be fast and standard. Our new system works in DB serializable mode most of the time. When used properly Firebird allows us to run up to several hundreds active users (>1000 in Tumen region). This transactional issue is important only for UNIX (Linux, Solaris, Darwin) classic version because superserver version has global mutex around engine and execute SQL requests sequentally and cannot be used in large systems. --- Snapshot mode seems to provide serializable semantics (with Oracle-like parallelizm). At least our tests show that and Interbase documentation states it too. Snapshot table stability also provides serializable semantics, but at price of sequental execution of transactions on same tables - like MSSQL does. I can paste you serializable isolation level definition from Oracle 9i documentation so you can compare it with snapshot mode definition from Interbase documentation - it is the same thing. --- The patch I submitted is quick and dirty fix to map JDBC READ COMMITED and SERIALIZABLE to snapshot mode. I'd provide access to transaction parameters from database URL to make it cleaner.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2002-07-29 21:22 Sender: d_jencks Logged In: YES user_id=60525

I do not understand at all from your comment why you think this is a bug or what you want the behavior to be. (Well, I now see you supplied a patch, but no explanation) As I understand the situation the firebird transaction isolation modes do not exactly duplicate the standard transaction isolation semantics, and in my opinion the ones provided by firebird are more useful than the standard ones, so I have no interest in changing this.

Given this, I think it's appropriate to supply the widest range of firebird options from the standard labels, with the closest agreement between firebird meaning and standard meaning. I think the current mapping does this. The firebird default snapshot mode is stronger than normal repeatable read and weaker than serializable. My understanding (which may well be wrong) is that snapshot table stability does indeed provide serializable semantics. In any case it is stronger than snapshot, just as serializable is stronger than repeatable read. If you disagree, please supply the mapping you would like to see implemented with specific comments about why it is more appropriate than what we have now.

thanks david jencks

[firebird-automations]

Commented by: @pcisar

Reopened just to add attachment.

[firebird-automations]

Modified by: @pcisar

status: Closed \[ 6 \] =\> Reopened \[ 4 \] SF\_ID: 575397 =\> resolution: Won't Fix \[ 2 \] =\>

[firebird-automations]

Modified by: @pcisar

Attachment: FBTpb\.java\.diff \[ 10094 \]

[firebird-automations]

Commented by: @pcisar

Please, close the issue again.

[firebird-automations]

Modified by: @pcisar

status: Reopened \[ 4 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \] SF\_ID: 575397 =\>

[firebird-automations]

Modified by: Roman Rokytskyy (rrokytskyy)

status: Resolved \[ 5 \] =\> Closed \[ 6 \] SF\_ID: 575397 =\>