Deadlock that I cannot figure out.

Question

Post reply

Deadlock that I cannot figure out.

Ronnie_Doggart

SSC Veteran

Points: 299
More actions
November 21, 2012 at 2:16 pm

#151122

Hi,
I have a deadlock occurring and cannot figure it out, it involves an update and a delete of a row.
Here is the deadlock graph:
11/21/2012 15:17:33,spid15s,Unknown,waiter id=process8f8898 mode=U requestType=wait
11/21/2012 15:17:33,spid15s,Unknown,waiter-list
11/21/2012 15:17:33,spid15s,Unknown,owner id=process8f8b68 mode=X
11/21/2012 15:17:33,spid15s,Unknown,owner-list
11/21/2012 15:17:33,spid15s,Unknown,keylock hobtid=320775158497280 dbid=7 objectname=frontline.dbo.LGNCC_PUBLISHEDEVENT indexname=PK__LGNCC_PUBLISHEDE__24B26D99 id=lock191c3100 mode=X associatedObjectId=320775158497280
11/21/2012 15:17:33,spid15s,Unknown,waiter id=process8f8b68 mode=X requestType=wait
11/21/2012 15:17:33,spid15s,Unknown,waiter-list
11/21/2012 15:17:33,spid15s,Unknown,owner id=process8f8898 mode=U
11/21/2012 15:17:33,spid15s,Unknown,owner-list
11/21/2012 15:17:33,spid15s,Unknown,keylock hobtid=72057594237485056 dbid=7 objectname=frontline.dbo.LGNCC_PUBLISHEDEVENT indexname=LGNCC_PUBLISHEDEVENT_IDX1 id=lock1917aa40 mode=U associatedObjectId=72057594237485056
11/21/2012 15:17:33,spid15s,Unknown,resource-list
11/21/2012 15:17:33,spid15s,Unknown,(@P0 bigint)EXEC LGNCC_EVP_DeletePublishedEvent @P0
11/21/2012 15:17:33,spid15s,Unknown,inputbuf
11/21/2012 15:17:33,spid15s,Unknown,unknown
11/21/2012 15:17:33,spid15s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
11/21/2012 15:17:33,spid15s,Unknown,EXEC LGNCC_EVP_DeletePublishedEvent @P0
11/21/2012 15:17:33,spid15s,Unknown,frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x01000700d3620006d09cab30000000000000000000000000
11/21/2012 15:17:33,spid15s,Unknown,WHERE ID = @aID
11/21/2012 15:17:33,spid15s,Unknown,DELETE FROM LGNCC_PUBLISHEDEVENT
11/21/2012 15:17:33,spid15s,Unknown,frame procname=frontline.dbo.LGNCC_EVP_DeletePublishedEvent line=8 stmtstart=180 stmtend=286 sqlhandle=0x03000700b1e28758fdbeec00c99d00000100000000000000
11/21/2012 15:17:33,spid15s,Unknown,executionStack
11/21/2012 15:17:33,spid15s,Unknown,process id=process8f8b68 taskpriority=0 logused=212 waitresource=KEY: 7:72057594237485056 (1f01f584ce7b) waittime=3640 ownerId=186486618 transactionname=DELETE lasttranstarted=2012-11-21T15:17:29.847 XDES=0x2ec746e0 lockMode=X schedulerid=2 kpid=1896 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2012-11-21T15:17:29.847 lastbatchcompleted=2012-11-21T15:17:29.843 hostname=perappph11 hostpid=0 loginname=frontline isolationlevel=read committed (2) xactid=186486618 currentdb=7 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
11/21/2012 15:17:33,spid15s,Unknown,(@P0 nvarchar(4000)<c/>@P1 int<c/>@P2 int<c/>@P3 int<c/>@P4 int<c/>@P5 int<c/>@P6 nvarchar(4000))EXEC LGNCC_EVP_UpdateStatus @P0<c/> @P1<c/> @P2<c/> @P3<c/> @P4<c/> @P5<c/> @P6
11/21/2012 15:17:33,spid15s,Unknown,inputbuf
11/21/2012 15:17:33,spid15s,Unknown,unknown
11/21/2012 15:17:33,spid15s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
11/21/2012 15:17:33,spid15s,Unknown,EXEC LGNCC_EVP_UpdateStatus @P0<c/> @P1<c/> @P2<c/> @P3<c/> @P4<c/> @P5<c/> @P6
11/21/2012 15:17:33,spid15s,Unknown,frame procname=adhoc line=1 stmtstart=158 sqlhandle=0x01000700a6c8801ff8e88d30000000000000000000000000
11/21/2012 15:17:33,spid15s,Unknown,AND Status = @aOldStatus;
11/21/2012 15:17:33,spid15s,Unknown,AND Operation = @aOperation
11/21/2012 15:17:33,spid15s,Unknown,AND EventType = @aEventType
11/21/2012 15:17:33,spid15s,Unknown,WHERE ObjectID = @aObjectID
11/21/2012 15:17:33,spid15s,Unknown,LastModifiedBy = @aModifiedBy<c/> LastModifiedDate = getDate()
11/21/2012 15:17:33,spid15s,Unknown,UPDATE LGNCC_PUBLISHEDEVENT SET Status = @aNewStatus<c/> Attempts = @aAttempts<c/>
11/21/2012 15:17:33,spid15s,Unknown,frame procname=frontline.dbo.LGNCC_EVP_UpdateStatus line=14 stmtstart=494 stmtend=1014 sqlhandle=0x030007003f9a9f56ebbeec00c99d00000100000000000000
11/21/2012 15:17:33,spid15s,Unknown,executionStack
11/21/2012 15:17:33,spid15s,Unknown,process id=process8f8898 taskpriority=0 logused=0 waitresource=KEY: 7:320775158497280 (8500e7b87d33) waittime=3609 ownerId=186486616 transactionname=UPDATE lasttranstarted=2012-11-21T15:17:29.847 XDES=0x2ec64c90 lockMode=U schedulerid=2 kpid=7120 status=suspended spid=83 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2012-11-21T15:17:29.847 lastbatchcompleted=2012-11-21T15:17:29.847 hostname=perappph11 hostpid=0 loginname=frontline isolationlevel=read committed (2) xactid=186486616 currentdb=7 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
11/21/2012 15:17:33,spid15s,Unknown,process-list
11/21/2012 15:17:33,spid15s,Unknown,deadlock victim=process8f8898
11/21/2012 15:17:33,spid15s,Unknown,deadlock-list
The table definiition is:
CREATE TABLE [dbo].[LGNCC_PUBLISHEDEVENT](
[ID] [numeric](18, 0) NOT NULL,
[ObjectID] [varchar](255) NOT NULL,
[EventType] [numeric](2, 0) NOT NULL,
[Operation] [numeric](2, 0) NOT NULL,
[Status] [numeric](1, 0) NOT NULL,
[Attempts] [numeric](3, 0) NOT NULL,
[CreatedBy] [varchar](100) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastModifiedBy] [varchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
The indexes are:
CREATE NONCLUSTERED INDEX [LGNCC_PUBLISHEDEVENT_IDX1] ON [dbo].[LGNCC_PUBLISHEDEVENT]
(
[ObjectID] ASC,
[EventType] ASC,
[Operation] ASC,
[Status] ASC
)
Any advice appreciated.
Thanks
Ronnie

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

What do the 2 stored procedures listed in the deadlock graph look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ronnie_Doggart SSC Veteran Points: 299 More actions · Answer 2

Hi,

here is the code for the two procedures, they are quite simple.

CREATE PROCEDURE [dbo].[LGNCC_EVP_UpdateStatus]

(

@aObjectID VARCHAR(255),

@aEventType NUMERIC(2),

@aOperation NUMERIC(2),

@aOldStatus NUMERIC(1),

@aNewStatus NUMERIC(1),

@aAttempts NUMERIC(3),

@aModifiedBy VARCHAR(100)

)

AS

BEGIN

UPDATE LGNCC_PUBLISHEDEVENT SET Status = @aNewStatus, Attempts = @aAttempts,

LastModifiedBy = @aModifiedBy, LastModifiedDate = getDate()

WHERE ObjectID = @aObjectID

AND EventType = @aEventType

AND Operation = @aOperation

AND Status = @aOldStatus;

END

CREATE PROCEDURE [dbo].[LGNCC_EVP_DeletePublishedEvent]

(

@aID NUMERIC(18)

)

AS

BEGIN

DELETE FROM LGNCC_PUBLISHEDEVENT

WHERE ID = @aID

END

Thanks

Ronnie

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

They are simple, indeed and, by themselves, I don't see them causing the deadlock. Are they "wrapped" in other procedures that use explicit transactions?

Also, if the [ObjectID] ASC, [EventType] ASC, [Operation] ASC, [Status] ASC columns that form the nonclustered index form unique combinations throughout the table, consider making that index a UNIQUE index. There is a performance benefit in doing so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michaela SSCertifiable Points: 5649 More actions · Answer 4

If you take a look over the query plans of either stored procedures then you will see that Eager Spool operator is used by the query optimiser in some cases, based on query selectivity. Eager Spool is a blocking operator, hence deadlocks or lengthy blockings may occur. Consider removing the non-clustered index or change the stored procedures to SELECT INTO a temp table and then update/delete from it respectively.

Ronnie_Doggart SSC Veteran Points: 299 More actions · Answer 5

Hi,

I checked the traces I have, and the two statements are the only ones listed with those transactionid's so I assume there are no other statements within the transactions.

Would this assumption be correct.

This deadlock occurs quite frequently, so I have a good few traces with the deadlock showing, although it is not every time the combination of statements is run.

The only other comment I have been given is that the deadlock did not occur when the server was an actual physical server, but since it has been virtualized it has appeared.

Thanks

Ronnie