Why a Deadlock Is Not Just “Really Bad Blocking”
By Kathi Kellenberger
I frequently speak with customers who have SQL Server performance issues. Sometimes they complain that there is deadlocking and go on to describe scenarios where queries are blocked for long periods of time. The misunderstanding is that deadlocking is a long running blocking situation.
The way I like to describe blocking is by comparing it to a busy intersection with traffic lights, as shown in Figure 1. You may be annoyed while you wait for the traffic light to turn green, but the intersection is actually working as designed. Once the light turns green and the cars are out of the way, you can proceed.
Figure 1. Waiting for a traffic light is a form of being blocked
Most of the time, the wait times at an intersection controlled by traffic lights are not out of the ordinary. Occasionally, a crash causes such an intersection to be blocked much longer.
In SQL Server, blocking is caused because a statement has locked a resource that another query needs. Again, this blocking is by design and most of the time it happens so fast that you don’t really notice it. A locked resource could be a row or a page or even a table.
Occasionally, a poorly written query, maybe a Cartesian product, causes excessively long blocking. In this case, the responsible query may cause I/O and CPU to spike, and possibly also to consume most of the buffer. This spiking of I/O and CPU is like that car crash I mentioned.
You may be wondering how long blocking might last. It will last forever unless the blocking query completes, or until either the blocked or blocking query is killed. Software applications often will have timeouts that end the blocking.
I recently had to troubleshoot a strange blocking situation. A query running one afternoon caused several scheduled jobs that usually run at night to hang. Dozens of queries from the application had timed out because of the blocking. We finally traced the cause to a transaction that had been left open running from a service. This was from an explicit transaction, one that was started with BEGIN TRANSACTION, that had errored out. Unfortunately, the error trapping did not rollback the transaction so the transaction was left open until we killed it the next day.
While blocking can last hours, or forever if nothing changes, deadlocking will last for five seconds at the most. Deadlocking is a special locking situation often caused by two connections updating rows in two tables in opposite order. I like to compare deadlocking to a downtown traffic jam at 5 pm in a major city, as illustrated in Figure 2. Imagine that two cars – labeled as A and B in the image -- need to turn left, but the line of cars behind each one is blocking the other.
Figure 2. Deadlocks can be likened to a downtown traffic jam
The deadlock in Figure 2 will be cleared up only if one of the cars gives up and goes straight. A deadlock condition in SQL Server can never be cleared up unless one of the transactions is killed. Because of this, the database engine scans for deadlocks every five seconds. If a deadlock is found, SQL Server will roll back one of the transactions, whichever is easiest to rollback. The other transaction can then continue.
Following is an example by which you can demonstrate SQL Server’s behavior of checking every five seconds:
UPDATE Table1 SET Col1 = Col1 + 1
--Switch to connection 2
UPDATE Table2 SET Col2 = 0
DELETE FROM Table1
--Switch to connection 1
UPDATE Table2 Set Col3 = 5
Within five seconds of running the last statement, one of the transactions will be rolled back. You’ll see this creepy sounding message:
Deadlocks are situations that cannot be resolved naturally, so SQL Server finds and resolves deadlocks automatically.
We never like to see deadlocking or excessive blocking in the database. In general, keeping transactions short and queries efficient is the way to cut down on these issues. Blocking and deadlocking can never be eliminated, but can be reduced with good T-SQL code.
About the Author
Kathi Kellenberger is a consultant with Linchpin People. She enjoys writing and speaking on SQL Server topics, having written over two dozen articles, contributed to four books, and presented at many SQL Server events. In her spare time, Kathi enjoys spending time with family and friends, singing and cycling.