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 10:13:50 GMT

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


Mime
View raw message