db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6300) row locks incorrectly taken for rows that do not match SELECT predicate
Date Thu, 25 Jul 2013 00:45:48 GMT

    [ 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

Mime
View raw message