[
https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13718633#comment-13718633
]
Mike Matrigali commented on DERBY-6300:
---------------------------------------
here is the interesting query plan from the attached derby.log - note that for
some reason the index scan is being done at repeatable read. I would have
expected it
to be read committed which should be the default for derby unless another
isolation level is chosen:
Wed Jul 24 10:21:31 PDT 2013 Thread[main,5,main] (XID = 360), (SESSIONID = 1),
SELECT col1, col2 FROM LocksIssue1 WHERE col1 in (?,?,?,?,?,?,?,?,?,?) WITH R
S ******* Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 10
Rows filtered = 10
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1.50
optimizer estimated cost: 21.13
Source result set:
Index Scan ResultSet for LOCKSISSUE1 using index IDX_COL2_COL1 at
repeatable read isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 10
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=10
Number of rows visited=10
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 1.50
optimizer estimated cost: 21.13^M
> 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
