RE: Deadlock involving Identity Column

SSC Eights!

Points: 863

September 10, 2013 at 8:42 am

The deadlock info is below. Both spids are inserting via the same stored procedure. This is an audit type table so there are high inserts. There is another table that has a similar problem to a lesser degree but they are consistent where they are clustering on the identity column.

Unknown,waiter id=processc19e28 mode=IX requestType=convert

07/23/2013 10:59:28,spid21s,Unknown,waiter id=process3b9787a8 mode=IX requestType=convert

07/23/2013 10:59:28,spid21s,Unknown,waiter-list

07/23/2013 10:59:28,spid21s,Unknown,owner id=process3b9787a8 mode=S

07/23/2013 10:59:28,spid21s,Unknown,owner id=processc19e28 mode=S

07/23/2013 10:59:28,spid21s,Unknown,owner-list

07/23/2013 10:59:28,spid21s,Unknown,pagelock fileid=1 pageid=1662692 dbid=5 objectname=AMAC.dbo.AuditLog id=lock24d2c5c0 mode=S associatedObjectId=72057594504937472

07/23/2013 10:59:28,spid21s,Unknown,resource-list

07/23/2013 10:59:28,spid21s,Unknown,Proc [Database Id = 5 Object Id = 292912115] Stored procedure doing insert

07/23/2013 10:59:28,spid21s,Unknown,inputbuf

07/23/2013 10:59:28,spid21s,Unknown,( @intAuditLog_ID<c/> @intUserID<c/> @dtDateTime<c/> @chrIsSubscriber<c/> @chrSubscriber_AgencyID<c/> @vtxtChangesMade<c/>@vChrScreenName)

07/23/2013 10:59:28,spid21s,Unknown,VALUES

07/23/2013 10:59:28,spid21s,Unknown,AuditLog (AuditLog_ID<c/> User_ID<c/> DateTime<c/> IsSubscriber<c/> SubscriberAgency_ID<c/> ChangesMade<c/>ScreenName)

07/23/2013 10:59:28,spid21s,Unknown,INSERT INTO

07/23/2013 10:59:28,spid21s,Unknown,frame procname=AMAC.dbo.ap_AuditLog_i line=44 stmtstart=3020 stmtend=3528 sqlhandle=0x03000500f37b75119b788301fa9300000100000000000000

07/23/2013 10:59:28,spid21s,Unknown,executionStack

07/23/2013 10:59:28,spid21s,Unknown,process id=process3b9787a8 taskpriority=0 logused=676 waitresource=PAGE: 5:1:1662692 waittime=4000 ownerId=66749210653 transactionname=user_transaction lasttranstarted=2013-07-23T10:59:24.170 XDES=0x4b47c6d8 lockMode=IX schedulerid=7 kpid=1112 status=suspended spid=118 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-07-23T10:59:24.170 lastbatchcompleted=2013-07-23T10:59:24.170 clientapp=Internet Information Services hostname=WEB1 hostpid=5964 loginname=sa isolationlevel=serializable (4) xactid=66749210653 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128024

07/23/2013 10:59:28,spid21s,Unknown,Proc [Database Id = 5 Object Id = 292912115]

07/23/2013 10:59:28,spid21s,Unknown,inputbuf

07/23/2013 10:59:28,spid21s,Unknown,( @intAuditLog_ID<c/> @intUserID<c/> @dtDateTime<c/> @chrIsSubscriber<c/> @chrSubscriber_AgencyID<c/> @vtxtChangesMade<c/>@vChrScreenName)

07/23/2013 10:59:28,spid21s,Unknown,VALUES

07/23/2013 10:59:28,spid21s,Unknown,AuditLog (AuditLog_ID<c/> User_ID<c/> DateTime<c/> IsSubscriber<c/> SubscriberAgency_ID<c/> ChangesMade<c/>ScreenName)

07/23/2013 10:59:28,spid21s,Unknown,INSERT INTO

