Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Question Unanswered: Deadlock Problem in SQL Trigger Urgent

    Hello Db experts,

    Recently I am facing a problem as, I have a trigger on table1 in after insert
    event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
    hello

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    What does the trigger do?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd bet that we can. Check out the FAQ entry for How to get quick and correct answers for help.

    -PatP

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if you want it fixed you need to give us code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Thanks Coolberg

    Dear Coolberg


    Please know thrigger do some calculation for Table1's last inserted record and insert or Update in an another table like Control1 table
    hello

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Mahfuz
    Hello Db experts,

    Recently I am facing a problem as, I have a trigger on table1 in after insert
    event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
    Nomoskar Mahfuz,
    Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
    Bhalo Thakben!!
    Last edited by rudra; 08-22-06 at 18:18.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rudra
    Nomoskar Mahfuz,
    Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
    Bhalo Thakben!!
    Easy for you to say! Thanks for the Bengali assistance, I'm sure it will help.

    -PatP

  8. #8
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Question Deadlock Problem in SQL Trigger Urgent

    Hello Again,

    Sorry for not posting the code before. Please have a look at my code below. Now let me explain what problem I faced,there is a table named Table1 where data being inserted from 4 different sources in huge number (Almost 1000 records/sec), The After Insert trigger(Trigger1) written on Table1 inserts or updates the extracted information from Table1 to Table1_Backup.
    Most of the times during Insertion to Table1 I am getting a message as:

    "Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. "

    and I am loosing data in Table1 i.e not all records were inserted.
    But when I removed the Trigger , I didn't get this error message and there were no data loss. Looking Forward For any help to come out of this problem.

    Code:

    create table Table1
    (sId int,
    sName varchar(50),
    sPhone varchar(50),
    sBalance bigint
    )

    create table Table1_Backup
    (sId int,
    sName varchar(50),
    sPhone varchar(50),
    sBalance bigint

    )


    Create TRIGGER Trigger1
    ON Table1
    After INSERT
    AS
    Declare

    @counter int,
    @sId int,
    @sName varchar(50),
    @sPhone varchar(30),
    @sBalance bigint;

    Begin

    set @counter = 0;
    set @sId = 0;
    set @sName = null;
    set @sPhone = null;
    set @sBalance = 0;


    select @sId = sId,
    @sName = sName,
    @sPhone = sPhone,
    @sBalance = sBalance
    from INSERTED Table1;


    Select @counter = @sId From Table1_Backup WHERE sId = @sId;

    IF (@counter < 1 )
    insert into Table1_Backup
    values(@sId,@sName,@sPhone,@sBalance);
    ELSE
    Update Table1_Backup
    set sBalance = sBalance + @sBalance
    Where sId = @sId;
    End
    hello

  9. #9
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Unhappy Valo nei bangali dada, shomadha chai

    Hi Dada,
    Nin bangaly bolchi, kintu ami to problem ta post korechi shudhu bangali der jonno noy, ekhane to non bangali o ache. Kintu apnar shahajjo korar icche dhekhe khub valo laglo. Tai apnake bangaly likchi. Amra bangalay kotha (chat) bolte pari jodi apni chan, amar yahoo id hocche "mahfuz_onsky@yahoo.com".
    Asha kori shomadhan ta apnar kachei pabo ebong ekjon bangali dada er kache pele valoi lagbe.

    Kotha hobe.

    Mahfuz.



    Quote Originally Posted by rudra
    Nomoskar Mahfuz,
    Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
    Bhalo Thakben!!
    hello

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Can you show me the Insert statement that you are using to insert data? By the way which version of Sql server are you running?
    It may be a problem in your application code,plz provide the code where you are inserting the data.
    Last edited by rudra; 08-23-06 at 03:59.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL Server, a trigger fires once for each SQL Statement that launches the trigger, no matter how many rows the original statement might have affected. There could be 1000 or more rows in the INSERTED table for any given execution of your trigger.

    There appears to be a logic error inside your trigger, but without knowing exactly what you are trying to do, I can't fix that. The code translated to use sets instead of working one row at a time would be:
    Code:
    CREATE TRIGGER Trigger1
    ON Table1
    After INSERT
    AS
    
    INSERT INTO Table1_Backup (
       sId, sName, sPhone, sBalance)
       SELECT sId, sName, sPhone, sBalance
          FROM INSERTED
          WHERE sID < 1
    
    UPDATE Table1_Backup
       SET sBalance = sBalance + (SELECT Sum(INSERTED.sBalance)
          FROM INSERTED
          WHERE  INSERTED.sId = Table1_Backup.sId)
    
    END
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •