SQL deadlocking can be a difficult and a complex problem to solve. SQL deadlocks may occur anytime the database application has to handle multiple users and complex transactions. SQL deadlocks can directly lead to server timeouts and end-user errors.

Deadlocking can happen to any database system that depends on locking to ensure that user transactions don’t interfere with each other. It’s important to understand what causes SQL deadlocks in order to isolate and resolve them.

Database transaction is the primary concept behind deadlocking. To provide users with a view of the database that is consistent, SQL Server must lock some of its resources while the work is being completed. A SQL Server deadlock happens when at least two processes attempt to acquire locks in a manner that is incompatible. The actual deadlock occurs when multiple processes are waiting on each other to get access to a resource that one of the other processes is already holding.

SQL Server threads can wait on application events, memory resources, other threads, parallel query resources, and locks. Deadlocking can happen with application events, memory waits, threads, parallelism and locks. The most common scenario of SQL Server deadlocking involves locking of actual table objects.

Deadlocks typically involve at least one resource and two transactions. It’s helpful to view deadlocks as occurring in two stages. The first stage happens when each thread is granted a lock on a particular resource. Usually, they are different resources, although they could also be the same.

The second stage happens when there is a blocked request and each thread requests a lock that is incompatible on a resource that belongs to the other thread. Prior to the thread being released, each thread waits on the other to release its lock. The SQL server will then detect the deadlocked state and roll back one of the transactions. This will typically lead to an end-user error.

It’s important to note that deadlocking is different than blocking. Some momentary blocking is normal and happens because one thread is waiting on another. In any database system that uses locking to maintain transaction isolation and data integrity, blocking is expected. However, blocking that is long in duration is considered a problem. Short periods of blocking occur frequently in a system that is active. The kind of blocking that needs to be avoided is when two threads block each other mutually. This type of blocking can result in deadlocks.

Usually, SQL Server will roll back the least expensive transaction involved in a deadlock. This can be overridden by using a SET DEADLOCK_PRIORITY session-level option. However, when both threads have the same DEADLOCK_PRIORITY setting, the Lock Monitor will select one of the processes as the victim.

When rolling back a victim transaction, the SQL Server engine will abort the batch, roll back its transaction, and cancel the query. This is different from a 1205 error. SQL deadlock cannot be detected from within Transact SQL by using command @@ERROR. This has a severity level of 13 which can be corrected by the user, and therefore the client may resubmit the query.

There are several tools that can help isolate sources of SQL deadlocks. These tools detect objects and transactions involved in a deadlock. Most of these tools use a periodic detection system that inspects the processes every several seconds to determine if there are any deadlock cycles.

While completely eliminating deadlocks from a highly transactional application is not an easy task, it can be accomplished through a combination of following best design practices and using tools that detect and isolate SQL deadlocks.

Click here for more information about sql server performance tuning and sql deadlocks.