none
Internal Activation Questions

    Question

  • Hi All,

    First time, I setup Internal Activation for two 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 this 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 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

    CREATE QUEUE   [RecordAddUsersQueue];
    CREATE SERVICE [RecordAddUsersService] ON QUEUE [RecordAddUsersQueue];
    
    ALTER QUEUE [AddUsersQueue] WITH ACTIVATION 
    (     STATUS = ON,
          MAX_QUEUE_READERS = 1,
          PROCEDURE_NAME = usp_AddInstanceUsers,
          EXECUTE AS OWNER);
    
    


    Thanks,

    Kuzey






    • Edited by KuzeyI Wednesday, April 02, 2014 7:21 PM Code fix
    Wednesday, April 02, 2014 5:06 PM