Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6300

row locks incorrectly taken for rows that do not match SELECT predicate

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Abandoned
    • 10.8.3.0, 10.10.1.1
    • None
    • SQL, Store
    • None
    • Windows, Linux

    Description

      Derby seems to be taking S-locks on all the rows in a table after a SELECT query, even when none of the rows match the query predicate. For example, after running a query like
      SELECT col1, col2 FROM table1 WHERE col1 IN (?, ?, ?...) WITH RS

      and the query returns 0 rows, we still see S-locks being taken on all rows in the table.

      This issue seems to be dependent on which exact query plan gets chosen to be executed, as changing some combination of the following factors seems to avoid the issue:

      1) The number of total rows in the table is small. In the test case, we're using 10 rows.
      2) There is an explicitly created composite index on the table that covers all the rows.
      3) The number of values in the IN clause of the SELECT query is sufficiently large.

      What plan the optimizer chooses seems to be a factor. For example, in our actual database, we've found we need about 5 or 6 parameters in the IN clause to reproduce the issue. In the attached test case, it seems the issue can be seen with 3 or more parameters.

      The attached test results in a database deadlock if the row locking issue occurs. It basically does the following:
      a) Have a table with 10 rows. The values are basically A0, A1, ...
      b) Have a transaction selecting for values C0, C1, ...
      c) Have a 2nd transaction selecting for values D0, D1, ...
      d) Execute SQL deletes from both transactions

      The test fails in (d) with a deadlock because after (b) and (c), both transactions have S-locks on all the rows in the table.

      We've tested on 10.8.3 and 10.10.1.1, and both seem to exhibit the issue.

      Attachments

        1. derby.log
          43 kB
          Mike Matrigali
        2. RowLocksIssue.java
          10 kB
          H Zhang

        Issue Links

          Activity

            People

              Unassigned Unassigned
              zhangho H Zhang
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: