db-derby-dev mailing list archives

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

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

Knut Anders Hatlen commented on DERBY-3185:
-------------------------------------------

The first paragraph about "SET ISOLATION statement" in the reference
manual (http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html)
also says:

> The SET ISOLATION statement allows a user to change the isolation
> level for the user's connection. Valid levels are SERIALIZABLE,
> REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

Whereas the above statement is true (those four levels are in fact
valid), it is confusing since it suppresses the fact that SERIALIZABLE
and REPEATABLE READ both map to
java.sql.Connection.TRANSACTION_SERIALIZABLE, and the list of valid
levels is incomplete, since none of the levels mentioned map to
java.sql.Connection.TRANSACTION_REPEATABLE_READ.

Perhaps the paragraph would have been clearer if it had used the DB2
names and referred to the mapping table:

  Valid levels are RR, RS, CS and UR (or some of their aliases, see
  the syntax description below). The mapping of JDBC isolation levels
  to Derby isolation levels can be found in Derby's Developer Guide
  under the section named "Locking, concurrency, and isolation".

> 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