Get Rid of Deadlocks

Locks are used by relational database management systems to increase user concurrency (more users) while guaranteeing data consistency. A deadlock is when two locks interfere each other, and is caused by one process locking a row, page, partition, or table while it waits for another row, page, partition, or table to become available, but the one it's waiting on is locked by another process that's waiting for what the first process has locked. When that happens, SQL Server will detect it and roll back one of them (the one that requires the least work to redo will be the one that gets rolled back).

Occasional deadlocks are common, but it's uncommon for them to occur so often that they become a problem. Deadlocking does become a problem sometimes, though, and when my customers run into it, they usually ask for my assistance. While there are many articles about how to detect deadlocks, I haven't found much info about how to eliminate them, reduce them, or minimize their impact, so I'm going to list all the alternatives I'm aware of here, both for future reference for myself and in case it might help someone else:

SQL Server-based changes (DBA tasks):

• Change indexes. After you enable the 1204 and 1222 trace flags and determine which indexes are getting deadlocked, you can often eliminate deadlocks by adding an index, changing an index, or every once in a while by deleting an index. (See https://msdn.microsoft.com/en-us/library/ms178104.aspx.)
• Enable page-level and row-level locking. Page and row locking are allowed by default, but if someone's turned them off, you might need to consider turning them back on. (See https://technet.microsoft.com/en-us/library/ms189076.aspx and https://technet.microsoft.com/en-us/library/ms188388.aspx.)
• Turn on row-versioning. (See https://msdn.microsoft.com/en-us/library/ms177404.aspx.)
• Turn off parallel operations. If parallel operations are possible (the server has multiple processors/cores) and allowed, it's possible that turning it off (by setting MaxDOP to 1) will reduce or eliminate deadlocking. You should avoid this if something else will work, so that most workloads can benefit from parallelization. (See https://msdn.microsoft.com/en-us/library/ms181007.aspx.)

Code-based changes (Developer tasks):

• Change code to trap SQL Server 1205 errors and resubmit the request. A 1205 error means the request was chosen as a deadlock victim, and a try/catch can resubmit without the user ever being aware of it.
• Redesign the application to ensure that all database requests are serialized. Depending on the application, that can be easy, or it can be a major effort requiring the introduction of middleware.

If anyone else know of any other options, please let me know in the comments!

Comments

• Anonymous
  January 01, 2003
  The comment has been removed

• Anonymous
  January 01, 2003
  Hi Rob, Sorry for the delay in replying. I have notifications turned on, but I'm still not getting them when people post comments. Yes, the main idea in my "Redesign the application" option is to guarantee that no two processes use resources in the opposite order. How, or if, that could be accomplished, depends on the nature of the application. You're in center field!

• Anonymous
  December 17, 2011
  In the Developer list of recommendations (assuming one has control over the application), could you rewrite all updates on commonly deadlocked tables so that they affect all tables in the same order?  I had considered this at one point, even if it meant making unnecessary updates to a parent row before updating one of its related child rows.  The idea was to guarantee that no two processes would go at resources in the opposite order.  Am I in left field?

• Anonymous
  December 28, 2011
  We were able to eliminate deadlocks (caused by GUID's as foreign keys) by turning on row versioning for the server.  Using NOLOCK hints made no difference as the foreign key lookup always requests a lock... Only a solution for MSSQL 2005 or newer.