SQL Service Broker Internal Activation Questions

• Kuzey

  Say Hey Kid

  Points: 673

  More actions

  April 3, 2014 at 6:47 am

  #306300

  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