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

        Dyre Tjeldvoll created issue -
        Rick Hillegas made changes -
        Field Original Value New Value
        Labels derby_triage10_8
        Rick Hillegas made changes -
        Urgency Low
        Gavin made changes -
        Workflow jira [ 12416641 ] Default workflow, editable Closed status [ 12799488 ]

          People

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

            Dates

            • Created:
              Updated:

              Development