Post reply

Transaction rollback when multiple threads are inserting records into table because of trigger

  • June 18, 2014 at 1:12 am

    #287662

    Hi All ,

    I have two tables called ECASE and PROJECT

    In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .

    When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.

    When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.

    I need to prevent the deadlock . Can any one help me out

  • June 18, 2014 at 1:21 am

    #1722610

    Please post table definition, index definitions, the trigger's definition and the deadlock graph (xml from system health or output from traceflag 1222)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 18, 2014 at 3:44 am

    #1722658

    Hi GilaMonster ,

    Thank you for you reply

    I have added the table defination and index defination for the table ECASE and also i added the log file in the attachment section .

    Please find the trigger defination as below

    CREATE TRIGGER caseid_increment_trigger BEFORE INSERT ON ecase

    FOR EACH ROW SET NEW.case_id = (select ifnull(max(case_id),9999) + 1 from ecase where project_id = new.project_id);

  • June 18, 2014 at 3:52 am

    #1722661

    ravi.teja 62048 (6/18/2014)

    CREATE TRIGGER caseid_increment_trigger BEFORE INSERT ON ecase

    FOR EACH ROW SET NEW.case_id = (select ifnull(max(case_id),9999) + 1 from ecase where project_id = new.project_id);

    That's not a SQL Server trigger (SQL Server doesn't have FOR EACH ROW triggers, nor does it have BEFORE triggers). Maybe post your question on an MySQL site, rather than one dedicated to MS SQL Server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply