February 22, 2018 at 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
February 23, 2018 at 2:40 am
Bharatvip - Thursday, February 22, 2018 6:09 PMWe 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
ENDHere 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.
February 23, 2018 at 6:26 am
Bharatvip - Thursday, February 22, 2018 6:09 PMWe 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
ENDHere 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)
February 23, 2018 at 11:52 am
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.
February 26, 2018 at 6:22 am
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)
February 28, 2018 at 8:15 am
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