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] [Commented] (DERBY-6300) row locks incorrectly taken for rows that do not match SELECT predicate
Date Fri, 26 Jul 2013 15:39:50 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13720891#comment-13720891
] 

Mike Matrigali commented on DERBY-6300:
---------------------------------------

>In an earlier comment, you mentioned: "Since you are using a prepared statement, derby
chooses a query plan without knowing what the values for the ? operators are." Would that
mean that if we >didn't use a PreparedStatement, the optimizer might pick some other plan?
That wasn't clear from reading the tuning doc. I guess I should find some time and just try
seeing what Derby does in >practice...

In the case of the posted repro the "?" markers refer to columns which are the primary key.
 In this case Derby should "know" that at most one row will match a single value. I believe
the costing
should be the same in this case whether you use "?" or inlined values.  
 I need to look at
costing for IN-LISTS more closely.  In other costing the optimizer will just assume 1 matching
row for an equality constraint on a unique key value.  

In general when trying to figure out a bug optimization it is often a good practice to check
the query with both "?" markers against what the optimizer does with actual in-lined values.
 Because
Derby optimization is costly best practice is to use "?" parameters.  Then if your application
executes the query more than once the subsequent exections do not have to be optimized.  This
can happen either if the same connection uses a prepared statement more than once, or if any
thread executes the same query while the query is still in the statement cache.
                
> 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