Prev: How do I get scalar value of a child stored procedure?
Next: parse uri
From: mirthcyy on 1 Mar 2010 14:00
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!

From: mirthcyy on 1 Mar 2010 14:03
Sorry one proc try to get X lock on both indexes while the other one
tries to get S lock.

From: Uri Dimant on 2 Mar 2010 03:15
Must read

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




"mirthcyy" <mirthcyy(a)gmail.com> wrote in message
news:dbf19bf5-4aeb-4509-88a7-e21dc6bbe613(a)z35g2000yqd.googlegroups.com...
> 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!
>


From: mirthcyy on 2 Mar 2010 10:20
good article. Thanks.
 | 
Pages: 1
Prev: How do I get scalar value of a child stored procedure?
Next: parse uri