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 3

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

Table 2. The most common types of resource locks.

Lock Mode

Abbreviation



Description

Shared

S

Used for reading (read lock)

Update

U

Used to evaluate prior to writing (may become exclusive)

Exclusive

X

Used for writing (insert, update, delete)

Intent Shared

IS

Have or intend to request shared lock(s) at a finer level

Intent Update

IU

Have or intend to request update lock(s) at a finer level

Intent Exclusive

IX

Have or intend to request exclusive lock(s) at a finer level

Shared Intent Update

SIU

Have shared lock with intent to acquire update lock at a finer level

Shared Intent Exclusive

SIX

Have shared lock with intent to acquire exclusive lock at a finer level

Update Intent Exclusive

UIX

Have update lock with intent to acquire exclusive lock at a finer level

Schema-Stability

Sch-S

Used when compiling queries

Schema Modification

Sch-M

Used for DDL operations (ALTER or DROP) on a table schema

Bulk Update

BU

Used with bulk copy into a table with either TABLOCK hint or table lock option is set


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.

Table 3. Most resources lock combinations are incompatible.

Granted:

Requested

S

X

U

IS

IX

SIX

Sch-S

Sch-M

BU

S

Yes

No

Yes

Yes

No

No

Yes

No

No

X

No

No

No

No

No

No

Yes

No

No

U

Yes

No

No

Yes

No

No

Yes

No

No

IS

Yes

No

Yes

Yes

Yes

Yes

Yes

No

No

IX

No

No

No

Yes

Yes

No

Yes

No

No

SIX

No

No

No

Yes

No

No

Yes

No

No

Sch-S

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

Yes

Sch-M

No

No

No

No

No

No

No

No

No

BU

No

No

No

No

No

No

Yes

No

Yes





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