Insert causing deadlocks

Posted on 2005-05-12
Last Modified: 2010-08-05
I'm getting errors back from SQL Server when trying to insert new rows into a database table called tblLog.

There are 5 VB6 applications that write some of their operations to this table, but they are quite frequently causing a deadlock.

I was previously sending the SQL statement to SQL Server using the ADO Command object, via the Command.Execute method.  In an effort to fix the problem, I've now moved the SQL statement into a stored procedure and I'm using the Command.Execute method to run the stored procedure instead but it's made little difference:


  ' Set up a command object for the stored procedure.
  Set cmd.ActiveConnection = m_cn
  cmd.CommandText = "spInsertLogMsg"
  cmd.CommandType = adCmdStoredProc

  ' create parameter objects and pass in the input parameters
  ' NOTE - I've not included this code here...



The stored procedure is simply:


CREATE PROC spInsertLogMsg @Adap_ID int, @Msg_Type char(1), @Msg text, @Module_Com varchar(50) = NULL,
                                            @Source_Function varchar(50) = NULL, @Err_Num int = NULL, @HL7 text = NULL

(Adap_ID, Msg_Type, Occurred_At, Module_Com, Source_Function, Msg, Err_Num, HL7)
VALUES (@Adap_ID, @Msg_Type, GetDate(), @Module_Com, @Source_Function, @Msg, @Err_Num, @HL7)


I've tried setting different Isolation levels but it doesn't seem to have had any effect.  The insert for one of the applications succeeds while the other four fail and write an error to a text file:

"12/05/2005 11:16:57",-2147467259,"[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #18) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.","Microsoft OLE DB Provider for ODBC Drivers"
"12/05/2005 11:16:57",-2147467259,"Desc: [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #18) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.SQL State: 40001","Microsoft OLE DB Provider for ODBC Drivers"

Could I trap the error in the stored procedure and re-try the insert statement?  Shouldn't SQL Server handle this type of issue for me, is there another setting I need to change?

Question by:ian_r
    LVL 12

    Expert Comment

    From first look on your situation I think that you go wrong way. The procedure doesont seem to be able to couse deadlock.

    Try to run Profiler and watch what is going there. Look for other procedure called in same time or other clanch.
    If you paste report from Profiler we can alayze it.


    Author Comment


    I didn't realise, but someone had set a trigger on the same table to fire every time an insert occurred.  It's meant to keep the records down to only 3 months worth, but it was locking the table and causing the deadlock.

    Thanks for responding anyway Patrik.

    Accepted Solution

    Question Closed, 250 points refunded.
    Community Support Moderator

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Cluster index creation - SQL Server 4 32
    passing parameter in sql procedure 9 27
    SQL Agent Timeout 5 28
    Software suggestion 12 20
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now