Message Queue stored procedure getting Deadlocked.

  • We have a MessageQueue table that gets records populated into it. Three different application servers have services that come to pick records from this table in order to process them. The three servers call a stored procedure GetMessageQueue passing in the server from where the call is being made and also the number of records it wants to pick in order to process them. The proc updates the status and populates the processdate so that the next call from a second server or the same one does not pick up the same records. Being done as we are not deleting the processed records from the table. Have done the selecting of the records in an inner loop and made the whole update and select into a single transaction so we don't need begin/commit transaction logic. Unfortunately we are seeing many deadlocks. Looking at the details of the deadlocks seems when the proc is called from two servers there is a deadlock. Would it be ok to put a with (nolock) hint in the inner select or is that not the preferred way to do this.

    CREATE PROCEDURE [dbo].[GetMessageQueue]
    @NumMessages int,
    @Server varchar(100) = null
    AS
    BEGIN
     set nocount on;
    declare @Returntempmsgs table
     (
      MessageQueueId int,
      TypeId int,
      [Data] varchar(max),
      StatusTypeId int
     )
    update e
     set  StatusTypeId = 1,[ProcessStartDate] = getdate(),[Server] = @Server
     output inserted.MessageQueueId, inserted.TypeId, inserted.[Data], inserted.StatusTypeId
     into @Returntempmsgs  
     from MessageQueue e
     where MessageQueueId in (
     select top (@NumMessages)  
                       MessageQueueId
       from  MessageQueue 
       where  ProcessStartDate is null
       order by MessageQueueId asc
     )
     select * from @Returntempmsgs 
    END

    Here is part of the Extended events description:
    </process>
    </process-list>
    <resource-list>
    <keylock hobtid="72057594043236352" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="PK_MessageQueue" id="lockccdf88fb80" mode="X" associatedObjectId="72057594043236352">
    <owner-list>
    <owner id="processccd2e7b848" mode="X" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd4d344e8" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
    <keylock hobtid="72057594046840832" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="ix_MessageQueue_ProcessStartDate" id="lockcc577f0980" mode="S" associatedObjectId="72057594046840832">
    <owner-list>
    <owner id="processccd4d344e8" mode="S" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd2e7b848" mode="X" requestType="wait" />
    </waiter-list>
    </keylock>
    </resource-list>
    </deadlock>

    Thanks,
    BVip

  • Bharatvip - Thursday, February 22, 2018 6:09 PM

    We have a MessageQueue table that gets records populated into it. Three different application servers have services that come to pick records from this table in order to process them. The three servers call a stored procedure GetMessageQueue passing in the server from where the call is being made and also the number of records it wants to pick in order to process them. The proc updates the status and populates the processdate so that the next call from a second server or the same one does not pick up the same records. Being done as we are not deleting the processed records from the table. Have done the selecting of the records in an inner loop and made the whole update and select into a single transaction so we don't need begin/commit transaction logic. Unfortunately we are seeing many deadlocks. Looking at the details of the deadlocks seems when the proc is called from two servers there is a deadlock. Would it be ok to put a with (nolock) hint in the inner select or is that not the preferred way to do this.

    CREATE PROCEDURE [dbo].[GetMessageQueue]
    @NumMessages int,
    @Server varchar(100) = null
    AS
    BEGIN
     set nocount on;
    declare @Returntempmsgs table
     (
      MessageQueueId int,
      TypeId int,
      [Data] varchar(max),
      StatusTypeId int
     )
    update e
     set  StatusTypeId = 1,[ProcessStartDate] = getdate(),[Server] = @Server
     output inserted.MessageQueueId, inserted.TypeId, inserted.[Data], inserted.StatusTypeId
     into @Returntempmsgs  
     from MessageQueue e
     where MessageQueueId in (
     select top (@NumMessages)  
                       MessageQueueId
       from  MessageQueue 
       where  ProcessStartDate is null
       order by MessageQueueId asc
     )
     select * from @Returntempmsgs 
    END

    Here is part of the Extended events description:
    </process>
    </process-list>
    <resource-list>
    <keylock hobtid="72057594043236352" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="PK_MessageQueue" id="lockccdf88fb80" mode="X" associatedObjectId="72057594043236352">
    <owner-list>
    <owner id="processccd2e7b848" mode="X" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd4d344e8" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
    <keylock hobtid="72057594046840832" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="ix_MessageQueue_ProcessStartDate" id="lockcc577f0980" mode="S" associatedObjectId="72057594046840832">
    <owner-list>
    <owner id="processccd4d344e8" mode="S" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd2e7b848" mode="X" requestType="wait" />
    </waiter-list>
    </keylock>
    </resource-list>
    </deadlock>

    Thanks,
    BVip

    Is this implemented just now ? Not working at the moment ? else It has been working well, suddenly get deadlocked.

  • Bharatvip - Thursday, February 22, 2018 6:09 PM

    We have a MessageQueue table that gets records populated into it. Three different application servers have services that come to pick records from this table in order to process them. The three servers call a stored procedure GetMessageQueue passing in the server from where the call is being made and also the number of records it wants to pick in order to process them. The proc updates the status and populates the processdate so that the next call from a second server or the same one does not pick up the same records. Being done as we are not deleting the processed records from the table. Have done the selecting of the records in an inner loop and made the whole update and select into a single transaction so we don't need begin/commit transaction logic. Unfortunately we are seeing many deadlocks. Looking at the details of the deadlocks seems when the proc is called from two servers there is a deadlock. Would it be ok to put a with (nolock) hint in the inner select or is that not the preferred way to do this.

    CREATE PROCEDURE [dbo].[GetMessageQueue]
    @NumMessages int,
    @Server varchar(100) = null
    AS
    BEGIN
     set nocount on;
    declare @Returntempmsgs table
     (
      MessageQueueId int,
      TypeId int,
      [Data] varchar(max),
      StatusTypeId int
     )
    update e
     set  StatusTypeId = 1,[ProcessStartDate] = getdate(),[Server] = @Server
     output inserted.MessageQueueId, inserted.TypeId, inserted.[Data], inserted.StatusTypeId
     into @Returntempmsgs  
     from MessageQueue e
     where MessageQueueId in (
     select top (@NumMessages)  
                       MessageQueueId
       from  MessageQueue 
       where  ProcessStartDate is null
       order by MessageQueueId asc
     )
     select * from @Returntempmsgs 
    END

    Here is part of the Extended events description:
    </process>
    </process-list>
    <resource-list>
    <keylock hobtid="72057594043236352" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="PK_MessageQueue" id="lockccdf88fb80" mode="X" associatedObjectId="72057594043236352">
    <owner-list>
    <owner id="processccd2e7b848" mode="X" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd4d344e8" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
    <keylock hobtid="72057594046840832" dbid="7" objectname="Consumer.dbo.MessageQueue" indexname="ix_MessageQueue_ProcessStartDate" id="lockcc577f0980" mode="S" associatedObjectId="72057594046840832">
    <owner-list>
    <owner id="processccd4d344e8" mode="S" />
    </owner-list>
    <waiter-list>
    <waiter id="processccd2e7b848" mode="X" requestType="wait" />
    </waiter-list>
    </keylock>
    </resource-list>
    </deadlock>

    Thanks,
    BVip

    Using the NOLOCK table hint is a way to prevent table locks for SELECT, but you won't be able to stop locks from occurring on UPDATE statements, and besides, using NOLOCK puts you at significant risk for not even getting the correct result for your query that uses it.   You need to fully understand exactly what that hint does before using it.   The vast majority of the time, it really should NOT be used.   The question I have is why you would share a message queue among multiple applications.   Why not use separate message queues?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Well, we have multiple app servers that process the messages in the queue as the volume of messages is large we use multiple servers to read from the queue table. I guess we can use a different way to avoid deadlocks. Maybe update the records for a given server and then select them and return to the calling program.

  • Just curious, but does this make use of Microsoft's MQ Series of software that uses SQL Server message queues?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Have a look at the READPAST hint (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table):

    READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply