==
[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.
