[DERBY-4033] 50 transactions timing out with no CPU usage and no deadlocks

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.4.2.0, 10.5.1.1
• Environment: Mac OS X 10.5 with 32-bit JDK 1.5 and 32-bit Centos 4.4 sun JDK 1.6u11.
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Blair Zajac
• Created: 28/Jan/09 18:52
• Updated: 02/May/13 02:29
• Resolved: 07/May/09 07:34

Description

I have a test case for my JDBC DAO layer that runs 50 concurrent threads all inserting the same data to ensure that the DAO does not throw an error if the data is already in the table (more details on the app below). After working a while Derby 10.4.2.0 stops making progress, the java process shows 0% CPU utilization and Derby does not report a deadlock. Running kill -QUIT on java shows all threads waiting on something. After a while, one transaction will timeout.

Setting the lock timeout to -1 did not get the test to finish successfully. If I reduce the number of threads in the test to 10, then Derby successfully completes. The same exact code runs against PostgreSQL and Oracle and all 50 threads complete successfully.

Connecting to the Derby server with ij and SELECTing on SYSCS_DIAG.LOCK_TABLE shows that the transaction that has all the locks that other transactions are waiting on is not in a WAIT state for any other lock. So according to this, it should be making progress, but it's not.

When a timeout is set, the thread that times out has this stack trace using svn trunk r737572

org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286)
org.apache.derby.impl.services.locks.Timeout.createException(Timeout.java:150)
org.apache.derby.impl.services.locks.Timeout.buildException(Timeout.java:249)
org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(ConcurrentLockSet.java:597)
org.apache.derby.impl.services.locks.AbstractPool.lockObject(AbstractPool.java:119)
org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowLocking3.java:248)
org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:504)
org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:638)
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(B2IRowLocking3.java:335)
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockNonScanRowOnPage(B2IRowLocking3.java:1091)
org.apache.derby.impl.store.access.btree.BTreeController.doIns(BTreeController.java:707)
org.apache.derby.impl.store.access.btree.BTreeController.insert(BTreeController.java:1261)
org.apache.derby.impl.store.access.btree.index.B2IController.insert(B2IController.java:210)
org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(IndexChanger.java:439)
org.apache.derby.impl.sql.execute.IndexChanger.doInsert(IndexChanger.java:383)
org.apache.derby.impl.sql.execute.IndexChanger.insert(IndexChanger.java:589)
org.apache.derby.impl.sql.execute.IndexSetChanger.insert(IndexSetChanger.java:268)
org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:453)
org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1022)
org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:495)
org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
NoProgressTest.executeUpdate(NoProgressTest.java:38)
NoProgressTest.access$100(NoProgressTest.java:10)

This issue was raised on the derby-users mailing list.

http://www.nabble.com/50-transactions-timing-out-with-no-CPU-usage-and-no-deadlocks-to21659280.html

A suggestion to try the DERBY-2991 patch d2991-preview-1e.diff did work when applied to svn trunk and the test code successfully completed even using 500 threads.

As background, here is the schema showing what I'm doing. The schema has four tables, three of which represent a set of facilities and the fourth a location.

CREATE TABLE facility
(
facility_id int primary key,
code char(3)
);

CREATE TABLE facility_set
(
facility_set_id int primary key
)

CREATE TABLE facility_set_membership
(
facility_id int,
facility_set_id int
)

CREATE TABLE location
(
location_id int primary key,
facility_set_id int,
path varchar(256)
)

Issue Links

• depends upon: DERBY-2991 Index split deadlock