- 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
Common Causes of Blocking
In this section, we'll go over a few most common causes of blocking.
More often than not, blocking problems are due to poor application design. A transactional database model should be highly normalized. There are several normalization rules that you should adhere to when designing your database. We won't go into the details of normalization, but to summarize the concept—you should not keep any redundant data in your database. Transactional databases should not have any repeating columns, and each piece of data should be stored only once. That way, the transactions modify lean tables, complete, and release locks quickly.
The lack of appropriate indexes can often cause blocking problems as well. If you have a transaction that modifies many rows of a table without a clustered index, SQL Server might decide to acquire a table lock for the duration of your transaction. The rest of the transactions will be blocked until your transaction is committed or rolled back.
Poorly written transactions are by far the most common cause of blocking. Such transactions ask for an input value from a graphical user interface (or a Web screen) in the middle of a transaction. Imagine what happens if user Lucy gets interrupted by a phone call in the middle of a transaction. The transaction will hold the locks until Lucy gets off the phone and decides to input the requested value. If a phone call is fairly quick, we might get away with a few hate emails from the rest of the application users. But Lucy might decide to take a lunch break or go home right after her phone conversation. Therefore, never ask for a user input inside a transaction.
Inappropriate use of locking hints can be yet another cause of blocking. If you force SQL Server to acquire 50000 row level locks, your transaction might have to wait until other transactions complete, and this many locks are available.