Have Geek Brain

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

We are trying to resolve the deadlock problem. The transaction being committed is trying to release an Update (U) lock on a resource that has another transaction enabled with an Exclusive (X) lock. According to Books Online ( http://msdn.microsoft.com/en-us/library/ms175519.aspx

), blocking updates should prevent deadlocks, not cause them.

So my question is, why / when does an update lock apply to a resource? We're a little confused by this because a resource trying to block an update lock being applied to will not

will be handled by the process in which the transaction is rolled back.

Thanks for your help with this.

Randy

+2


a source to share


3 answers


There's a whole universe of "what ifs" behind what causes deadlocks (by which I mean, there is no way to tell from your initial post what is really happening). There may be table locks, there may be index locks; may be outstanding transactions that you are not aware of; there could be table header locks, there could be tempdb issues (very unlikely), who knows?

The best method I've ever found for diagnosing dead ends works like this:



  • Start SQL Profiler, configure it with the "Deadlock Graph" and "Lock: Deadlock" events and be sure to include the TextData column li>
  • While Profiler is running, make your application generate deadlock
  • Select the "Deadlock Graph" profiler line for a simple yet confusing graphical display of what's going on. This can help you understand what's really going on.
  • If that doesn't work, Profiler creates a graphic file based on a very detailed piece of XML. Extract this line (select the Deadlock Graph line, Ctrl + C, copy, paste in your text editor of choice and remove all columns except XML) and then view the XML (in your preferred XML editor).

Now that I got and worked through this XML, I always had to figure out what was causing the deadlock. This is a good way to find out how strange and confusing some of the internals of SQL can get.

0


a source


You will need a little more research to find out what is actually blocking, what isolation level each request is at, etc.

Some helpful resources.



SQL Server Transaction Isolation Levels and Locks

Types of blocking and blocking SQL Server

+1


a source


You might be handling your transactional isolation incorrectly and are in sequential transaction mode.

You should

  • Use correct transaction isolation on the connection.

  • Sometimes use SQL hints, like the NoLock hint, when you are basically just reading some data and doing nothing with it in a transaction anyway.

0


a source






More articles:


All Articles