Deadlock running concurrent ALTER TABLE commands
------------------------------------------------
Key: DERBY-5296
URL: https://issues.apache.org/jira/browse/DERBY-5296
Project: Derby
Issue Type: Bug
Affects Versions: 10.8.1.2
Reporter: Adam Heath
==
[java] 2011-06-25 20:57:59,232 (entity-datasource(localderby)-1) [
DatabaseUtil.java:2383:ERROR] SQL Exception while executing the following:
[java] ALTER TABLE OFBIZ.COMMUNICATION_EVENT_TYPE ADD CONSTRAINT
COM_EVNT_TYPE_PAR FOREIGN KEY (PARENT_TYPE_ID) REFERENCES
OFBIZ.COMMUNICATION_EVENT_TYPE (COMMUNICATION_EVENT_TYPE_ID)
[java] Error was: java.sql.SQLTransactionRollbackException: A lock could
not be obtained due to a deadlock, cycle of locks and waiters is:
[java] Lock : ROW, SYSCONSTRAINTS, (3,27)
[java] Waiting XID : {12420, X} , OFBIZ, ALTER TABLE
OFBIZ.COMMUNICATION_EVENT_TYPE ADD CONSTRAINT COM_EVNT_TYPE_PAR FOREIGN KEY
(PARENT_TYPE_ID) REFERENCES OFBIZ.COMMUNICATION_EVENT_TYPE
(COMMUNICATION_EVENT_TYPE_ID)
[java] Granted XID : {12418, S}
[java] Lock : ROW, SYSCONGLOMERATES, (297,11)
[java] Waiting XID : {12418, S} , OFBIZ, ALTER TABLE
OFBIZ.COMMUNICATION_EVENT ADD CONSTRAINT COM_EVNT_RESENUM FOREIGN KEY
(REASON_ENUM_ID) REFERENCES OFBIZ.ENUMERATION (ENUM_ID)
[java] Granted XID : {12420, X}
[java] . The selected victim is XID : 12420.
==
I was adding thread-pool based start-of-day support to ofbiz(ofbiz.apache.org),
so that CREATE TABLE, CREATE INDEX, kinda stuff could run in parallel.
However, foreign key stuff in derby deadlocks.
The above paste shows two alter table commands. Both the primary parent table
and the second child table do not overlap. There should be no reason for these
to deadlock at all.
Shouldn't derby be finding out all the locks it needs to take, doing an alpha
sort(or any kind of stable sort), then locking in that order? This is a
classic deadlock issue(based on the book Java Concurrency in Practice).
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
