Get Rid of Deadlocks

[SQL-Server-Team]

First published on TECHNET on Dec 12, 2011

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 http://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 http://technet.microsoft.com/en-us/library/ms189076.aspx and http://technet.microsoft.com/en-us/library/ms188388.aspx .)


• Turn on row-versioning. (See http://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 http://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!