SQL Transaction was deadlocked

sql-server exception stored-procedures triggers
Piotr Salaciak picture Piotr Salaciak · Mar 22, 2011 · Viewed 83.6k times · Source

Sometimes I get this kind of exception on not very busy SQL server:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Line number: 1
Error Number: 1205
Procedure: 
Server name: P01
Error Source: .Net SqlClient Data Provider
Error State: 47

I'm not able to reproduce it. I tried to run several queries from different clients at the same time, but it didn't show up. What is the best way to handle this kind of issue when it happens inside procedure or inside trigger? I mean, how to rerun the transaction?

How to do it when exception occurs inside procedure called from the trigger, which was called by the insert made by some procedure (that is: procedure01 -> insert -> trigger -> procedure02 !)

Answer

John Sansom picture John Sansom · Mar 22, 2011

I would suggest that you come at the problem from two perspectives.

  1. Trap or Catch Deadlock Errors so that you can re-run the transaction that is chosen as the Deadlock Victim by the SQL Server database engine.

  2. Find out what is causing your Deadlock Events. You can do this in one of two ways, either run a SQL Server Profiler Trace to catch and record the Deadlock Event or you can enable some SQL Server Trace Flags that will record the details of the Deadlock Event to the SQL Server Error Log.

In the vast majority of cases, you can identify the cause of your Deadlock Events and remedy the situation through either a structural change in the database schema or a logical change to the code involved/responsible for the Deadlock Event.

For further reading take a look at:

I hope I've answered your question but do let me know if I can help you further in any way.

Related questions

SQL error: String or binary data would be truncated

I'm doing an integration on a community platform called Telligent. I'm using a 3rd-party add-on called BlogML to import blog posts from an XML file (in BlogML format) into my local Telligent site. The Telligent platform comes with many classes …

Read More
sql try/catch rollback/commit - preventing erroneous commit after rollback

I am trying to write an MS sql script that has a transaction and a try/catch block. If it catches an exception, the transaction is rolled back. If not, the transaction is committed. I have seen a few different …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More