locked
Transaction (Process ID 61) was deadlocked on lock resources with another process RRS feed

  • Question

  • User-1410221107 posted

    I have two store procedures . Below one Selection stored proc is reads the data and update status to 1 and then selects the data.

     ALTER PROCEDURE [dbo].[SP_GetPolicyEventsOminibusbyEvents]
    (
    @PublishStatus int,
    @TopN int
    )
    AS 
    --SET TRANSACTION ISOLATION LEVEL Serializable
    
    BEGIN TRANSACTION POLICYEVENTS 
    
    
    Declare @eventId int
    Create table #tempEvents
    (
        [EventID] [int]  NOT NULL,
    )
    Insert into #tempEvents
    (
    [EventID] 
    )
    (Select top(@TopN) [EventID]   FROM [dbo].[PolicyEventsOminibus] WITH (UPDLOCK,READPAST) WHERE [PublishStatus] = @PublishStatus)
    
    update [PolicyEventsOminibus] set [PublishStatus]=1 where Eventid in (Select #tempEvents.eventid from #tempEvents)
    
    Select  [PolicyEventsOminibus].* FROM [dbo].[PolicyEventsOminibus] WITH (UPDLOCK,READPAST) where Eventid IN(Select #tempEvents.eventid from #tempEvents)
    
    drop TABLE #tempEvents
    commit TRANSACTION POLICYEVENTS

    After fetching the data we do some processing and then update the status to 2 using below stored proc.

         ALTER PROCEDURE [dbo].[Update_DMVPolicyEventsOminibus]
    (
    @Publishstatus int,
    @PrevPublishstatus int,
    @LastUpdatedTimeStamp varchar(100),
    @EventID int,
    @RowVersion rowversion  
    ) 
    AS
    --SET TRANSACTION ISOLATION LEVEL Serializable
    
    Begin Transaction
    
    
    Update PolicyEventsOminibus
    SET
    Publishstatus = @Publishstatus ,
    LastUpdatedTimeStamp  = @LastUpdatedTimeStamp
    where EventID = @EventID and [RowVersion] = @RowVersion and Publishstatus =@PrevPublishstatus
    Commit Transaction

    But without using isolation level Serialization I am getting the dead lock as "Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." when running multiple sessions or Instances

    I want to avoid Serialization in select stored procedure especially as it blocks the insertion. I have seen other related question. But those were not helpful.

    My understanding is if we apply READPAST and UPDLOCK the rows should be blocked for selection by other thread and should not get dead lock. Please correct me if I am wrong.

    I would appreciate if somebody help me to identity the alternative?.

    Friday, January 8, 2016 5:23 AM

All replies

  • User-219423983 posted

    Hi mohan089,

    >> My understanding is if we apply READPAST and UPDLOCK the rows should be blocked for selection by other thread and should not get dead lock. Please correct me if I am wrong.

    According to the link you could know that the “READPAST” specifies that the Database Engine not read rows that are locked by other transactions and the “UPDLOCK” specifies that update locks are to be taken and held until the transaction completes. So, I think if you using the “READPAST” would not help you return the right result to the current instance, because it just skips the “busy” records. For this issue demo, you could have a look at the “READPAST” section.

    https://msdn.microsoft.com/en-us/library/ms187373.aspx

    About how to handle the dead lock, you’d better have a look at the following article which provides a demo to show how to solve the dead lock.

    http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock

    For more things about the “Transaction (Process ID) was deadlocked” error, you’d better have a look at the following blog. In this blog, it provides some methods to troubleshoot the error and give some tips to avoid this issue.

    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

    Best Regards,

    Weibo Zhang

    Tuesday, January 12, 2016 8:29 AM