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 Wed, 24 Jul 2013 22:29:49 GMT

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

Mike Matrigali commented on DERBY-6300:

I didn't notice before.  The select statement that the test case is using is:
SELECT col1, col2 FROM LocksIssue1 WHERE col1 in (?,?,?,?,?,?,?,?,?,?) WITH RS

I believe at least with the provided test case that you are seeing the expected behavior of
Derby in this case.  It will
choose not to do multiple index probes and will lock rows both qualifying and non-qualifying
rows while processing
the query.  Because you specified RS these locks remain until end of transaction.  This behavior
is unlikely to change
as part of bug fix.  

"WITH RS" sets the isolation level of the select to Connection.TRANSACTION_REPEATABLE_READ,
for Derby
this means that it will retain locks on all rows read until end of transaction.  For a full
index scan this will mean
it will retain locks on all rows until end of transaction.  So retaining the locks is expected
behavior for Derby.  If
you had either not specified a WITH clause or specified CS then the locks would have been
released at the latest
when you closed the statement.  See following for details:

Since you are using a prepared statement, derby chooses a query plan without knowing what
the values for the
? operators are.   It compares the cost of doing 10 probes (1 each for each parameter) vs
 a scan of all 10
rows in the table and chooses to do the scan which is more efficient.  See page 53 of this
document for more
detail on when Derby will choose a multi-probe access path vs a scan:

> 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:,
>         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, 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

View raw message