April 3, 2014 at 6:47 am
Hi All,
I setup Internal Activation for two existing stored procedures. One, inserts one or more records , the other, updates one or more records in the same table. So, I have two initiator, two target queues.
It works fine on development so far, but I wonder what types of problems I might encounter when we move it to prod where these two stored procedures are frequently called. We have already experiencing deadlock issues caused by these two stored procedures. Asynchronous execution is my main goal with this implementation.
Q1) Is there a way to use one target queue for both stored procedures to prevent any chance of deadlocks?
Q2) Is there anything I can do to make it more reliable? like one execution error should not stop incoming requests
to the queue?
Q3) Tips to improve scalability (high number of execution per second)?
Q4) Can I set RETRY if there is a deadlock?
Here is the partial code of the insert stored procedure;
CREATE QUEUE [RecordAddUsersQueue];
CREATE SERVICE [RecordAddUsersService] ON QUEUE [RecordAddUsersQueue];
ALTER QUEUE [AddUsersQueue] WITH ACTIVATION
( STATUS = ON,
MAX_QUEUE_READERS = 1, --or 10?
PROCEDURE_NAME = usp_AddInstanceUsers,
EXECUTE AS OWNER);
CREATE PROCEDURE [dbo].[usp_AddInstanceUsers] @UsersXml xml
AS
BEGIN
DECLARE @Handle uniqueidentifier;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [RecordAddUsersService]
TO SERVICE 'AddUsersService'
ON CONTRACT [AddUsersContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE [AddUsersXML] (@UsersXml);
END
GO
CREATE PROCEDURE [dbo].[usp_SB_AddInstanceUsers]
AS
BEGIN
DECLARE @Handle uniqueidentifier;
DECLARE @MessageType sysname;
DECLARE @UsersXML xml;
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
WAITFOR
(RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@UsersXML = message_body
FROM [AddUsersQueue]), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
IF (@MessageType = 'ReqAddUsersXML')
BEGIN
--<INSERT>....
DECLARE @ReplyMsg nvarchar(100);
SELECT
@ReplyMsg = N'<ReplyMsg>Message for AddUsers Initiator service.</ReplyMsg>';
SEND ON CONVERSATION @Handle
MESSAGE TYPE [RepAddUsersXML] (@ReplyMsg);
END
ELSE
IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Handle;
END
ELSE
IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
END CONVERSATION @Handle;
END
COMMIT TRANSACTION;
END
END
GO
Thank you,
Kuzey
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply