are used by relational database management systems to increase user concurrency (more users) while guaranteeing data consistency. A
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):
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
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
Code-based changes (Developer tasks):
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!