Resolving Deadlocks in SQL Server 2000 (Cont.) SQL Server Deadlocking Factors You can view deadlocks as containing four factors: - Lock modes and incompatibility
- Transaction timing
- The order of lock requests
- Isolation level of the transactions
Lock Modes and Incompatibility SQL Server has a number of lock modes. The most common are listed in Table 2. The core set of locks on resources are the shared and exclusive locks. They can be granted on rows, pages, and tables. These are the types of locks you'll see most often in deadlocks. The next set, the intent locks, are higher-level locks granted when lower-level locks are also granted. They also can occur in deadlocks, especially the hard ones. The last set, the schema locks and bulk update, are rarely involved in deadlocks. What's important is that not all of these types of locks are compatible; in fact the majority aren't, as illustrated in Table 3. It can be difficult to find and test lock compatibility. Table 3 is taken from Books Online and enhanced, and I've tested all the combinations that appear there. However, there are other types of locks that may also be incompatible. You can see at a glance, though, that more combinations are incompatible than are compatible. Transaction Timing Lock incompatibility is not the only factor in producing deadlocks. Transactions must overlap in time for blocking and deadlocking to occur. Usually that means that the database must be active enough for short transactions to coincide closely in time, or that transactions have sufficiently long duration to make blocking and deadlocking likely. As a result, sometimes deadlocks require a large number of concurrent transactions to surface. When testing your application, you might find that a load of five or ten simultaneous users is not enough to reveal deadlocking, but that a load of twenty users is. The Order of Lock Requests The transactions must coincide in time, but they must also grant and request locks in a certain order. That is, the initial locks must be granted before incompatible locks are requested. Sometimes changing the order of lock requests may release locks early or remove incompatible locks. Isolation Level of the Transactions Another key component of deadlocks is the isolation level of the transactions. SQL Server has four isolation levels, and the default is READ COMMITTED. That means that your transaction will only read committed data, not uncommitted data. At this level, your transaction must get a shared lock in order to read a resource, and if it is already locked with an incompatible mode, your transaction must wait. If you lower the isolation level to READ UNCOMMITTED, so that you allow the transaction to read uncommitted data, the requirement for shared locks is lifted. | " |
The core set of locks on resources are the shared and exclusive locks. They can be granted on rows, pages, and tables. These are the types of locks you'll see most often in deadlocks.
| " |
In the READ COMMITTED and READ UNCOMMITTED isolation levels, shared locks are released as soon as a read query is completed. In the higher isolation levels of REPEATABLE READ and SERIALIZABLE, your transaction will hold the shared locks until the end of the transaction. As a result, the higher isolation levels make deadlocks more likely. Table 4 summarizes how shared locks behave with various isolation levels. Isolation level is often a critical factor in deadlocking, but not always: deadlocks involving only exclusive locks are independent of the isolation levels, because the isolation levels affect only how shared locks are treated. You might think that deadlocking involves only exclusive locks, but some deadlocks involving shared locks can occur with the READ COMMITTED isolation level, whereas others involving shared locks require isolation levels higher than READ COMMITTED. Types of Lock Deadlocks Deadlocks can involve more than two threads, often quite a number of threads. When many threads are involved in a deadlock, SQL Server will choose one of the threads as a victim, return to the deadlock, choose another thread as a victim, and then eventually narrow the entire deadlock down to a core of two threads. You can view deadlocks as falling into two categories, depending on whether the transactions initially lock the same or different resources. When the transactions each lock a single resource, let's call that a "single-resource" deadlock, and when there is more than one resource, we'll call that a "multiple-resource" deadlock. Single-Resource Deadlocks The single-resource deadlock is sometimes called 'conversion' deadlock, because it occurs when each transaction simultaneously attempts to convert its compatible lock to an incompatible lock. | " |
Mixed X-S deadlocks can be much more difficult to diagnose. They can take place with the default isolation level of READ COMMITTED or higher and involve SELECT statements.
| " |
The initial grant stage requires shared locks, because both threads must obtain a lock on a single resource. The single-resource deadlock also requires an isolation level higher than READ COMMITTED so that the share locks will continue to be held through the end of the transaction. The blocked request stage requires exclusive or update locks, locks that are incompatible with shared locks. Table 5 shows how a single-resource deadlock can occur. The HOLDLOCK hint causes each SELECT to enter into the SERIALIZABLE isolation level, so that the shared locks are held for the duration of the transaction. This is a comparatively rare type of deadlock because the SERIALIZABLE isolation level and HOLDLOCK hint are rarely used in practice. Multiple-Resource Deadlocks The multiple-resource deadlock is sometimes called a 'cycle' deadlock, but that is a misnomer. All deadlocks involve a cycle of some kind. This type of deadlock occurs when each thread requests an incompatible lock on the other's resource. The grant stage normally requires exclusive locks, because shared locks will not be held to the end of the transaction under the default isolation level of READ COMMITTED. There are many variations of the multiple-resource deadlock, but they can generally be classified into two sets. Some multiple-resource deadlocks involve exclusive locks only; let's call them X-only. In this group, threads make just exclusive lock requests. Others involve mixed exclusive and shared (X-S). First, threads are granted exclusive locks. Then the threads request shared locks and then they are blocked. The mixed X-S deadlocks can be more subtle and challenging. Table 6 shows how an X-only deadlock can occur. Note that the X-only type of deadlock works at any isolation level, because exclusive locks are always held to the end of a transaction. This is a fairly easy deadlock to detect and prevent. All you have to do is enforce the rule that transactions in stored procedures or other code have to access common sets of tables in the same order. The X-S, exclusive with shared, type of deadlock is a more complex and subtle type of deadlock. There are many variations: X-X:S-S, X-X-X:S-S-S, X-IS:IS-X, S-IX:IX-S, X-X:U-U, etc. All of these can occur with the READ COMMITTED isolation level, so they are actually the type that you are more likely to see in subtle and complex deadlocks. Table 7 shows one example of an X-S type of deadlock. Notice in this case that each transaction attempts to read another row in the same table. The rows are the resources that are locked by the transactions. It might be rather rare that a transaction would want to read another transaction's row just after an update, so Table 8 shows a much more subtle type of X-S deadlock. In this example, each transaction inserts a row into the same table, and then reads the table for more information, perhaps to do another insert. This is a much more common pattern of deadlock, something that transactions in stored procedures are legitimately required to do. Mixed X-S deadlocks can be much more difficult to diagnose. They can take place with the default isolation level of READ COMMITTED or higher and involve SELECT statements. |