locked
Deadlock while updating rows RRS feed

  • 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 ++++


    • Edited by GURSETHI Wednesday, January 13, 2016 4:33 AM corrected sequence
    Wednesday, January 13, 2016 4:02 AM

Answers