[jira] [Updated] (DERBY-5296) Deadlock running concurrent ALTER TABLE commands

["Mike Matrigali (Updated) (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-5296:
----------------------------------

    Component/s: SQL
    
> Deadlock running concurrent ALTER TABLE commands
> ------------------------------------------------
>
>                 Key: DERBY-5296
>                 URL: https://issues.apache.org/jira/browse/DERBY-5296
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira