SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Message Queue stored procedure getting Deadlocked.


Message Queue stored procedure getting Deadlocked.

Author
Message
Bharatvip
Bharatvip
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 169

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





Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3664 Visits: 531
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.

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98421 Visits: 7265
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)
Smile Smile Smile
Health & Nutrition
Bharatvip
Bharatvip
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 169
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.



sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98421 Visits: 7265
Just curious, but does this make use of Microsoft's MQ Series of software that uses SQL Server message queues?

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
lbhlittleton
lbhlittleton
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 1168
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search