Derby
  1. Derby
  2. DERBY-1799

SUR: current row not locked when renavigating to it, in queries with indexes

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      This problem is detected in transactions with isolation level read-committed/read-uncommitted.

      We have a table (T) which has a primary key (a), and a query which does "select A from T" (an indexed select)

      If the result set is scrollable updatable, we expect the current row to be locked with an update lock. This does not seem to happen when repositioning to a row which has been already been fetched previously.

      The result is that either the wrong row is locked, or if the result set has been on after last position, no row is locked.

      Output from ij:
      ij> get scroll insensitive cursor c1 as 'select a from t for update';
      ij> next c1;
      A
      -----------
      1
      ij> select * from SYSCS_DIAG.LOCK_TABLE;
      XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      243 |ROW |U |T |(1,7) |GRANT|T |1 |NULL
      243 |ROW |S |T |(1,1) |GRANT|T |1 |SQL060901103455010
      243 |TABLE|IX |T |Tablelock |GRANT|T |4 |NULL

      3 rows selected

      ij> after last c1;
      No current row
      ij> previous c1;
      A
      -----------
      3
      ij> select * from SYSCS_DIAG.LOCK_TABLE;
      XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      243 |TABLE|IX |T |Tablelock |GRANT|T |4 |NULL

      1 row selected

      The last select shows that no row is locked at this point, however we expect one row to be locked.

      1. derby-1799.diff
        36 kB
        Fernanda Pizzorno
      2. derby-1799.stat
        1 kB
        Fernanda Pizzorno

        Issue Links

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Andreas Korneliussen
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development