db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3185) SET (TRANSACTION) ISOLATION (LEVEL) is not SQL compliant
Date Thu, 08 Nov 2007 09:29:50 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12541008
] 

Bernt M. Johnsen commented on DERBY-3185:
-----------------------------------------

The following statement in http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html is also
misleading: "The JDBC java.sql.Connection.setTransactionIsolation method behaves almost identically
to this command, with one exception: if you are using the embedded driver, and if the call
to java.sql.Connection.setTransactionIsolation does not actually change the isolation level
(that is, if it sets the isolation level to its current value), the current transaction is
not committed." 

There is *TWO* exceptions: The one mentioned, AND the fact that "REPEATABLE READ" in SQL is
NOT the same isolation level as Connection.TRANSACTION_REPEATABLE_READ.

Most users will assume they are the same and never bother to read the docs. When they encounter
problems the user will still not read the docs because it's so obvious that the two should
be interpreted as the same isolation level. When posting on derby-user/derby-dev about this
"bug", the response would be a (presumably) polite pointer to the docs and the user (which
doesn't know the history and most likely does't care about DB2 compatability) will wonder
what the developers where thinking when they implemented it like this.

I think we should 1) interpret SQL REPEATABLE READ according to the SQL standard, and 2) implement
a SQL compatible SET TRANSACTION ISOLATION statement.

> 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