[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13551140#comment-13551140
]
Knut Anders Hatlen commented on DERBY-6011:
-------------------------------------------
I ran the repro with derby.language.logStatementText=true to see which
statements the transactions involved in the deadlock executed. The first
deadlock reported involved four transactions. Each transaction executed the
following three statements:
SELECT id,status,checktime FROM jobqueue WHERE dochash=? AND jobid=? FOR UPDATE
INSERT INTO jobqueue
(docpriority,id,priorityset,docid,status,dochash,checktime,checkaction,jobid)
VALUES (?,?,?,?,?,?,?,?,?)
SELECT id,status,checktime FROM jobqueue WHERE dochash=? AND jobid=? FOR UPDATE
The first two statements were executed with the same dochash (presumably the
application first checks whether the row exists, and inserts a new row if it
doesn't exist). The third statement used a different dochash and got stuck
waiting for a lock.
All four transactions used different dochash values, so one wouldn't normally
expect lock conflicts between them.
I'm wondering, though, if the lock conflicts are caused by the use of
serializable transactions. Derby supports the serializable isolation level by
taking previous key locks. That is, when it reads a row using via an index, it
locks not only that row, but also the row right before it in the index.
Combined with some logic in the insert code, which refuses to insert a new row
into an index if the position right before it is locked, the previous key lock
prevents the so-called phantom reads that are not allowed in serializable
transactions.
So even though the transactions access completely different sets of rows, they
may end up locking some of the same rows if the isolation level is serializable
and the accessed rows live near each other in the index.
At least that's a theory. I'll have to trace the locking too in order to find
out if that could explain the deadlocks, or if there's something else going on.
> Derby performs very badly (seems to deadlock and timeout) in very simple
> multi-threaded tests
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-6011
> URL: https://issues.apache.org/jira/browse/DERBY-6011
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.7.1.1, 10.8.2.2, 10.9.1.0
> Environment: Lenovo laptop with SSD's, Windows 7, 64-bit, Sun JDK
> 1.6.xx
> Reporter: Karl Wright
> Attachments: derby.log, manifoldcf.log
>
>
> The Apache ManifoldCF project supports Derby as one of its underlying
> databases. Simple tests, however, demonstrate that Derby is apparently
> deadlocking and timing out repeatedly under multi-thread conditions. This
> problem is long-standing, and is not exhibited by any other database
> ManifoldCF supports, and makes a simple test take between 6x and 12x as long.
> There is a trivial test with demonstrates the problem vs. other databases.
> Please do the following (once you have java 1.6+, svn 1.7+, and ant 1.7+
> available):
> (1) Check out https://svn.apache.org/repos/asf/manifoldcf/trunk
> (2) Run the following ant target to download the dependencies: "ant
> make-core-deps"
> (3) Run the Derby test: "ant run-rss-tests-derby" . Note the time required -
> at least 180 seconds, can be up to 360 seconds.
> (4) Run the equivalent HSQLDB test: "ant run-rss-tests-HSQLDB". This test
> takes about 31 seconds to run.
> The output of the Derby test can be found in the directory
> "tests/rss/test-derby-output". Have a look at manifoldcf.log, where all
> long-running queries are reported. Derby.log is also included, which shows
> only that during the test's cleanup phase the database is deleted before it
> is shutdown, which is not pertinent to the performance issue.
> I am available to assist with ManifoldCF, if that seems to be required.
--
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
