deadlock problem

I have an application that calls many stored procedures. They are
occassionally getting into a deadlock situation.
I have used DBCC TraceOn (-1,1204) to trace the problem and have had
moderate success. However, I have hit a particular deadlock that I just
don't understand and was hoping someone here may be able to help.
The two sps in question are Item_I and Item_DNPK.
The output from DBCC Trace (see below) tells me that SPID 60 (see red text)
has an exclusive (X) lock on the Item table index (KEY: 10:613577224:1 is a
clustered index XPKItem). SPID 60 is currently deadlocked at line 29 of
Item_I. Line 29 is the very first transaction in this sp.
The other client, SPID 56 (see green text) has a Range-Shared-Update lock on
the same resource as SPID 60 and is currently deadlocked at line 60 of
Item_DNPK. Line 60 is the very first meaningful transaction in this sp (not
counting declares and create operations on #temp tables).
The deadlock is, I think, occurring because SPID 60, which has an X lock on
the resource, is waiting for a Range-Insert-Null lock (see maroon text),
while SPID 56 is waiting for a Range-S-U lock on the same resource.
What I don't understand is why there is a deadlock in this case; with one
resource? Also, how can I prevent it, given that the lines in question are
right at the beginning of the transactions in the respective sps?
Any help would be very much appreciated
Adrian
Output of DBCC TraceOn (1204) for the deadlock described above::
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:1
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (100095e758e0)
CleanCnt:1 Mode: X Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42be3fe0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:60 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 60 ECID: 0 Statement Type: INSERT
Line #: 29
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_I;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368) Value:0x42bd7920 Cost:(0/0)
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:2
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (ffffffffffff)
CleanCnt:1 Mode: Range-S-U Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42bd85a0 Mode: Range-S-U Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 56 ECID: 0 Statement Type: INSERT
Line #: 60
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_DNPK;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-Insert-Null SPID:60 ECID:0 Ec:(0x4428D368) Value:0x42bd29e0
Cost:(0/214)
2006-02-21 14:25:06.21 spid4 Victim Resource Owner:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368)See if this helps
http://sql-server-performance.com/deadlocks.asp
Madhivanan|||Please post the queries.
ML
http://milambda.blogspot.com/|||Here are the two queries involved in the deadlck I am referring to:
Query 1 - sp Item_DNPK (I have left some of this stored proc out just
because it is very long. My deadlocking is happening on the Insert so I
don't think the rest of the sp (after this Insert) is relevant. If you think
it will help, I can post it as well):
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
BEGIN TRAN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
Query 2: Item_I:
CREATE PROC [dbo].[Item_I]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : Generated from schema stored procedure template 'SP_I'
-- Description : Generic identity based insert stored procedure for
table 'Item'
-- Returns : New Identity Key if successful or negative error
number
----
--
@.aItemTypeKey int ,
@.aParentKey int ,
@.aItemPartitionKey int ,
@.aOwnerKey int ,
@.aExternalKey sql_variant ,
@.aName nvarchar(256) ,
@.aDescription nvarchar(256) ,
@.aInternal varbinary(100) ,
@.aIsLeaf bit = 0,
@.aIsDeleted bit = 0,
@.aCreatedOn datetime ,
@.aModifiedOn datetime
AS
BEGIN
DECLARE @.Result int
SET NoCount ON
INSERT INTO [dbo].[Item]
(
ItemTypeKey,
ParentKey,
ItemPartitionKey,
OwnerKey,
ExternalKey,
Name,
Description,
Internal,
IsLeaf,
IsDeleted,
CreatedOn,
ModifiedOn
)
VALUES
(
@.aItemTypeKey,
@.aParentKey,
@.aItemPartitionKey,
@.aOwnerKey,
@.aExternalKey,
@.aName,
@.aDescription,
@.aInternal,
@.aIsLeaf,
@.aIsDeleted,
@.aCreatedOn,
@.aModifiedOn
)
IF (@.@.Error = 0)
BEGIN
SELECT @.Result = @.@.Identity
END
ELSE
BEGIN
SELECT @.Result = -@.@.Error
END
RETURN @.Result
END
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D0BAC95E-35B8-47A2-9F51-2BF174E5EF0F@.microsoft.com...
> Please post the queries.
>
> ML
> --
> http://milambda.blogspot.com/|||Comments inline:

> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
Repeatable read? Is there another reason for this isolation level? As far as
I see it you could just use defaults here (READ COMMITED).

> BEGIN TRAN
> CREATE TABLE #Temp (MimeElementKey int)
> -- primary key has to exist in order to prevent the subsequent deletes
> from hanging
> CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
> RecursionDepth int not null,
> PRIMARY KEY CLUSTERED
> (
> [ItemKey],
> [RecursionDepth]
> ))
> -- get the dependent items of the initial item being deleted
> INSERT INTO #Temp2
> SELECT ItemKey, ParentKey, @.RecursionDepth
> FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
The HOLDLOCK hint instructs the procedure not to release the lock until it
ends. As I see it you select a set of values here and store them in a local
temporary table, I guess you make a few changes, then update the values
appropriately or what? Could you do these in a single update statement? It
would really help if we could see the rest of the procedure here - especiall
y
the part where the explicit lock is acquired.
Also try adding the ROWLOCK hint.

> WHERE ParentKey = @.aItemKey
> END
The other procedure looks OK to me. It's a simple insert procedure, and has
little or no room for improvement. I, personally, prefer the INSERT...SELECT
syntax to INSERT...VALUES, but I've never heard that choosing either one
would affect locking.
ML
http://milambda.blogspot.com/|||Thanks for the reply.
I tried these two isolation levels (REPEATABLE READ and SERIALIZABLE), but
they don't seem to make a dramatic difference. I'm a little in the dark
ragarding this deadlock, so I must admit that I don't really know whether
READ REPEATABLE is better than READ COMMITTED or not.
The rest of this stored procuder involves several (7 or 8) different
delete/update queries which themselves were involved in earlier deadlocks.
It is for that reason I had started introducing these non-default isolation
levels. Again, though, I am largely driving by the seat of my pants, here;
no explicit reason for having done that.
The reason for the temp table is actually this: my schema is represented as
meta data within several tables and I need to delete a hierarchy of items
defined within this meta schema. To do this, I recurse the sp looking for
children of items at each level. I place the identifiers of all affected
items in the #temp table and then, as I come out of the recursion, I delete
and update various aspects of my schema to delete the children in a clean
and orderly fashion. The semantics of the sp may not make much sense to you
since the rest of the schema is not known to you. IF you have any questions,
though, I will be happy to try and work through them with you.
Thanks for your help in looking into this.
Here is the rest of the sp:
CREATE PROC [dbo].[Item_DNPK]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : APD
-- Description : Cascade primary key based delete stored procedure for
table 'Item'
-- Recursively calls into itself deleting all children of
the
-- item passed in as the parameter
-- Returns : Zero if successful or negative error number
-- Returns : and List of dependent items that were deleted during
the cascade
----
--
@.aItemKey int,
@.RecursionDepth int = 0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
DECLARE @.IsLocalTran bit
DECLARE @.Result int
-- This procedure is recursive; keep track of the depth of recursion
SELECT @.RecursionDepth = @.RecursionDepth + 1
IF (@.@.TranCount = 0)
BEGIN
SET @.IsLocalTran = 1
BEGIN TRAN
END
ELSE
BEGIN
SET @.IsLocalTran = 0
END
SET @.Result = IsNull(Object_Id('tempdb..#Temp'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp
END
-- A temporary table to hold the cascaded item Ids for later deletion
SET @.Result = IsNull(Object_Id('tempdb..#Temp2'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp2
END
-- for the first entry, create holding tables
IF (@.RecursionDepth = 1)
BEGIN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
ELSE
BEGIN
-- get the dependent items of the items at the previous recursion level
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i WITH (UPDLOCK,HOLDLOCK)
JOIN #Temp2 t WITH (NOLOCK)
ON i.ParentKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth-1
END
-- if the "get" of dependent children returned a non-zero number of items,
recurse to get their children
IF (SELECT @.@.ROWCOUNT) > 0
BEGIN
-- get these children to delete as well
EXEC Item_DNPK null, @.RecursionDepth
END
-- delete all items and clean up links as a result of these deletions
INSERT INTO #Temp
SELECT ME.[MimeElementKey]
FROM [dbo].[MimeElement] ME
JOIN [dbo].[Property] P
ON ME.[MimeElementKey] = P.[MimeElementKey]
WHERE P.[ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Property]
WHERE [ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[MimeElement]
FROM [dbo].[MimeElement] ME
JOIN #Temp WITH (NOLOCK)
ON ME.[MimeElementKey] = #Temp.[MimeElementKey]
DELETE FROM #Temp
END
IF (@.@.Error = 0)
BEGIN
/* DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[SourceItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[TargetItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth */
DELETE [dbo].[Link]
WHERE [SourceItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
OR [TargetItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Property]
SET [ReferenceTargetItem] = NULL
FROM [dbo].[Property] p
JOIN #Temp2 t WITH (NOLOCK) ON p.[ReferenceTargetItem] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Item]
SET [ParentKey] = NULL
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK) ON i.[ParentKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Item]
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK)
ON i.ItemKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth
END
IF (SELECT @.RecursionDepth) = 1
BEGIN
-- delete the primary item passed in as a parameter
IF (@.@.Error = 0)
BEGIN
-- put it in #Temp2 so we can return it in the "cascadedDeletedItems"
resultset
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i -- WITH (NOLOCK)
WHERE i.ItemKey = @.aItemKey
exec Item_D1PK @.aItemKey
END
if (@.IsLocalTran = 1)
BEGIN
IF (@.@.Error = 0)
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
END
SELECT @.Result = -@.@.Error
-- return the items affected by this query
SELECT distinct ItemKey FROM #Temp2
RETURN @.Result
END
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D228D99-FB06-4FB4-AEC2-24653942A8B4@.microsoft.com...
> Comments inline:
>
> Repeatable read? Is there another reason for this isolation level? As far
> as
> I see it you could just use defaults here (READ COMMITED).
>
> The HOLDLOCK hint instructs the procedure not to release the lock until it
> ends. As I see it you select a set of values here and store them in a
> local
> temporary table, I guess you make a few changes, then update the values
> appropriately or what? Could you do these in a single update statement? It
> would really help if we could see the rest of the procedure here -
> especially
> the part where the explicit lock is acquired.
> Also try adding the ROWLOCK hint.
>
> The other procedure looks OK to me. It's a simple insert procedure, and
> has
> little or no room for improvement. I, personally, prefer the
> INSERT...SELECT
> syntax to INSERT...VALUES, but I've never heard that choosing either one
> would affect locking.
>
> ML
> --
> http://milambda.blogspot.com/|||Before I read through the entire procedure - is there a tree or a hierarchy
involved in these deletes?
This could be optimized, take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Deletes require exclusive locks - you should make the delete procedure as
concise as possible - maybe even by building the list outside of a
transaction, and only beginning an explicit transaction just before the
actual delete statement. The delete will block other users anyway, so make i
t
as short as possible.
ML
http://milambda.blogspot.com/|||Yes. Typically an item in the item table has reference to it's parent which
is another item in the item table. The relationship from parent to children
is one to many. A row representing an item in the item table has references
to other tables that define things like properties and property types, etc.
Essentially, then, the hierarchy is to determine all the children of the
item whose id is passed in as a parameter to the sp, and recursively do that
for all those children until the end of the line is reached
I hope this answers your question.
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/|||That certainly is one of the function's purposes - to get a list of all
descendants in a hierarchy.
ML
http://milambda.blogspot.com/|||Thanks for the link and suggestion. I'll try putting the select outside of
the transaction and get back to you on my results
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/