Friday, April 12, 2013

MSSQL: Transaction was deadlocked

For the past few weeks i’ve encountered mssql deadlock error in my application. The exception was: “Transaction (Process ID ...) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” So what is this deadlock and how to find more information about it?

Basically, scheme of the problem looks like this:

We have two processes (for example log writers) and we have two resources (for example tables). Process 1 locks Resource 1 and works with it, Process 2 doing the same thing with resource 2. Process 1 now need to lock Resource 2, so it will remove lock from Resource 1 as soon as it will be able to lock Resource 2, but in the same time Process 2 needs Resource 1. So both processes cant continue work, while requesting resource is unavailable, but in the same time resource cant be released, while process works with it. In this situation one of the processes will be chosen as deadlock victim and transaction will be terminated.

To get some information about what really happened we need to do two things: first is enable deadlock trace by executing DBCC TRACEON (1222, -1) for your database. Now, after another deadlock occurs, you can expand SqlServerAgent error log:

And find information about the deadlock and the queries:

In this log you will be able to find requests that caused the deadlock and the information about the locked resources. In my case it was the conflict between delete and update queries.

No comments :

Post a Comment