db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6011) Derby performs very badly (seems to deadlock and timeout) in very simple multi-threaded tests
Date Fri, 11 Jan 2013 14:46:12 GMT

    [ 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
> ---------------------------------------------------------------------------------------------
>                 Key: DERBY-6011
>                 URL: https://issues.apache.org/jira/browse/DERBY-6011
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions:,,
>         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

View raw message