Derby
  1. Derby
  2. DERBY-3185

SET (TRANSACTION) ISOLATION (LEVEL) is not SQL compliant

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: None
    • Component/s: JDBC, SQL
    • Urgency:
      Low

      Description

      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.

        Activity

        Hide
        Kathey Marsden added a comment -

        If there are no objections, I think we should follow Dan's suggestion and file a new issue for implementing SET TRANSACTÏON ISOLATION LEVEL according to the SQL standard and then leave SET ISOLATION alone, and close this issue as Invalid.

        Show
        Kathey Marsden added a comment - If there are no objections, I think we should follow Dan's suggestion and file a new issue for implementing SET TRANSACTÏON ISOLATION LEVEL according to the SQL standard and then leave SET ISOLATION alone, and close this issue as Invalid.
        Hide
        Daniel John Debrunner added a comment -

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

        Show
        Daniel John Debrunner added a comment - 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).
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Hide
        Bernt M. Johnsen added a comment -

        The following statement in http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html is also misleading: "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."

        There is TWO exceptions: The one mentioned, AND the fact that "REPEATABLE READ" in SQL is NOT the same isolation level as Connection.TRANSACTION_REPEATABLE_READ.

        Most users will assume they are the same and never bother to read the docs. When they encounter problems the user will still not read the docs because it's so obvious that the two should be interpreted as the same isolation level. When posting on derby-user/derby-dev about this "bug", the response would be a (presumably) polite pointer to the docs and the user (which doesn't know the history and most likely does't care about DB2 compatability) will wonder what the developers where thinking when they implemented it like this.

        I think we should 1) interpret SQL REPEATABLE READ according to the SQL standard, and 2) implement a SQL compatible SET TRANSACTION ISOLATION statement.

        Show
        Bernt M. Johnsen added a comment - The following statement in http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html is also misleading: "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." There is TWO exceptions: The one mentioned, AND the fact that "REPEATABLE READ" in SQL is NOT the same isolation level as Connection.TRANSACTION_REPEATABLE_READ. Most users will assume they are the same and never bother to read the docs. When they encounter problems the user will still not read the docs because it's so obvious that the two should be interpreted as the same isolation level. When posting on derby-user/derby-dev about this "bug", the response would be a (presumably) polite pointer to the docs and the user (which doesn't know the history and most likely does't care about DB2 compatability) will wonder what the developers where thinking when they implemented it like this. I think we should 1) interpret SQL REPEATABLE READ according to the SQL standard, and 2) implement a SQL compatible SET TRANSACTION ISOLATION statement.
        Hide
        Knut Anders Hatlen added a comment -

        The first paragraph about "SET ISOLATION statement" in the reference
        manual (http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html)
        also says:

        > The SET ISOLATION statement allows a user to change the isolation
        > level for the user's connection. Valid levels are SERIALIZABLE,
        > REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

        Whereas the above statement is true (those four levels are in fact
        valid), it is confusing since it suppresses the fact that SERIALIZABLE
        and REPEATABLE READ both map to
        java.sql.Connection.TRANSACTION_SERIALIZABLE, and the list of valid
        levels is incomplete, since none of the levels mentioned map to
        java.sql.Connection.TRANSACTION_REPEATABLE_READ.

        Perhaps the paragraph would have been clearer if it had used the DB2
        names and referred to the mapping table:

        Valid levels are RR, RS, CS and UR (or some of their aliases, see
        the syntax description below). The mapping of JDBC isolation levels
        to Derby isolation levels can be found in Derby's Developer Guide
        under the section named "Locking, concurrency, and isolation".

        Show
        Knut Anders Hatlen added a comment - The first paragraph about "SET ISOLATION statement" in the reference manual ( http://db.apache.org/derby/docs/dev/ref/rrefsqlj41180.html ) also says: > The SET ISOLATION statement allows a user to change the isolation > level for the user's connection. Valid levels are SERIALIZABLE, > REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED. Whereas the above statement is true (those four levels are in fact valid), it is confusing since it suppresses the fact that SERIALIZABLE and REPEATABLE READ both map to java.sql.Connection.TRANSACTION_SERIALIZABLE, and the list of valid levels is incomplete, since none of the levels mentioned map to java.sql.Connection.TRANSACTION_REPEATABLE_READ. Perhaps the paragraph would have been clearer if it had used the DB2 names and referred to the mapping table: Valid levels are RR, RS, CS and UR (or some of their aliases, see the syntax description below). The mapping of JDBC isolation levels to Derby isolation levels can be found in Derby's Developer Guide under the section named "Locking, concurrency, and isolation".
        Hide
        Dyre Tjeldvoll added a comment -

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

        Show
        Dyre Tjeldvoll added a comment - 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."
        Hide
        A B added a comment -

        > There is also an inconsistency between JDBC and SQL when specifying 'repeatable read'

        <snip>

        > the current behavior is really confusing and should at least be better documented.

        Is this covered in Table 1 here?

        http://db.apache.org/derby/docs/dev/devguide/cdevconcepts15366.html

        I think this describes what you are seeing when you say "FAILS, RR is translated into Connection.TRANSACTION_SERIALIZABLE ". Or did I misunderstand?

        Show
        A B added a comment - > There is also an inconsistency between JDBC and SQL when specifying 'repeatable read' <snip> > the current behavior is really confusing and should at least be better documented. Is this covered in Table 1 here? http://db.apache.org/derby/docs/dev/devguide/cdevconcepts15366.html I think this describes what you are seeing when you say "FAILS, RR is translated into Connection.TRANSACTION_SERIALIZABLE ". Or did I misunderstand?
        Hide
        Bernt M. Johnsen added a comment -

        According to SQL 2003, the keyword LEVEL is required too.

        Show
        Bernt M. Johnsen added a comment - According to SQL 2003, the keyword LEVEL is required too.

          People

          • Assignee:
            Unassigned
            Reporter:
            Dyre Tjeldvoll
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development