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] [Updated] (DERBY-6300) row locks incorrectly taken for rows that do not match SELECT predicate
Date Wed, 08 Jan 2014 22:32:51 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mike Matrigali updated DERBY-6300:
----------------------------------

    Component/s: Store
                 SQL

> 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
>          Components: SQL, Store
>    Affects Versions: 10.8.3.0, 10.10.1.1
>         Environment: Windows, Linux
>            Reporter: H Zhang
>         Attachments: RowLocksIssue.java, derby.log
>
>
> 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 was sent by Atlassian JIRA
(v6.1.5#6160)

Mime
View raw message