Resolving Deadlocks in SQL Server 2000 (Cont.) Finding the Cause of a Subtle Deadlock The first step in finding the cause of a subtle deadlock is to find a method to reproduce it. This may involve stress or load testing the system, or just watching it under use. It also helps to find the minimal conditions required for the deadlock, isolating the application behavior so that you can narrow your tracing to just the deadlock behavior. If you reproduce the deadlock with both 1204 trace flag and SQL Trace active, you can extract the essential data. Examining the trace flag 1204 output will help you identify which spids are involved, and what types of locks lead to the deadlock. You can use SQL Profiler to get each spid's transaction history by saving the SQL Trace data output to a SQL Server table. Once you have the trace output in a table, you can query it. Find the beginning and ending row numbers of each spid's transaction, and then select the StartTime and TextData for a given spid. This will give the history of the transaction in question as a series of commands in the Query Analyzer output window. Figure 3 shows a SQL command you can use to extract the transaction history, using the example of an X-S deadlock. 
Figure 3. You can query a trace table to determine a transaction's history. Once you have scripts of the transaction histories from Query Analyzer output, you can arrange the histories according to StartTime, possibly trimming out extraneous commands. You can then reproduce the deadlock under controlled conditions using Query Analyzer. Just put the transaction history scripts side by side and step through the histories command by command, as shown in Figure 4. 
Figure 4. You can step through transaction histories one step at a time. This can be very tedious to do, so it's a strategy you'd only use if the deadlock is particularly subtle. However, it will give you insight into the exact cause or causes of the deadlock. As you're stepping through the transaction histories, you can observe the locks granted and held using sp_lock. When you spot the initial locks granted, and then the lock requests that are waiting, you'll know exactly which commands lead to the deadlock. Deadlock Resolution Techniques There are basically four steps you can use to resolve deadlocks. These are: - Remove incompatible lock requests
- Change the timing of transactions
- Change the order of resource requests
- Change the isolation level
Remove Incompatible Lock Requests The queries involved may be obtaining too many locks, or locks where they are not needed. This may happen when WHERE clauses are too broad, or broader than the query really needs. For example, a query might be reading a whole table or range of rows where only one row's data is actually desired. Queries may be obtaining or holding the wrong kinds of locks using hints such as HOLDLOCK or UPDLOCK unnecessarily. Change the Timing of Transactions You can change the timing of transactions. A deadlock requires transactions to coincide in time, and often they must coincide very closely. Making the transactions shorter or briefer may help, because long transactions increase the likelihood of deadlocks. Perhaps the transactions may not need to overlap in time at all. You might find that batch processes can be run sequentially rather than in parallel, or at a time when users are not online. Change the Order of Resource Requests For X-only multiple-resource deadlocks, you should ensure that resources are accessed in the same order. This is a good general rule for stored procedures accessing the same or similar sets of tables. Changing the order of resource requests will not usually work for mixed X-S multiple-resource deadlocks, because they occur when both transactions are accessing resources in the same order. Change the Isolation Level Changing the isolation level of queries may sometimes be the only way to resolve stubborn deadlocks. For single-resource conversion deadlocks, you can lower the isolation level of the initial locks, removing SERIALIZABLE transactions or HOLDLOCK hints. For the mixed X-S multiple-resource deadlocks, you can use a locking hint on the share lock queries, which are SELECT statements. You can use the NOLOCK to lower the isolation level of the query, or lower the entire isolation level of the transaction using SET ISOLATION LEVEL: SET ISOLATION LEVEL READ UNCOMMITTED
If you do not want your transaction to read uncommitted data, you can use the READPAST hint to have your share lock queries read past a locked resource. This is not the same as lowering the isolation level of the transaction or query because the query will still not read any uncommitted data. Generally using READPAST is a cleaner method of resolving a mixed X-S multiple-resource deadlock than a NOLOCK hint. Deadlocking can be an annoying problem in an active, transactional system. But with SQL Server Profiler and the output of the trace flag 1204, you can find ways to resolve even the most stubborn deadlocks. Ron Talmage |