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

          Andreas Korneliussen created issue -
          Fernanda Pizzorno made changes -
          Field Original Value New Value
          Assignee Fernanda Pizzorno [ fernanda ]
          Fernanda Pizzorno made changes -
          Component/s Store [ 11412 ]
          Andreas Korneliussen made changes -
          Link This issue relates to DERBY-1696 [ DERBY-1696 ]
          Fernanda Pizzorno made changes -
          Attachment derby-1799.diff [ 12340928 ]
          Attachment derby-1799.stat [ 12340929 ]
          Kathey Marsden made changes -
          Assignee Fernanda Pizzorno [ fernanda ]
          Knut Anders Hatlen made changes -
          Urgency Normal
          Issue & fix info [Repro attached]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Mike Matrigali made changes -
          Labels derby_triage10_5_2 derby_triage10_5_2 derby_triage10_9
          Gavin made changes -
          Workflow jira [ 12382841 ] Default workflow, editable Closed status [ 12798764 ]

            People

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

              Dates

              • Created:
                Updated:

                Development