Lock Question - When is an Update (U) lock issued?

We are trying to resolve a deadlock problem. The transaction that is getting rolled back is attempting to issue an Update (U) lock on a resource that another transaction has an Exclusive (X) lock on. According to Books Online (http://msdn.microsoft.com/en-us/library/ms175519.aspx), an Update lock is supposed to prevent deadlocks, not cause them.

So, my question is, why/when is an Update lock applied to a resource? We're a little confused about this because the resource that is attempting to have the Update lock applied to will not be updated by the process that is having the transaction rolle开发者_如何学Pythond back.

Thanks for your help on this.

Randy

You are going to need to do a bit more research to find out what is actually locking, what isolation level each query is in, etc.

Some helpful resources.

SQL Server Transaction Isolation Levels and their Locks

SQL Server Lock Types and Lock Hints

There's a whole universe of "what if" behind what causes deadlocks (by which I mean, there's no way to tell from your initial post what's really going on). Could be table locks, could be locks on indexes; could be oustanding transactions you are not aware of; could be table header locks, could be tempdb issues (very unlikely), who knows?

The best method I've ever found to diagnose deadlocks works like so:

• Fire up SQL Profiler, configure it with the "Deadlock Graph" and "Lock: Deadlock" events, and be sure to include the TextData column
• While Profiler is running, cause your application to generate the deadlock
• Select the "Deadlock Graph" profiler row, and you'll get a simple yet confusing graphic display of what's going on. This might help you figure out what's really going on.
• If that doesn't help, the graphic is generated by Profiler based on a very detailed lump of XML. Extract this string (select the Deadlock Graph row, Ctrl+C to copy, paste in your text editor of choice, and delete all the columns but the XML), and then review the XML (in your preferred XML editor).

To-date, once I've gotten and worked through that XML, I've always been able to figure out what was causing the deadlock. It's a good way to learn how weird and convoluted some of SQL's internals can get.

Probably you do not handle your transaction isolation properly and are in serialized transaction mode.

You should

• Use the proper transaction isolation on the connection.

• Sometimes use hints on SQL, like the NoLock hint when you basically just read some data and don't do anything else with it anyway in a transaction.