?
Solved

Insert causing deadlocks

Posted on 2005-05-12
4
Medium Priority
?
2,181 Views
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...

  cmd.Execute

------------------------------------

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
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
INSERT INTO tblLog WITH (ROWLOCK)
(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?



0
Comment
Question by:ian_r
3 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 13984796
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.

Patrik
0
 

Author Comment

by:ian_r
ID: 13984995

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

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 14016032
Question Closed, 250 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

862 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