db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dyre Tjeldvoll (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3185) SET (TRANSACTION) ISOLATION (LEVEL) is not SQL compliant
Date Wed, 07 Nov 2007 14:12:51 GMT
SET (TRANSACTION) ISOLATION (LEVEL) is not SQL compliant
--------------------------------------------------------

                 Key: DERBY-3185
                 URL: https://issues.apache.org/jira/browse/DERBY-3185
             Project: Derby
          Issue Type: Bug
          Components: JDBC, SQL
    Affects Versions: 10.3.1.4
            Reporter: Dyre Tjeldvoll


The SQL standard (2003) requires the keyword 'TRANSACTION' and allows the keyword 'LEVEL'
in 
SET TRANSACTION ISOLATION LEVEL <level> Derby permits neither and issues a syntax error
(but permits the optional word 'CURRENT').

There is also an inconsistency between JDBC and SQL when specifying 'repeatable read' isolation
level. 

Specifying repeatable read from JDBC works as expected:
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); 
assert(conn.getTransactionIsolation() == Connection.TRANSACTION_REPEATABLE_READ) // OK

Doing it in SQL yields RR or 
SET ISOLATION REPEATABLE READ;
VALUES CURRENT ISOLATION; -> RR
assert(conn.getTransactionIsolation() == Connection.TRANSACTION_REPEATABLE_READ) // FAILS,
RR is translated into Connection.TRANSACTION_SERIALIZABLE

Using RS in SQL works as expected
SET ISOLATION RS;
VALUES CURRENT ISOLATION; -> RS
assert(conn.getTransactionIsolation() == Connection.TRANSACTION_REPEATABLE_READ) // OK

I guess there could be backward compatibility issues that makes it difficult to change this,

but the current behavior is really confusing and should at least be better documented. An
alternative is to add a new 
SQL compliant SET TRANSACTION which uses the standard isolation level specifiers, and keep
SET (CURRENT) ISOLATION as it is today for backward
compatibility. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message