Deadlock during insert with multiple RI

Database

Deadlock during insert with multiple RI

I have the following database design in Teradata.

MAIN_TABLE - has two hard RIs to SUB_TABLE_1 and SUB_TABLE_2.

create table DEADLOCK_TEST.MAIN_TABLE (

        MAIN_TABLE_ID INTEGER not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 CYCLE) ,

        MAIN_TABLE_VALUE_1 VARCHAR(10) Character Set UNICODE,

        SUB_TABLE_1 INTEGER,

        SUB_TABLE_2 INTEGER

    ) UNIQUE PRIMARY INDEX PI_MAIN (

        MAIN_TABLE_ID

    );

    create table DEADLOCK_TEST.SUB_TABLE_1 (

        SUB_TABLE_1_ID INTEGER not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 CYCLE) ,

        SUB_TABLE_1_NM VARCHAR(128) Character Set UNICODE

       ) UNIQUE PRIMARY INDEX PI_SUB_TABLE_1(

        SUB_TABLE_1_ID

    );

 create table DEADLOCK_TEST.SUB_TABLE_2 (

        SUB_TABLE_2_ID INTEGER not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 CYCLE) ,

        SUB_TABLE_2_NM VARCHAR(128) Character Set UNICODE

       ) UNIQUE PRIMARY INDEX PI_SUB_TABLE_2(

        SUB_TABLE_2_ID

    );

alter table DEADLOCK_TEST.MAIN_TABLE 

        add constraint FK1_SUB_TABLE_1

        foreign key (SUB_TABLE_1) 

        references DEADLOCK_TEST.SUB_TABLE_1(SUB_TABLE_1_ID);

        alter table DEADLOCK_TEST.MAIN_TABLE 

        add constraint FK1_SUB_TABLE_2

        foreign key (SUB_TABLE_2) 

        references DEADLOCK_TEST.SUB_TABLE_2(SUB_TABLE_2_ID);

CREATE VIEW DEADLOCK_TEST.V_MAIN_TABLE AS LOCKING ROW FOR ACCESS SELECT  * FROM DEADLOCK_TEST.MAIN_TABLE;

CREATE VIEW DEADLOCK_TEST.V_SUB_TABLE_1 AS LOCKING ROW FOR ACCESS SELECT  * FROM DEADLOCK_TEST.SUB_TABLE_1;

CREATE VIEW DEADLOCK_TEST.V_SUB_TABLE_2 AS LOCKING ROW FOR ACCESS SELECT  * FROM DEADLOCK_TEST.SUB_TABLE_2;

When I try to insert into the table (MAIN_TABLE) using 40 concurrent sessions using below insert startement,  after 20 to 30 inserts getting 2631 error - Transaction Aborted due to deadlock.

INSERT INTO DEADLOCK_TEST.V_MAIN_TABLE (MAIN_TABLE_VALUE_1,SUB_TABLE_1,SUB_TABLE_2) VALUES ('main',1,1);

But when I try to insert with just one reference value into the same table using below statement, not getting deadlock.

INSERT INTO DEADLOCK_TEST.V_MAIN_TABLE (MAIN_TABLE_VALUE_1,SUB_TABLE_1) VALUES ('main',1);

We have a situation to support multiple hard RIs, but with the above behavior unable to proceed.  Any help would be appreciated.

Thanks.

2 REPLIES

Re: Deadlock during insert with multiple RI

Hi Dieter,

Can you please help us on the above issue

Regards

Dinesh.S

Re: Deadlock during insert with multiple RI

We tested the same scenerio in DB2.It works fine.Any help would be appreciated.

Thanks

Dinesh.s