sql server deadlock (keylock)

[mirthcyy]

hi guys,

I have two procedures: one for selecting data from a table and do some
calculations and the other for update or insert into the same table.

There are two indexes on that table: one clustered (primary key):
Pk_Table and one nonclustered: Ix_Table.

I've captured a few deadlocks on those two procedure executions. It
seems one proc gets x lock on the Pk_table key first and the other
proc gets the x lock on Ix_Table and then both proc tries to get s
lock on the other key. Then the deadlock happens. The below is part of
deadlock xml:

<resource-list>
<keylock hobtid="72057594130202624" dbid="5"
objectname="DBName.dbo.Table" indexname="Ix_Table" id="lock890c8300"
mode="S" associatedObjectId="72057594130202624">
<owner-list>
<owner id="processf245c8" mode="S" />
</owner-list>
<waiter-list>
<waiter id="processf66868" mode="X" requestType="wait" /
</waiter-list>
</keylock>
<keylock hobtid="72057594124500992" dbid="5"
objectname="DBName.dbo.Table" indexname="Pk_Table" id="lock1b437e400"
mode="X" associatedObjectId="72057594124500992">
<owner-list>
<owner id="processf66868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processf245c8" mode="S" requestType="wait" /
</waiter-list>
</keylock>
</resource-list>

Right now I added "with nolock" hint to one of the select proc hopeing
to solve this deadlock issue. But I am wondering that how I could
avoid this key usage sequence conflicts?

Thanks a lot for any help!

[mirthcyy]

Sorry one proc try to get X lock on both indexes while the other one
tries to get S lock.

[Uri Dimant]

Must read

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

Post by mirthcyy
hi guys,
I have two procedures: one for selecting data from a table and do some
calculations and the other for update or insert into the same table.
Pk_Table and one nonclustered: Ix_Table.
I've captured a few deadlocks on those two procedure executions. It
seems one proc gets x lock on the Pk_table key first and the other
proc gets the x lock on Ix_Table and then both proc tries to get s
lock on the other key. Then the deadlock happens. The below is part of
<resource-list>
<keylock hobtid="72057594130202624" dbid="5"
objectname="DBName.dbo.Table" indexname="Ix_Table" id="lock890c8300"
mode="S" associatedObjectId="72057594130202624">
<owner-list>
<owner id="processf245c8" mode="S" />
</owner-list>
<waiter-list>
<waiter id="processf66868" mode="X" requestType="wait" /
</waiter-list>
</keylock>
<keylock hobtid="72057594124500992" dbid="5"
objectname="DBName.dbo.Table" indexname="Pk_Table" id="lock1b437e400"
mode="X" associatedObjectId="72057594124500992">
<owner-list>
<owner id="processf66868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processf245c8" mode="S" requestType="wait" /
</waiter-list>
</keylock>
</resource-list>
Right now I added "with nolock" hint to one of the select proc hopeing
to solve this deadlock issue. But I am wondering that how I could
avoid this key usage sequence conflicts?
Thanks a lot for any help!

[mirthcyy]

good article. Thanks.