LearnNowOnline

Blog

SQL Server 2012: Blocking and Deadlocks

Excerpt by Don Kiely | May 16, 2013

When you modify data in SQL Server, it is important to understand how isolation levels and locking can affect performance and concurrency. The term concurrency refers to the ability of multiple users or applications to access the same data. When you update data, concurrency conflicts occur when multiple users select data and then try to update the data simultaneously. SQL Server places various kinds of locks on database objects and table rows in order to ensure data integrity.

One problem is that locks on the data can delay the updates. Another problem is that users may be able to update data that has changed since they last inspected the data. Applications often prevent this by adding a WHERE clause to the update to ensure that the update will succeed only if the data in the database still has the values that the user originally retrieved. The following article discusses Blocking and Deadlocks.

Blocking and Deadlocks

SQL Server is very efficient at applying and releasing locks. If you are modifying data using the default READ COMMITTED isolation level, locks will be applied and quickly released on each row of data. But anytime a resource is locked in SQL Server, it may cause other processes to be unable to access data necessary for the other processes to perform its actions. Two situations you have to be aware of and prepared for are blocking and deadlocks.

Blocking

Blocking occurs when locks are held for too long. When a transaction is blocked because another transaction has locks on data, it just sits there and waits. Over a network, blocked transactions will either hang or eventually time out with an error message.

You can reduce the chance of common blocking problems by avoiding user interaction in the middle of transactions, keeping data modifications short, and avoiding recursive operations and nested triggers.

Deadlocks

A deadlock occurs when two separate processes are each holding a resource that the other needs. Each process is waiting to release the resource it is holding until the other resource becomes available. Unless one of the processes is forced to yield, they will stay deadlocked forever. Deadlocks are difficult to simulate in a development environment; they seem to appear only when your database is running and many users attempt to complete the same operation at the same time.

The Figure below displays how a deadlock works. In this case, Transaction 1 has a lock on Resource 1 and needs a lock on Resource 2 to perform its work. Transaction 2 has a lock on Resource 2 and needs a lock on Resource 1 to perform its work. Transaction 1 can't get a lock on Resource 2, and Transaction 2 can't get a lock on Resource 1. So the transactions sit, deadlocked, waiting for the other transaction to let go of its locks.

SQL Server uses an interval to determine which processes are running and which are blocked. If this interval passes twice and a process is still blocked, SQL Server chooses a deadlock victim. The victim's transaction is rolled back, and error code 1205 is returned to the client application of the losing transaction. Your error handling routine in the client application can test for error 1205 and resubmit or cancel the query. If SQL Server did not select a deadlock victim, then eventually your server would run out of available processes and crash.

To avoid deadlocks, access objects in the same order every time, so that if a process is blocked it goes into a wait queue until the process that is holding the lock is complete.

This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.