Deadlock Victim
SQL Server used to return error messages to queries saying your processed had been canceled because you query was elected as the Deadlock Victim for a blocking query. This event was due to the fact that your process held a lock on an object necessary for another query to complete. That other query held a lock on an object your query needed to complete. Neither query would be able to progress, hence the term deadlock.
When a deadlock occurs in SQL Server it elects one process to be terminated abnormally. When that occurs, an error message is returned stating that the process was elected to be the deadlock victim.
I haven’t seen this error for years. Previously, it was something I frequently trapped for in applications, allowing for a retry of the query automatically, should it find itself an elected deadlock victim. I don’t think about it much anymore.
There are some things you can do to help reduce deadlocks such as addressing tables in the same order in all your queries. One of the most helpful things is to maintain locks for the shortest time necessary, releasing objects for use by other processes. I think as we are moving to stateless applications, and are not maintaining connections and locks for longer periods, we are seeing less deadlocks.
One common report I run in SQL Server Management Studio when performance slows down is the Blocking Queries report. In my instances, they always return with no data. That doesn’t mean I don’t have blocking queries. It means the blocks are so quick they don’t make it into the report…nothing is holding locks for long periods of time. This is a great report to run if you think you might be having blocking, or deadlocking in your system.
Are you still seeing deadlocks? Share your experience with comments below or drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Putting Data to Work for Mid-Market Companies
read more)