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.
