[
https://issues.apache.org/jira/browse/DERBY-3185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12541013
]
Knut Anders Hatlen commented on DERBY-3185:
-------------------------------------------
Bernt wrote:
>the fact that "REPEATABLE READ" in SQL is NOT the same isolation level as
>Connection.TRANSACTION_REPEATABLE_READ
I know what you meant (s/SQL/Derby/), but just to make it perfectly clear:
"REPEATABLE READ" in the SQL standard is exactly the same as
Connection.TRANSACTION_REPEATABLE_READ in JDBC. Both are defined as isolation
levels that prevent dirty reads and non-repeatable reads, and that may allow
phantom reads.
> 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.
