SQL Server: Blocking Problems
- Blocking and Deadlocks
- Common Causes of Blocking
- Troubleshooting Blocking
- Resolving Blocking Issues
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
SQL Server transactions are extremely powerful—they provide a way to incorporate business rules into your applications and protect data integrity. Behind the scenes, the transactions are implemented through the SQL Server's locking mechanism, which we discussed in a previous article. Due to the poor application design, some of the incorrect settings on the server, or poorly written transactions, the locks can conflict with other active locks. This article will tell you why the blocking conflicts occur, and suggest a few ways to resolve them.
Blocking and Deadlocks
Many people confuse blocking with deadlocks. Let's make sure we're on the same page: blocking and deadlocks are two very different occurrences. Blocking occurs due to user A's transaction locking the resources that user B wants to read or modify. Most blocking conflicts are temporary in nature, and will resolve themselves eventually. Deadlocks are much worse then blocking. A deadlock occurs when user A has locks on the resources that user B wants to modify, and user B has locks on the resources that user A intends to modify. So a deadlock is much like an infinite loop: If you let it go, it will continue until the end of time, until your server crashes, or until the power goes out (whichever comes first). Fortunately, SQL Server has a built-in algorithm for resolving deadlocks. It will choose one of the deadlock participants and roll back its transaction. The user will see a friendly message:
Your server command (process id N) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command.
On the other hand, blocking will resolve itself, but it might take quite a bit longer than your users would like. That's why you should try to avoid blocking conflicts in your applications.