Your hero for the day!
What is the difference between a blocking transaction and a deadlock? I was asked this very question recently and although I did answer correctly, I felt that I did not have an authoritative answer. Therefore…ITS LEARNING TIME!
I’ve taken the following excerpt from the MSDN page:
Blocking Locks
A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem.
Some of the common design problems that cause blocking locks are very wide tables, insufficient indexes, and tables that are not completely normalized. If your application is using SQL Server, you can monitor the total number of blocking locks by using the User Activity Monitor in Microsoft SQL Server’s SQL Enterprise Manager.
Deadlocks
Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions.
Long simultaneous transactions which variously lock and unlock resources must be carefully designed to avoid deadlocks.
Ok, so blocking is just what it sounds like, processA is waiting for processB, because processB got there first. We often find this happening at Best Buy on the demo games. Some kid is always bogarting the controller which effectively blocks your attempt to become the video game playing process.
Deadlocking occurs when two ogre heads are both eating tacos, and they both reach for the same taco (although this scenario could cause further debate…do two headed ogres reach for items independently of each head?)…they both reach for the same taco at the same exact precise time. So simple enough, if not then Microsoft also has a great diagram on this page.
This however leads me to one last question, how does SQL Server choose the victim of the deadlock? Who gets the taco?
Back to Technet:
By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). The deadlock priority defaults to NORMAL. If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.
OOOOH of course, logically the least expensive transaction…right? Sounds a little like the SQL blackbox, but acceptable nonetheless. So to our two headed ogre example, the head that is least hungry would be the victim of the tacolock.
One thing I did learn out of this was that you can set the deadlock priority. This is completely new to me, and somewhat enticing. Now I would prefer to take the steps needed to prevent deadlocks, BUT in scenarios when you don’t have the ability to alter application code, you could use this to hardcode on the backend so that say certain important nightly tasks do not get deadlocked causing alerts that require human intervention (true story).
Cheers