Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Resolving Deadlocks in SQL Server 2000 : Page 4

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place.


advertisement
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.

Table 4. Isolation level affects how shared locks are handled in a transaction.

Lock Mode

Read Un-committed



Read Committed

Repeatable Read

Serializable

Shared

Held until data read and processed

Held until data read and processed

Held until end of transaction

Held until end of transaction

Update

Held until end of transaction unless promoted to exclusive or released

Held until end of transaction unless promoted to exclusive or released

Held until end of transaction unless promoted to exclusive

Held until end of transaction unless promoted to exclusive

Exclusive

Held until end of transaction

Held until end of transaction

Held until end of transaction

Held until end of transaction


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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date