Deadlocks

Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).

We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.

One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.

What do you guys think?SQL-Server-Performance.com is a site that I use often to help me with situations such as this.

They have an article calledTips for Reducing SQL Server Deadlocks which I highly recommend. Part of that article states "Most well-designed applications, after receiving a deadlock message, will resubmit the aborted transaction, which most likely can now run successfully.", which is exactly what you are proposing to do so it is sounding like a good idea.

These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint

Terri|||Thanks, that's what I wanted to see.

Our solution then:

- All stored procedures with no updates / deletes will use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

- I'll overload the SqlCommand.ExecuteNonQuery() method to re-run deadlocked transactions

- In transactions doing updates / deletes we'll use the NOLOCK hint where serializability is not required

in addition to our current practices:

- Only use transactions where serializability is required

- Keep the transaction as short as possible

- Optimise select statements in transactions with indexes on non-trivial tables

Cheers for that link Terri|||I would definitely use NOLOCKs on any queries that "dirty reads" are ok. Also, you can use WITH (ROLOCK) on update and delete statements where you are deleting one row. Such as deleting based on a Primary key.|||Do you know what type of Deadlocks you're are getting, lock promotion deadlocks or the more traditional resource contention type?|||Just regular resource contention ones.|||Pierre, Can you give an example of how you ended up re-running the transaction? I am having the same problems, I get about 5-10 per day also. Any help would be appriciated!
Thanks,
Jason|||

Try the code below it is what is recommended by my book SQL Server 2000 A beginner's guide by Dusan Petkovic. But this code is for SQL Server 2005 so test it. The code is from the link below. The key is to write a conditional statement that will return SQL Server @.@. ERROR 1205 which is Deadlock. Run a search for SET DEADLOCK_PRIORITY in the BOL (books online). Hope this helps.
CREATE PROCEDURE DeadLock_Test AS

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY LOW

DECLARE @.Err INTEGER
DECLARE @.ErrMsg VARCHAR(200)

RETRY:
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblContact SET LastName = 'SP_LastName_1' WHERE ContactID = 1
UPDATE tblContact SET LastName = 'SP_LastName_2' WHERE ContactID = 2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @.Err = @.@.ERROR
IF @.Err = 1205
ROLLBACK TRANSACTION
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@.Err, 'Deadlock recovery attempt.')
WAITFOR DELAY '00:00:10'
GOTO RETRY
IF @.Err = 2627
SET @.ErrMsg = 'PK Violation.'
IF @.ErrMsg IS NULL
SET @.ErrMsg = 'Other Error.'
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@.Err, @.ErrMsg)
END CATCH
http://www.campbellassociates.ca/blog/CategoryView.aspx?category=SQL%20Server

|||

Pierre,

I finally found my error now after a year, when they say use Query Analyizer They mean it. I had a stupid trigger that I had written way before I knew what I was doing (I still don't) but anyway the trigger was poorly written and actuall not needed. No More Deadlocks!!! Weheww!!! So I think the moral to the story is don't use Triggers Unless you absolutely have to.

sql