[
https://issues.apache.org/jira/browse/DERBY-3185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12540813
]
Dyre Tjeldvoll commented on DERBY-3185:
---------------------------------------
Hi AB,
No you did not misunderstand. That page explains it very well, and there is
even a reference to it in the reference guide. Perhaps I should have checked
that reference, but the reference guide also contains the following paragraph:
"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."
> 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.
