What is a deadlock

I’ve run into a fair bit of confusion in the forums recently on this, so I thought I’d quickly explain what a deadlock is, and how it differs from blocking and locks.

Locks

Locking is a normal part of SQL’s operations. When a row is read by a query, a lock is placed on that row to prevent the row from changing during the read. When a row is modified a lock is placed on the row to prevent any queries reading the value as it is changing.

Locks can occur at the row level, at the page level or at the table level. As long as they are short lived, they are not a problem in and of themselves

Blocking

Blocking occurs when one process needs to read or modify a piece of data that is locked by another process. Blocking, if extended can result in slow-running queries and performance problems. If multiple processes are blocked by one process and are themselves blocking still other processes, it’s called a blocking chain

Blocking situations will resolve by themselves, although it may be necessary for an administrator to kill the root of the blocking chain to allow the other processes to continue.

If the killed process was doing data modifications, those modifications have to be rolled back. This rollback can take significant time and resources locked by the process will remain locked until the rollback is complete.

Deadlocks

Deadlocks occur when two (or more) processes are holding locks on resources and are waiting for locks on resources in such a way that they will never resolve.

As an example. Process 1 holds a lock on TableA and wants a lock on TableB. Process 2 holds a lock on TableB and wants a lock on TableA. Neither process can run and they will both wait forever if left alone.

SQL Server contains a deadlock detector which is capable of noticing the majority of cases of deadlocks that occur. When it notices a deadlock, it will pick one of the processes and automatically kill it. The client of that process will receive an error message saying that their process was involved in a deadlock and picked as the deadlock victim. The client of the other process will not receive any messages.

It is not necessary, not will it likely be possible, to manually kill a connection involved in a deadlock. The SQL deadlock detector takes care of that.

Hope that cleared up a few things. In a future post I’ll go over the various methods of deadlock detection and look at some pros and cons to them.