You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mike Matrigali (JIRA)" <ji...@apache.org> on 2013/07/24 23:41:51 UTC
[jira] [Comment Edited] (DERBY-6300) row locks incorrectly taken
for rows that do not match SELECT predicate
[ https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13718633#comment-13718633 ]
Mike Matrigali edited comment on DERBY-6300 at 7/24/13 9:41 PM:
----------------------------------------------------------------
here is the first interesting query plan from the attached derby.log - note that for some reason the index scan is being done at repeatable read. For this test case
10 rows are inserted and there is an in-list with 10 ? parameters on col1. col1 is the primary key and there is a unique index on col2, col1. Because of the
small number of rows and at compile time it does not know what the values of the ? parameters are it has chosen to just do an full index scan on
IDX_COL2_COL1 which as both columns needed in the result set.
Does this match your application? ie. are there a relatively small number of rows when compared to the number of ? operators in the IN-LIST?
For test cases it is probably best to add an update statistics after loading the tables and before demo'ing the issue. In 10.8 and later the system will automatically
update the statistics, but it takes a bit to kick in. The first select that needs the stats will queue the work, but procede without getting the stats. Subsequent
selects will then benefit once the stat is calculated. This can add a confusing timing issue to test cases.
I would have expected it to be read committed which should be the default for derby unless another isolation level is chosen, I am focusing on that for now.:
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
was (Author: mikem):
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