April 15, 2014 at 10:40 am
Greetings,
Chasing a deadlock that has me perplexed.
Here is the errorlog output of a single deadlock with trace flags 1222 and 1204;
2014-04-14 16:51:54.50 spid6s Deadlock encountered .... Printing deadlock information
2014-04-14 16:51:54.50 spid6s Wait-for graph
2014-04-14 16:51:54.50 spid6s
2014-04-14 16:51:54.50 spid6s Node:1
2014-04-14 16:51:54.50 spid6s KEY: 8:72057598361665536 (01006ff789ae) CleanCnt:3 Mode:U Flags: 0x0
2014-04-14 16:51:54.50 spid6s Grant List 2:
2014-04-14 16:51:54.50 spid6s Owner:0x0000000A318A9300 Mode: U Flg:0x0 Ref:1 Life:02000000 SPID:136 ECID:0 XactLockInfo: 0x0000000B10A02E58
2014-04-14 16:51:54.50 spid6s SPID: 136 ECID: 0 Statement Type: SELECT Line #: 1
2014-04-14 16:51:54.50 spid6s Input Buf: Language Event: (@P1 numeric(28))select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid6s Requested By:
2014-04-14 16:51:54.50 spid6s ResType:LockOwner Stype:'OR'Xdes:0x00000000DE630A90 Mode: U SPID:146 BatchID:1 ECID:0 TaskProxy:(0x0000000253EE6598) Value:0xd8d51140 Cost:(0/0)
2014-04-14 16:51:54.50 spid6s
2014-04-14 16:51:54.50 spid6s Node:2
2014-04-14 16:51:54.50 spid6s KEY: 8:72057597754343424 (2301f08baa2a) CleanCnt:2 Mode:U Flags: 0x0
2014-04-14 16:51:54.50 spid6s Grant List 1:
2014-04-14 16:51:54.50 spid6s Owner:0x00000001E35472C0 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:146 ECID:0 XactLockInfo: 0x00000000DE630AC8
2014-04-14 16:51:54.50 spid6s SPID: 146 ECID: 0 Statement Type: SELECT Line #: 1
2014-04-14 16:51:54.50 spid6s Input Buf: Language Event: (@P1 numeric(28))select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid6s Requested By:
2014-04-14 16:51:54.50 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000B10A02E20 Mode: U SPID:136 BatchID:1 ECID:0 TaskProxy:(0x00000004C7D66598) Value:0x54b5b580 Cost:(0/0)
2014-04-14 16:51:54.50 spid6s
2014-04-14 16:51:54.50 spid6s Victim Resource Owner:
2014-04-14 16:51:54.50 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000B10A02E20 Mode: U SPID:136 BatchID:1 ECID:0 TaskProxy:(0x00000004C7D66598) Value:0x54b5b580 Cost:(0/0)
2014-04-14 16:51:54.50 spid18s deadlock-list
2014-04-14 16:51:54.50 spid18s deadlock victim=process9aa56d8
2014-04-14 16:51:54.50 spid18s process-list
2014-04-14 16:51:54.50 spid18s process id=processfc1048 taskpriority=0 logused=0 waitresource=KEY: 8:72057598361665536 (01006ff789ae) waittime=1326 ownerId=3883121205 transactionname=user_transaction lasttranstarted=2014-04-14T16:51:53.180 XDES=0xde630a90 lockMode=U schedulerid=5 kpid=9508 status=suspended spid=146 sbid=1 ecid=0 priority=0 transcount=1 lastbatchstarted=2014-04-14T16:51:53.183 lastbatchcompleted=2014-04-14T16:51:53.180 clientapp=SQLFlex - SN:25242400 - WtsSid:5 hostname=\\Hhs-ana-02\analive$\SC\APP\SC.EXE hostpid=7748 loginname=analive isolationlevel=read uncommitted (1) xactid=3883121205 currentdb=8 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-04-14 16:51:54.50 spid18s executionStack
2014-04-14 16:51:54.50 spid18s frame procname=adhoc line=1 stmtstart=34 sqlhandle=0x02000000aab1a935dabd70ce72cf1ad6994d2fe6bcc16eab
2014-04-14 16:51:54.50 spid18s select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid18s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-04-14 16:51:54.50 spid18s unknown
2014-04-14 16:51:54.50 spid18s inputbuf
2014-04-14 16:51:54.50 spid18s (@P1 numeric(28))select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid18s process id=process9aa56d8 taskpriority=0 logused=0 waitresource=KEY: 8:72057597754343424 (2301f08baa2a) waittime=1326 ownerId=3883121200 transactionname=user_transaction lasttranstarted=2014-04-14T16:51:53.177 XDES=0xb10a02e20 lockMode=U schedulerid=10 kpid=11332 status=suspended spid=136 sbid=1 ecid=0 priority=0 transcount=1 lastbatchstarted=2014-04-14T16:51:53.180 lastbatchcompleted=2014-04-14T16:51:53.180 clientapp=SQLFlex - SN:25242400 - WtsSid:5 hostname=\\Hhs-ana-02\analive$\CH\APP\CH.EXE hostpid=6928 loginname=analive isolationlevel=read uncommitted (1) xactid=3883121200 currentdb=8 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2014-04-14 16:51:54.50 spid18s executionStack
2014-04-14 16:51:54.50 spid18s frame procname=adhoc line=1 stmtstart=34 sqlhandle=0x02000000aab1a935dabd70ce72cf1ad6994d2fe6bcc16eab
2014-04-14 16:51:54.50 spid18s select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid18s inputbuf
2014-04-14 16:51:54.50 spid18s (@P1 numeric(28))select "RECNUM", "FILE_NAME", "NEXT_ID", "NEXT_ID2", "NEXT_ID3" from "ANALIVE"."dbo"."AZNEXTID" WITH (ROWLOCK UPDLOCK) where ("RECNUM" = @P1) order by "RECNUM" option (fast 1)
2014-04-14 16:51:54.50 spid18s resource-list
2014-04-14 16:51:54.50 spid18s keylock hobtid=72057597754343424 dbid=8 objectname=ANALIVE.dbo.AZNEXTID indexname=AZNEXTID_INDEX01 id=lock1ff761780 mode=U associatedObjectId=72057597754343424
2014-04-14 16:51:54.50 spid18s owner-list
2014-04-14 16:51:54.50 spid18s owner id=processfc1048 mode=U
2014-04-14 16:51:54.50 spid18s waiter-list
2014-04-14 16:51:54.50 spid18s waiter id=process9aa56d8 mode=U requestType=wait
2014-04-14 16:51:54.50 spid18s keylock hobtid=72057598361665536 dbid=8 objectname=ANALIVE.dbo.AZNEXTID indexname=AZNEXTID_INDEX00 id=lock6a7d05680 mode=U associatedObjectId=72057598361665536
2014-04-14 16:51:54.50 spid18s owner-list
2014-04-14 16:51:54.50 spid18s owner id=process9aa56d8 mode=U
2014-04-14 16:51:54.50 spid18s waiter-list
2014-04-14 16:51:54.50 spid18s waiter id=processfc1048 mode=U requestType=wait
If I understand this correctly, this appears to be a keylock deadlock on table AZNEXTID's indexes; AZNEXTID_INDEX01 (clustered) and AZNEXTID_INDEX00 (non-clustered). Per several articles on the net
I tried to fix this by creating a covering index;
Here is the current DDL with my comments;
USE [ANALIVE]
GO
/****** Object: Table [dbo].[AZNEXTID] Script Date: 04/14/2014 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AZNEXTID](
[RECNUM] [decimal](28, 0) IDENTITY(1,1) NOT NULL,
[FILE_NAME] [varchar](8) NOT NULL DEFAULT (' '),
[NEXT_ID] [decimal](10, 0) NULL,
[NEXT_ID2] [decimal](10, 0) NULL,
[NEXT_ID3] [decimal](10, 0) NULL,
CONSTRAINT [AZNEXTID_INDEX01] PRIMARY KEY CLUSTERED
(
[FILE_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [AZNEXTID_INDEX00] Script Date: 04/14/2014 17:48:15 ******/
/*****************************************************************************************/
/* I've added FILE_NAME and NEXT_ID as include columns to try to cover the index */
/*****************************************************************************************/
CREATE UNIQUE NONCLUSTERED INDEX [AZNEXTID_INDEX00] ON [dbo].[AZNEXTID]
(
[RECNUM] ASC
)
INCLUDE ( [FILE_NAME],
[NEXT_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [AZNEXTID_INDEX02] Script Date: 04/14/2014 17:48:15 ******/
/*****************************************************************************************/
/* I've added RECNUMand NEXT_ID as include columns to try to cover the index */
/*****************************************************************************************/
CREATE UNIQUE NONCLUSTERED INDEX [AZNEXTID_INDEX02] ON [dbo].[AZNEXTID]
(
[FILE_NAME] ASC
)
INCLUDE ( [RECNUM],
[NEXT_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Why doesn't AZNEXTID_INDEX00 prevent the update from needing to reference INDEX01 with the covering columns?
Any and all help greatly appreciated,
Kurt
May 12, 2014 at 1:16 pm
Anyone have any ideas on this issue?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply