[
https://issues.apache.org/jira/browse/DERBY-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13718589#comment-13718589
]
Mike Matrigali commented on DERBY-6300:
---------------------------------------
Here is the output from the attached repro run on my machine:
Connected to jdbc:derby:C:/temp/RowLocksIssue;create=true
DDL : DROP TABLE LocksIssue1 : 0
DDL : CREATE TABLE LocksIssue1 ( col1 VARCHAR(64) NOT NULL, col2 VARCHAR(64)
NOT NULL, PRIMARY KEY (col1) ) : 0
DDL : CREATE UNIQUE INDEX IDX_COL2_COL1 ON LocksIssue1 (col2, col1) : 0
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA0',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB0')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA0',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB0') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB1')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB1') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB2')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB2') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB3')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB3') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB4')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB4') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB5')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB5') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB6')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB6') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA7',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB7')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA7',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB7') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA8',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB8')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA8',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB8') : 1
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA9',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB9')
SQL : INSERT INTO LocksIssue1(col1, col2) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA9',
'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBB9') : 1
SQL : SELECT col1, col2 FROM LocksIssue1 WHERE col1 in (?,?,?,?,?,?,?,?,?,?)
WITH RS : selected 0
SQL : SELECT col1, col2 FROM LocksIssue1 WHERE col1 in (?,?,?,?,?,?,?,?,?,?)
WITH RS : selected 0
Derby Locks Table (Thread[main,5,main]):
XID, TYPE, MODE, TABLENAME, LOCKNAME, STATE, TABLETYPE, LOCKCOUNT, INDEXNAME
255, ROW, S, LOCKSISSUE1, (1,9), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,9), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,10), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,10), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,11), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,11), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,12), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,12), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,13), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,13), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,14), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,14), GRANT, T, 1, null
255, TABLE, IS, LOCKSISSUE1, Tablelock, GRANT, T, 1, null
256, TABLE, IS, LOCKSISSUE1, Tablelock, GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,15), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,15), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,16), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,16), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,7), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,7), GRANT, T, 1, null
255, ROW, S, LOCKSISSUE1, (1,8), GRANT, T, 1, null
256, ROW, S, LOCKSISSUE1, (1,8), GRANT, T, 1, null
Running (Thread[Thread-10,5,main]): DELETE FROM LocksIssue1 WHERE col1 in
(?,?,?,?,?,?,?,?,?,?)
Running (Thread[Thread-11,5,main]): DELETE FROM LocksIssue1 WHERE col1 in
(?,?,?,?,?,?,?,?,?,?)
Thread 2 failed
SQL : DELETE FROM LocksIssue1 WHERE col1 in (?,?,?,?,?,?,?,?,?,?) : updated 0
Thread 1 committing delete...
Thread 1 done - 2
Done
> 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: 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
