bother, multi-threaded alter table is buggy in derby

[Adam Heath-2]

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

[Adam Heath-2]

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.

[Jacques Le Roux]

I think it would be better to report in Derby dev ML, if the problem is only with Derby.

Jacques

From: "Adam Heath" <[hidden email]>

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

[Adam Heath-2]

On 06/26/2011 05:42 AM, Jacques Le Roux wrote:
> I think it would be better to report in Derby dev ML, if the problem is
> only with Derby.

DERBY-5296.  I see lots of other deadlock issues filed, none seemed to
be about ALTER TABLE tho.

>
> Jacques
>
> From: "Adam Heath" <[hidden email]>
>> 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.
>