[jira] Commented: (DERBY-3185) SET (TRANSACTION) ISOLATION (LEVEL) is not SQL compliant

["Daniel John Debrunner (JIRA)" <ji...@apache.org>]

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

Daniel John Debrunner commented on DERBY-3185:
----------------------------------------------

Maybe a different new feature issue could be entered to support the SQL standard SET TRANSACTION ISOLATION statement if that's what someone wants.

Then this issue could either be closed and one opened with a better summary, or the discussion in this one focussed on better documentation or changing the behaviour (though that will impact existing applications).

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