Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: SQL Service Broker Internal Activation Questions

    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;

    Code:
        
        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
    Thanks,

    Kuzey

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As far as the retry logic goes, if a RECEIVE is rolled back, the message goes right back at the front of the queue for the next RECEIVE to try and grab it. One of the nice things about queues (from my limited testing) is that it seems that if you RECEIVE a message off the queue, and hold it (say with a transaction using waitfor delay to represent lots of processing) other RECEIVES happily grab records further back in the queue. I may have to revisit that, though.

    The procedure looks pretty good, but I am wondering why you have a WHILE (1 = 1) followed by an IF (@@ROWCOUNT = 0) BREAK. Doesn't that go against the essence of a service?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •