Avoid Database Deadlocks with Planning

esolving deadlocks is one of the more elusive solutions in database application development. Deadlocks are the dark side of concurrency, that is, they occur when some combination of locking, coding, or transaction management goes wrong. Deadlocking represents a failure of processes to work and play well together.

In this 10-Minute Solution, I discuss the controls and settings that go into transaction management and describe how to debug deadlocks by integrating resources in a manner that balances concurrency and integrity.



How do I apply locking, coding, and transaction management in a way that strikes the correct balance between concurrency and integrity and avoids deadlocks?



Integrate resources using the proper transaction isolation levels and locks, and plan ahead with your own serialization convention to avoid deadlocks.

Transaction Isolation Levels: Concurrency vs. Integrity
Transaction isolation levels dictate how visible a transaction’s intermediate product (the state of the data undergoing change before it is committed to the database) is to a second process that also needs to access the resources undergoing a change. The changes result from the traditional CRUD actions (Create, Read, Update, and Delete).

The four transaction isolation levels are:

Read Uncommitted Isolation Level The benefit of Read Uncommitted is that it offers maximum concurrency; transactions don’t have to wait or be blocked because no shared locks or exclusive locks are given. Under the Read Uncommitted isolation level, a second process can see data before the transaction that changes the data is complete. This is called a dirty read. Another possible con is the lost update: a change made by a first process is changed by a second one.

Read Committed Isolation Level Read Committed guarantees that the data viewed by a transaction is “real.” This level holds shared locks, which prevents dirty reads, but a writing transaction could cause either an unrepeatable read or a phantom record. Read Committed is the default transaction isolation level setting in SQL Server.

Repeatable Read Isolation Level Repeatable Read locks the data viewed to guarantee its stability throughout the entire transaction. A second transaction can add new inserts to the set, however. If the process needs to revisit the data, it will see the same data it did the first time, but it could see additional, “phantom” data as well.

Read Serializable Isolation Level In the Read Serializable level, the set is guaranteed to be free of phantoms because locks are held throughout the transaction. The set under consideration cannot have any updates, inserts, or deletes that weren’t there the first time. The tradeoff for serialized transactions is it has the lowest concurrency. No changes can happen to the data until it is finished.

By serializing the power to change data, the state of the data is guaranteed to be consistent (i.e., stable for each modification made). In general, the four levels illustrate the trade off in transaction isolation: maximum concurrency comes at the price of data integrity.

Ensure Integrity with Locks
Locking ensures integrity in data-changing events by informing a process that its action or contemplated action will be completed. Locks block competing processes until the resources being used by the current process are released. You can apply locks to various resources. In SQL Server 2000, you can lock a row, a key, a page of an extent, an extent of a table, an entire table, and even the entire database. You also can apply locks in an escalating scope through the following locking modes:

Shared

Update

Exclusive

Intent

Schema

Locks act in a hierarchy. They tell the higher levels controlling a resource that a lower-level lock is present (by placing an Intent lock at the higher levels). For example, for a row exclusively locked, the page and the table will each have an Intent Exclusive lock. Intent locks telegraph a process’s actions in advance to other processes that may also require that resource.

In addition to locking a specific resource, each mode indicates the severity of the restriction and the reduction in concurrency placed on the resource. Remember that each escalating lock combination causes a reduction in concurrency and an increase in the degree of exclusivity applied to a transaction.

Orchestrate Processes with Isolation Levels and Locking
SQL Server offers many commands to influence concurrency. To create the illusion of everyone working together, developers can manipulate transaction isolation levels and locking granularity and exercise coding practices to orchestrate the cooperation between processes. By doing so, they ensure that their transactions execute without invalidating each other’s changes. This orchestration is an illusion because only one process or transaction can happen at a time on a resource, but enforcing isolation and locking the DBMS can guarantee consistency and concurrency.

Anatomy of a Deadlock
Deadlocks come in two flavors: cycle deadlocks and conversion deadlocks. Both types arise when processes contend for the same resources. Cycle deadlocks arise in contention for data, either a row or a table. Conversion deadlocks occur over contention for lock escalation, where two processes are in competition for exclusivity.

In a cycle deadlock, Process A wants a resource that is already locked by Process B, and Process B wants the resource already locked by Process A. Each process waits for the other to release its lock on the resource it’s waiting for, but because both processes hold the lock on the resource requested by the other, they wind up waiting forever.

Both types, especially conversion deadlocks, can materialize from relationships that are not immediately obvious. Thankfully, the tools that ship with SQL Server and Windows 2000 are very helpful in identifying deadlock messages from the server. You can use the Create Trace Wizard and choose “Identify causes of a deadlock” or “Profile the performance of a stored procedure” to see what happened and how.

The server identifies deadlocks by raising error 1205. The server messages can be turned on and redirected by trace flags. The relevant trace flags are 1204 (for deadlock detection), 3604, and 3605. Trace 1204 gives voluminous information on the participants in the deadlock and provides a clue as to what type of deadlock you are clearing. Trace 3604 sends messages to Query Analyzer; trace flag 3605 redirects 1204’s output to the SQL Server error log.

Once the source for contention is known, you can determine which fix to apply or even elect to live with the deadlock, which also is an option. If you decide to live with the deadlock condition, you’ll have your application retry the operation after a 1205 error.

As an exercise to see how deadlocks come about, you can make a deadlock happen in Query Analyzer. Start a transaction in one session that will do an update on resource A (say, change an order number in an order header of Northwind). Open another Query Analyzer window and start another transaction on the same resource you decided to modify, but pick a different field (say, date). Go back to the first window. In the running transaction, run the command to also update date. You will succeed in tying the server in knots and the server will return the 1205 error.

Planning Ahead Is Easier Than Recovering
Identifying deadlocks is not too difficult?after all, the server complains. Resolving deadlocks is a different story. With the proper planning, however, you can debug deadlocks before they become a problem.

You can resolve cycle deadlocks by killing the cheapest process, the one that acquired its lock last or locked for the shortest amount of time. You also can set a deadlock priority high or low, which will alter the server’s selection of a deadlock victim. Even if you decide to live with the deadlock condition, you can still control which process the server picks as the deadlock victim. For example, if you determine that the deadlock occurs between a transaction processing operation and an operation servicing a reporting application, you can set the report application’s deadlock priority to low so that the server kills it instead of the transaction process.

Note: Altering the server code to trap for the error is not an option, because once the server detects the deadlock it terminates the transaction, which short-circuits your error-detection code. The retry must happen at the application.

While debugging cycle deadlocks is straightforward, conversion deadlocks are not as simple, because with them, locks can accumulate and become unmanageable. One way to plan for deadlocks is to adopt your own serialization convention to help the server do the right thing:

• Pick an order for things to happen and enforce that order everywhere. Alphabetical order by table name will do. This means that the server can serialize access to inserts, updates, and deletes in a universally observed order.
• Have your applications test for error 1205 and retry the transaction again.
• Code procedures so that the data modification comes before a select statement. In other words, do your update first, then select.

For example, despite looking fine, the following procedure deadlocked because of contention for the row that holds the nexid value:

Begin TransactionSELECT @Next_Number = NextID_Next_num 	FROM NextID 	WHERE (NextID_Id_name = @FieldName)UPDATE NextID SET NextID_Next_num = (@Next_Number + 1) 	WHERE (NextID_Id_name = @FieldName)Commit TransactionRETURN @Next_Number

Here is a different approach, in which the update comes before the select:

Begin Transaction  UPDATE NextID SET NextID_Next_num = NextID_Next_num + 1 	WHERE (NextID_Id_name = @FieldName)SELECT @Next_Number = NextID_Next_num 	FROM NextID WHERE  (NextID_Id_name = @FieldName)Commit Transaction

Once you’ve set an order for your server to approach your data modifications, all you have left to do is fix a deadlock. Since the server picks its victim, you are limited to retrying your transaction. You can do so by coding a retry loop in your application.