[Firebird-devel] [ firebird-Bugs-575397 ] SERIALIZABLE is not isc_tpb_consistency

[<no...@so...>]

Bugs item #575397, was opened at 2002-06-29 20:03
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=575397&group_id=9028

Category: Java/JDBC Client
Group: None
Status: Open
Resolution: None
Priority: 7
Submitted By: Nickolay Samofatov (skidder)
Assigned to: David Jencks (d_jencks)
Summary: SERIALIZABLE is not isc_tpb_consistency

Initial Comment:
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.

----------------------------------------------------------------------

>Comment By: Nickolay Samofatov (skidder)
Date: 2002-07-30 12:54

Message:
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.

----------------------------------------------------------------------

Comment By: David Jencks (d_jencks)
Date: 2002-07-29 23:22

Message:
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

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=575397&group_id=9028