[
https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13719104#comment-13719104
]
Mike Matrigali edited comment on DERBY-6300 at 7/25/13 12:43 AM:
-----------------------------------------------------------------
H Zhang, would like to understand if there is any workaround available for your
application problem (hoping your test
case is not exactly your issue). For instance:
1) do you need repeatable read isolation level?
2) Is it likely in your real application that the number of terms in your
IN-LIST is large relative to the number of rows in
your table?
was (Author: mikem):
H Zhang, would like to understand if there is any workaround available for
your application problem (hoping your test
case is not exactly your issue). For instance:
1) do you need repeatable read isolation level?
2) Is it likely in your real application that the nuber of terms in your
IN-LIST is large relative to the number of rows in
your table?
> row locks incorrectly taken for rows that do not match SELECT predicate
> -----------------------------------------------------------------------
>
> Key: DERBY-6300
> URL: https://issues.apache.org/jira/browse/DERBY-6300
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.8.3.0, 10.10.1.1
> Environment: Windows, Linux
> Reporter: H Zhang
> Attachments: derby.log, RowLocksIssue.java
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