07/23/2013 10:59:28,spid21s,Unknown,frame procname=AMAC.dbo.ap_AuditLog_i line=44 stmtstart=3020 stmtend=3528 sqlhandle=0x03000500f37b75119b788301fa9300000100000000000000

07/23/2013 10:59:28,spid21s,Unknown,executionStack

07/23/2013 10:59:28,spid21s,Unknown,process id=processc19e28 taskpriority=0 logused=592 waitresource=PAGE: 5:1:1662692 waittime=3968 ownerId=66749209961 transactionname=user_transaction lasttranstarted=2013-07-23T10:59:24.110 XDES=0x17c78648 lockMode=IX schedulerid=5 kpid=6996 status=suspended spid=254 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-07-23T10:59:24.110 lastbatchcompleted=2013-07-23T10:59:24.110 clientapp=Microsoft® Windows® Operating System hostname=LICAPPS04 hostpid=8044 loginname=amacbusiness isolationlevel=serializable (4) xactid=66749209961 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128536

07/23/2013 10:59:28,spid21s,Unknown,process-list

07/23/2013 10:59:28,spid21s,Unknown,deadlock victim=processc19e28

07/23/2013 10:59:28,spid21s,Unknown,deadlock-list

07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x17C78648 Mode: IX SPID:254 BatchID:0 ECID:0 TaskProxy:(0x1BD48374) Value:0x1fb6a860 Cost:(0/592)

07/23/2013 10:59:28,spid4s,Unknown,Victim Resource Owner:

07/23/2013 10:59:28,spid4s,Unknown,

07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x4B47C6D8 Mode: IX SPID:118 BatchID:0 ECID:0 TaskProxy:(0x0C814374) Value:0x3e016160 Cost:(0/676)

07/23/2013 10:59:28,spid4s,Unknown,Requested By:

07/23/2013 10:59:28,spid4s,Unknown,Grant List 3:

07/23/2013 10:59:28,spid4s,Unknown,Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 292912115]

07/23/2013 10:59:28,spid4s,Unknown,SPID: 254 ECID: 0 Statement Type: INSERT Line #: 44

07/23/2013 10:59:28,spid4s,Unknown,Owner:0x2DEE1720 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:254 ECID:0 XactLockInfo: 0x17C7866C

07/23/2013 10:59:28,spid4s,Unknown,Grant List 2:

07/23/2013 10:59:28,spid4s,Unknown,PAGE: 5:1:1662692 CleanCnt:4 Mode:S Flags: 0x2

07/23/2013 10:59:28,spid4s,Unknown,Node:2

07/23/2013 10:59:28,spid4s,Unknown,

07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x17C78648 Mode: IX SPID:254 BatchID:0 ECID:0 TaskProxy:(0x1BD48374) Value:0x1fb6a860 Cost:(0/592)

07/23/2013 10:59:28,spid4s,Unknown,Requested By:

07/23/2013 10:59:28,spid4s,Unknown,Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 292912115]

07/23/2013 10:59:28,spid4s,Unknown,SPID: 118 ECID: 0 Statement Type: INSERT Line #: 44

07/23/2013 10:59:28,spid4s,Unknown,Owner:0x25696060 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:118 ECID:0 XactLockInfo: 0x4B47C6FC

07/23/2013 10:59:28,spid4s,Unknown,Grant List 3:

07/23/2013 10:59:28,spid4s,Unknown,Grant List 2:

07/23/2013 10:59:28,spid4s,Unknown,PAGE: 5:1:1662692 CleanCnt:4 Mode:S Flags: 0x2

07/23/2013 10:59:28,spid4s,Unknown,Node:1

07/23/2013 10:59:28,spid4s,Unknown,

07/23/2013 10:59:28,spid4s,Unknown,Wait-for graph

07/23/2013 10:59:28,spid4s,Unknown,Deadlock encountered .... Printing deadlock information