On 06/25/2011 08:26 PM, Adam Heath wrote:
==
[java] 2011-06-25 19:48:46,529 (entity-datasource(localderby)-1) [
DatabaseUtil.java:2338:ERROR] SQL Exception while executing the
following:
[java] ALTER TABLE OFBIZ.ACCTG_TRANS_ENTRY ADD CONSTRAINT
ACCTTXENT_INVITEM FOREIGN KEY (INVENTORY_ITEM_ID) REFERENCES
OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_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, SYSCONGLOMERATES, (284,16)
[java] Waiting XID : {12055, S} , OFBIZ, ALTER TABLE
OFBIZ.ACCTG_TRANS_ENTRY ADD CONSTRAINT ACCTTXENT_INVITEM FOREIGN KEY
(INVENTORY_ITEM_ID) REFERENCES OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID)
[java] Granted XID : {12053, X}
[java] Lock : ROW, SYSCONSTRAINTS, (12,21)
[java] Waiting XID : {12053, X} , OFBIZ, ALTER TABLE OFBIZ.ACCTG_TRANS
ADD CONSTRAINT ACCTTX_INVITEM FOREIGN KEY (INVENTORY_ITEM_ID) REFERENCES
OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID)
[java] Granted XID : {12055, S}
[java] . The selected victim is XID : 12055.
==
Near as I can tell, derby is broken. The parent tables in both these
cases are different. And the child table is the same. So there really
shouldn't be a deadlock, it should just pause the second one briefly.
Ideally, the locking code in derby should find out the tables being
manipulated(in the alter table call), do an alpha-sort(or any kind of
stable sore), then lock in that order.
But I suppose that other databases may have similiar isssues with
multi-threaded schema changes. I guess I'll have to come up with a
solution.
It's even worse than I thought.
==
[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.
==
This shows that two completely separate ALTER TABLE commands(none of
the tables are overlapping) deadlock with derby.
