April 16, 2012 at 11:02 am
Hello,
I was wondering if someone might be able to give me a hand deciphering the attached deadlock XML report. Due to upload restrictions I have updated .xml to .txt.
What I belive is happening is
-- Owner processf262748 has an update (U) lock on Table1.PK_Table1 and is waiting for a update (U) lock on Table1.PK_Table1
-- Victim process60594c8 has an exclusive (X) lock on Table1.PK_Table1 and waiting for a update (U) lock on Table1.PK_Table1
hobtid="403666705907712" = PK_Table1
The code causing the issue is
SET NOCOUNT ON;
DECLARE @rc int, @trancount int, @rowcount int
SELECT @trancount = @@TRANCOUNT
IF @trancount = 0
BEGIN TRAN
DECLARE @Table1Handle TABLE (
Table1Id bigint
)
INSERT @Table1Handle
SELECT Table1Id
FROM dbo.Table1 WITH (NOLOCK)
WHERE Table1Id = @Table1Id
UNION ALL
SELECT Table1Id
FROM dbo.Table1 WITH (NOLOCK)
WHERE ParentTable1Id = @Table1Id
AND EndDateTime IS NULL
SELECT @rc = @@ERROR, @rowcount = @@ROWCOUNT
IF @rc <> 0
BEGIN
IF @trancount = 0
ROLLBACK TRAN
RETURN 99999
END
IF @rowcount = 0
GOTO endBatchHandler
SELECT @rowcount = 1
FROM dbo.Table1 spm WITH (UPDLOCK, INDEX(PK_Table1))
INNER JOIN
@Table1Handle t
ON spm.Table1Id = t.Table1Id
IF @@ERROR <> 0
BEGIN
IF @trancount = 0
ROLLBACK TRAN
RETURN 99999
END
UPDATE spm
SET EndDateTime = GetUTCDate(),
info = CASE WHEN spm.Table1Id = @Table1Id AND @info IS NOT NULL
FROM dbo.Table1 spm
INNER JOIN
@Table1Handle t
ON spm.Table1Id = t.Table1Id
with line 82 being the UPDATE spm part.
I assume it has a lot to do with the locking hint (UPDLOCK, INDEX(PK_Table1)).
dbo.Table1 has 3 indexes
index_nameindex_descriptionindex_keys
IX_Table1_1nonclustered located on PRIMARY
IX_Table1_2nonclustered, stats no recompute located on PRIMARY
PK_Table1clustered, unique, primary key located on PRIMARY
Many thanks
April 16, 2012 at 3:33 pm
can you post the schema for the table and all indexes.
also, can you get the execution plan for the problematic update statement?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply