Dead Lock on stored procedure

Question

question

rag121 asked Oct 4, '18

Dead Lock on stored procedure

Hi

We have a dead lock scenario which I am trying to understand. please can any one explain

where the dead lock happening and how i can approach to resolve this issue.

2018-10-01 15:20:19.520	spid344	Transaction (Process ID 344) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2018-10-01 15:20:19.520	spid30s	deadlock-list
2018-10-01 15:20:19.520	spid30s	 deadlock victim=process843c8088
2018-10-01 15:20:19.520	spid30s	  process-list
2018-10-01 15:20:19.520	spid30s	   process id=process843c8088 taskpriority=0 logused=0 waitresource=PAGE: 14:1:71449 waittime=1104 ownerId=23945360503 transactionname=INSERT lasttranstarted=2018-10-01T15:20:16.870 XDES=0x842ff780 lockMode=S schedulerid=2 kpid=3056 status=suspended spid=344 sbid=0 ecid=0 priority=0 
trancount=0 lastbatchstarted=2018-10-01T15:20:16.870 lastbatchcompleted=2018-10-01T15:20:16.853 clientapp=.Net SqlClient Data Provider hostname=xxxxxxx hostpid=7464 loginname=xxxxxxx isolationlevel=read committed (2) xactid=23945360503 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2018-10-01 15:20:19.520	spid30s	    executionStack
2018-10-01 15:20:19.520	spid30s	     frame procname=Events.dbo.stored_procedure_name line=221 stmtstart=9476 stmtend=24978 sqlhandle=0x0300110018d2e62d9bac8500c6a800000100000000000000
2018-10-01 15:20:19.520	spid30s	INSERT INTO @ExportTempTable 
2018-10-01 15:20:19.520	spid30s	(
2018-10-01 15:20:19.520	spid30s	[External reference],
2018-10-01 15:20:19.520	spid30s	Forename,
2018-10-01 15:20:19.520	spid30s	Surname,
2018-10-01 15:20:19.520	spid30s	[Prefix Name],
2018-10-01 15:20:19.520	spid30s	[Address line 1],
2018-10-01 15:20:19.520	spid30s	[Address line 2],
2018-10-01 15:20:19.520	spid30s	[Post town],
2018-10-01 15:20:19.520	spid30s	[Post code],
2018-10-01 15:20:19.520	spid30s	[Field Value 10     
2018-10-01 15:20:19.520	spid30s	    inputbuf
2018-10-01 15:20:19.520	spid30s	Proc [Database Id = 17 Object Id = 770101784]    
2018-10-01 15:20:19.520	spid30s	   process id=process844d0bc8 taskpriority=0 logused=568 waitresource=OBJECT: 14:245575913:0  waittime=1687 ownerId=23945361156 transactionname=user_transaction lasttranstarted=2018-10-01T15:20:17.827 XDES=0x1cf49f970 lockMode=IX schedulerid=3 kpid=9208 status=suspended spid=254 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-10-01T15:20:17.830 lastbatchcompleted=2018-10-01T15:20:17.830 clientapp=Events hostname=Liveserver04 hostpid=12668 loginname=xxxxxxx isolationlevel=read committed (2) xactid=23945361156 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2018-10-01 15:20:19.520	spid30s	    executionStack
2018-10-01 15:20:19.520	spid30s	     frame procname=adhoc line=1 stmtstart=312 stmtend=1068 sqlhandle=0x02000000544b36361f76b12f1d8d85f84c6c9583f00dd0b9
2018-10-01 15:20:19.520	spid30s	INSERT [dbo].[eventForms]([FormCode], [Source], [BudgetDept], [CampaignCode], [FreeholdCode], [EventCode], [SubRegion], [Account], [AppealCode], [PurchaseDescription])
2018-10-01 15:20:19.520	spid30s	VALUES (@0, @1, @2, @3, @4, @5, @6, @7, NULL, NULL)     
2018-10-01 15:20:19.520	spid30s	     frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2018-10-01 15:20:19.520	spid30s	unknown     
2018-10-01 15:20:19.520	spid30s	    inputbuf
2018-10-01 15:20:19.520	spid30s	(@0 nvarchar(max) ,@1 nvarchar(max) ,@2 int,@3 nvarchar(max) ,@4 nvarchar(max) ,@5 nvarchar(max) ,@6 nvarchar(max) ,@7 nvarchar(max))INSERT [dbo].[eventForms]([FormCode], [Source], [BudgetDept], [CampaignCode], [FreeholdCode], [EventCode], [SubRegion], [Account], [AppealCode], [PurchaseDescription])
2018-10-01 15:20:19.520	spid30s	VALUES (@0, @1, @2, @3, @4, @5, @6, @7, NULL)
2018-10-01 15:20:19.520	spid30s	SELECT [Id]
2018-10-01 15:20:19.520	spid30s	FROM [dbo].[EventForms]
2018-10-01 15:20:19.520	spid30s	WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()    
2018-10-01 15:20:19.520	spid30s	  resource-list
2018-10-01 15:20:19.520	spid30s	   pagelock fileid=1 pageid=71449 dbid=14 objectname=Databasename.dbo.Addresses id=lock175221080 mode=IX associatedObjectId=72057594038779904
2018-10-01 15:20:19.520	spid30s	    owner-list
2018-10-01 15:20:19.520	spid30s	     owner id=process844d0bc8 mode=IX
2018-10-01 15:20:19.520	spid30s	    waiter-list
2018-10-01 15:20:19.520	spid30s	     waiter id=process843c8088 mode=S requestType=wait
2018-10-01 15:20:19.520	spid30s	   objectlock lockPartition=0 objid=245575913 subresource=FULL dbid=14 objectname=databasename.dbo.EventForms id=lock17bde2e00 mode=S associatedObjectId=245575913
2018-10-01 15:20:19.520	spid30s	    owner-list
2018-10-01 15:20:19.520	spid30s	     owner id=process843c8088 mode=S
2018-10-01 15:20:19.520	spid30s	    waiter-list
2018-10-01 15:20:19.520	spid30s	     waiter id=process844d0bc8 mode=IX requestType=wait

sql-server-2012 deadlock

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-10-04T09:51:51Z

Kev Riley answered Oct 4, '18

The deadlock is between 2 processes :

process843c8088 (seemingly named Events.dbo.stored_procedure_name) and from the snippet we can see that it is trying to insert data into a temporary table - let's call this process1

process844d0bc8 (adhoc sql) trying to insert data into EventForms table - call this process2

process1 holds a shared lock on EventForms table and is waiting for a shared lock on Addresses, however process2 is holding an exclusive lock on Addresses and is waiting to acquire an exclusive lock on EventForms. SQL has decided to kill process1 and allow process2 to continue.

How to fix? - not easy to tell as we can't see the full queries, or know about the schema.

Possible approaches are: adding non-clustered indexes to allow different resources to be used, or changing transaction isolation levels to avoid collisions.

The key to understanding how to fix this, is to understand how the scenario happens in the first place.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rag121 commented · Oct 04, 2018 at 10:13 AM

Thanks Kev Riley

I could see what's happening after you break down to process 1 and Process 2, but one thing is still not clear.

So let me explain what's happening

Process 1 - It's a stored procedure inserting in to @ExportTempTable. This query is retrieving some data from several tables including EventForms, Address tables etc by using inner joins.

Process 2 - As we can see this is another Insert statement with WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity() from a different process and has no reference to Address table. Should this be holding the table lock.

As you mentioned above, Process 2 is holding exclusive lock on Address table, but from my database schema, EventForms doesn't have any reference to Address table.

I already have a Clustered index on the Address table, so what should be my non-clustered index?

0 ·

Kev Riley ♦♦ commented · Oct 04, 2018 at 12:36 PM

There could be a few reasons why process2 has a lock on Addresses: maybe somewhere in process2 an explicit transaction has been started (either by SQL or application level), and an another query referenced Addresses. Also Check for the existence of triggers/constraints/computed columns, anything that might tie these things together.

There will be a reason why the lock is there.

As for the non-clustered indexes, it would really depend on what the query in process1 was doing and whether a suitable index could be defined. But for the first step, work out why the locks are happening.

0 ·

question