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.
