Deadlock while updating rows

Question

Hi All,

I am facing a problem where when we call one stored procedure which does a SELECT MAX (grab id) and then update a row in same table with that specific ID in where condition. This execution is going for a deadlock.

Procedure is called by 3 different SPIDs at a given time (with few ms difference)

Deadlock graph					<deadlock-list>
 <deadlock victim="processd094988">
  <process-list>
   <process id="processd094988" taskpriority="0" logused="0" waitresource="KEY: 29:72057596472524800 (ef8cdab7c965)" waittime="935" ownerId="6439726440" transactionname="UPDATE" lasttranstarted="2016-01-12T12:02:46.627" XDES="0x457097970" lockMode="U" schedulerid="55" kpid="9688" status="suspended" spid="660" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-12T12:02:46.440" lastbatchcompleted="2016-01-12T12:02:46.440" clientapp="SSIS-STG_LOAD_FACTSET-{3B5BB315-BF31-496C-B44A-922804A70783}MELW45LNZ1S.PRA_DI_POC_Staging_01" hostname="MELWDSPRA20" hostpid="14656" loginname="NT3GWM01\svc_praasys_prod" isolationlevel="read committed (2)" xactid="6439726440" currentdb="29" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="PRAStaging.PRAStaging.spu_Audit_OnEnd" line="42" stmtstart="2044" stmtend="2330" sqlhandle="0x03001d008d9acd65c7aa1a0186a300000100000000000000">
UPDATE [PRAStaging].[PRA_AUDIT_LOG]
			SET EndDateTime		= GETDATE()
			,	AuditLogStatusCode		= @StatusCode
			WHERE AuditLogId			= @LogID     </frame>
     <frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x01001d00db9b962b60570da6030000000000000000000000">
exec [PRAStaging].[spu_Audit_OnEnd]

@PackageStep=@P1,
@StatusCode =1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 varchar(23))exec [PRAStaging].[spu_Audit_OnEnd]

@PackageStep=@P1,
@StatusCode =1    </inputbuf>
   </process>
   <process id="process6076988" taskpriority="0" logused="0" waitresource="KEY: 29:72057596472524800 (c523ad4954f6)" waittime="935" ownerId="6439726474" transactionname="UPDATE" lasttranstarted="2016-01-12T12:02:46.627" XDES="0xac5d943b0" lockMode="U" schedulerid="32" kpid="11200" status="suspended" spid="653" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-12T12:02:46.440" lastbatchcompleted="2016-01-12T12:02:46.440" clientapp="SSIS-STG_LOAD_FACTSET-{3B5BB315-BF31-496C-B44A-922804A70783}MELW45LNZ1S.PRA_DI_POC_Staging_01" hostname="MELWDSPRA20" hostpid="9848" loginname="NT3GWM01\svc_praasys_prod" isolationlevel="read committed (2)" xactid="6439726474" currentdb="29" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="PRAStaging.PRAStaging.spu_Audit_OnEnd" line="42" stmtstart="2044" stmtend="2330" sqlhandle="0x03001d008d9acd65c7aa1a0186a300000100000000000000">
UPDATE [PRAStaging].[PRA_AUDIT_LOG]
			SET EndDateTime		= GETDATE()
			,	AuditLogStatusCode		= @StatusCode
			WHERE AuditLogId			= @LogID     </frame>
     <frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x01001d00db9b962b60570da6030000000000000000000000">
exec [PRAStaging].[spu_Audit_OnEnd]

@PackageStep=@P1,
@StatusCode =1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 varchar(23))exec [PRAStaging].[spu_Audit_OnEnd]

@PackageStep=@P1,
@StatusCode =1    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057596472524800" dbid="29" objectname="PRAStaging.PRAStaging.PRA_AUDIT_LOG" indexname="PK_STG_AUDIT_LOG_1" id="lock41a0e4880" mode="U" associatedObjectId="72057596472524800">
    <owner-list>
     <owner id="process6076988" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processd094988" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057596472524800" dbid="29" objectname="PRAStaging.PRAStaging.PRA_AUDIT_LOG" indexname="PK_STG_AUDIT_LOG_1" id="lock41a0e5200" mode="U" associatedObjectId="72057596472524800">
    <owner-list>
     <owner id="processd094988" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6076988" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
					sa			25	2016-01-12 12:02:47.563				

Table Structure

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PRA_AUDIT_LOG](
	[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL,
	[PackageExecutionId] [varchar](200) NOT NULL,
	[PackageStep] [varchar](200) NOT NULL,
	[PackageName] [varchar](200) NOT NULL,
	[RunBy] [varchar](50) NOT NULL,
	[StartDateTime] [datetime] NOT NULL,
	[EndDateTime] [datetime] NULL,
	[AuditLogStatusCode] [int] NOT NULL,
 CONSTRAINT [PK_STG_AUDIT_LOG_1] PRIMARY KEY CLUSTERED 
(
	[AuditLogId] ASC,
	[PackageExecutionId] ASC,
	[PackageStep] ASC,
	[AuditLogStatusCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Table Has 4 Indexes

CREATE NONCLUSTERED INDEX [IX_PackageExecutionId_PackageStep] ON [dbo].[PRA_AUDIT_LOG] 
(
	[PackageExecutionId] ASC,
	[PackageStep] ASC
)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





CREATE NONCLUSTERED INDEX [IX_PackageStep] ON [dbo].[PRA_AUDIT_LOG] 
(
	[PackageStep] ASC
)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





CREATE NONCLUSTERED INDEX [IX_PRA_AUDIT_LOG_PACKAGENAME_PACKAGEXECUTIONID] ON [dbo].[PRA_AUDIT_LOG] 
(
	[PackageName] ASC,
	[PackageExecutionId] ASC
)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




ALTER TABLE [dbo].[PRA_AUDIT_LOG] ADD  CONSTRAINT [PK_STG_AUDIT_LOG_1] PRIMARY KEY CLUSTERED 
(
	[AuditLogId] ASC,
	[PackageExecutionId] ASC,
	[PackageStep] ASC,
	[AuditLogStatusCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

Stored Procedure

*[PRAStaging].[spu_Audit_OnEnd]
*   @PackageStep='1-JOB PARENT',
*   @StatusCode =1   


CREATE PROCEDURE [dbo].[spu_Audit_OnEnd] 
 
 @PackageStep as varchar(100) = null,
 @StatusCode  as int=null
 
 with execute as caller
AS

  DECLARE @logID as int
  
  		BEGIN
  		
  		SELECT @LogID=MAX(AuditLogId) FROM  dbo.[PRA_AUDIT_LOG]
  		 
  		WHERE PackageStep=@PackageStep
  				
  		GROUP BY PackageStep
  		
		
		-- UPDATE Audit Header Table
		-- Set end time to now and statuscode to 1 (success)
			UPDATE [dbo].[PRA_AUDIT_LOG]
			SET EndDateTime		= GETDATE()
			,	AuditLogStatusCode		= @StatusCode
			WHERE AuditLogId			= @LogID
		END



;




GO

My thought is that when first SPID runs, SELECT takes a IS - S  followed by IU - U locks by UPDATE (in sequence) and in between when other SPIDs comes they get blocked.

Need expert views on this.

---

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++