Managing synonyms in a proc with nested procs usin

Question

Building on the topics previously addressed by gbn in these questions Q1, Q2, Q3, Q4, and regarding the use of synonyms and re-creating the synonyms to keep the synonyms pointing at live data, it is not clear to me how prevent a "race condition" by using

"sp_getapplock after BEGIN TRAN in Transaction mode and trap/handle the return status as required."

MSDN documentation for sp_getapplock is a bit cryptic for me. For instance, can resource_name be any made-up string? But more to the point: if I run a single proc containing nested procs, where the first step is to build tables, and if those succeed, the next major step is to DROP and CREATE the existing synonyms, how would I properly implement sp_getapplock?

CREATE PROCEDURE [dbo].[some_old_proc]   
AS  
SET XACT_ABORT, NOCOUNT ON

DECLARE     @nested_build_success varchar(3) = 'No' 
DECLARE     @starttrancount int

BEGIN TRY

    SET @starttrancount = @@TRANCOUNT

    IF      @starttrancount = 0
    BEGIN TRANSACTION
                -- fill the tables that the synonyms don't point to yet...
                EXEC    dbo.nested_proc_1       
                EXEC    dbo.nested_proc_2
                EXEC    dbo.nested_proc_3
                EXEC    dbo.nested_proc_4
                EXEC    dbo.nested_proc_5

        IF      @starttrancount = 0
        BEGIN  
            COMMIT TRANSACTION

            SET @nested_build_success = 'Yes'
        END

END TRY
BEGIN CATCH

    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION    
        -- RAISERROR... log error event   
END CATCH


IF  @nested_build_success = 'Yes'
BEGIN TRAN
        -- simple talk article
 -- http://www.simple-talk.com/sql/t-sql-programming/developing-modifications-that-survive-concurrency/

         DECLARE @ret INT    -- does it matter what the resource_name is?
          EXEC @ret = sp_getapplock @Resource = 'DoesNameMatterHere', @LockMode = 'Exclusive';

         IF @ret < 0
         BEGIN  
             -- log error message? 
         END
         ELSE
         BEGIN 
            -- call the proc that a does a DROP and CREATE of the relevant synonyms
        -- so the synonyms point at a different set of tables...
           EXEC dbo.change_the_synonyms      
         END

    COMMIT TRAN

Perhaps a different and better way exists to avoid a race condition than the use of sp_getapplock, or a good example of what I'm trying to do is available?

Answer 1

If I understand your question correctly the preparation steps and the synonym setup must be in a single transaction, not two separate transactions. Here is an example, based on the Exception handling and nested transactions template:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        EXEC sp_getapplock 'usp_my_procedure_name', 
                     'Exclusive', 
                     'TRANSACTION';

        EXEC    dbo.nested_proc_1;       
        EXEC    dbo.nested_proc_2;
        EXEC    dbo.nested_proc_3;
        EXEC    dbo.nested_proc_4;
        EXEC    dbo.nested_proc_5;

        EXEC    dbo.change_the_synonyms;

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), 
                @message = ERROR_MESSAGE(), 
                @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 
                16, 1, @error, @message) ;
    end catch   
end

This will do all the work atomically. It will use an applock to serialize access so that no two procedures execute this work concurrently. In case of error the work will either completely roll back or, in the case when the caller already has a transaction, it rolls back the work to a consistent state at the procedure entry w/o rolling back the caller (this is extremely useful in batch processing). XACT_ABORT has its use in deployment script, but mixing XACT_ABORT with TRY/CATCH is a big no-no in my book.