Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shishirkhandekar
Starting Member
23 Posts |
 Posted - 2011-10-27 : 21:02:10
|
| Can anyone explain this to me? Is this a bug? As per locking concepts, this is not possible (or wasn't until I found it). The update statements are operating on the same table but different columns. One does a IF EXISTS and then issues an UPDATE statement. The WHERE clause of the IF EXISTS check and the actual UPDATE statement is the same. The other one issues the UPDATE statement directly. The locked resource is the same, the primary key. <resource-list> <keylock hobtid="72057594063093760" dbid="12" objectname="Some Procedure" indexname="PK_RAPTable" id="lockadef300" mode="U" associatedObjectId="72057594063093760"> <owner-list> <owner id="processa212bc8" mode="U"/> </owner-list> <waiter-list> <waiter id="process5e27288" mode="U" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594063093760" dbid="12" objectname="Some Other Procedure" indexname="PK_RapTable" id="lock6dd8500" mode="U" associatedObjectId="72057594063093760"> <owner-list> <owner id="process5e27288" mode="U"/> </owner-list> <waiter-list> <waiter id="processa212bc8" mode="U" requestType="wait"/> </waiter-list> </keylock> </resource-list>Thanks in advanceShishir Khandekar |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-28 : 00:15:14
|
| Your locking seems to be spanning across multiple pages. Are the tables involved are heaps ?PBUH |
 |
|
|
shishirkhandekar
Starting Member
23 Posts |
Posted - 2011-10-28 : 10:32:26
|
| No, they are not. They do have a clustered index. Even if it spanned across multiple pages, lock mode U is not compatible with any other so how could the other transaction get a U mode lock on the same resource? |
|
|
|
|
|
|
Conversion deadlock. All resources stuck on "U"