SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Deadlocks in a SQL Server Database

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 -T2605 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.

vitamindba

VitaminDBA.com is a source of SQL Server News and Tips coming from a SQL Server sr. database analyst with 10 years of IT experience ranging from Windows Server, Network, and SQL Server administration. I upload articles on the blog on a fairly consistent basis with the intention of at least 1 new article a week.

Comments

Leave a comment on the original post [vitamindba.com, opens in a new window]

Loading comments...