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