Deadlocks in a SQL Server Database

, 2018-12-18 (first published: )

Deadlocks in SQL Server happen when 2 (maybe more) processes are fighting over a resource in the database and are trying to obtain an exclusive lock on that resource.  When SQL Server decides to pick a process to become it’s deadlock victim, it is an attempt to protect the database and resolve the issue of the processes fighting over the same resource.

Determining what processes are causing the deadlock can be tricky.  Luckily Microsoft provides a trace flag you can turn on (-T1204 and -T3605). These flags will write deadlock information to the SQL Server Error Log.  If you suspect you have a deadlocking problem you will most likely find the following error message in the SQL Server Output window:

Transaction (Process ID 1602) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When a deadlock occurs you can view the SQL Error Log to obtain more information regarding the deadlock and even information regarding what table is involved.

If you have a process that is doing database maintenance and you do not want this process to become a deadlock victim I suggest using the SET DEADLOCK PRIORITY  hint in your code.

If you are fighting a deadlock issue in your database the -T1204 and -T3605 trace flags are generally safe to turn on in production (you should always do this in test first) to allow you to capture and log information relating to the deadlocking to the error log.

The post Deadlocks in a SQL Server Database appeared first on VitaminDBA.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads