Insert causing deadlocks

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?



ian_rAsked:
Who is Participating?
 
PAQ_ManCommented:
Question Closed, 250 points refunded.
PAQ_Man
Community Support Moderator
0
 
patriktCommented:
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
 
ian_rAuthor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.