How to initiate tables lock for stored procedure in Azure Synapse Analytics?

Question

Currently, I encountered an issue on Azure Synapse Analytics. I have a parent_cust_industry table which is full refresh - The table loads using stored procedure as below:

    CREATE PROCEDURE [test].[test_proc] AS
    BEGIN
    
    -- LOAD TYPE: Full refresh
    
    
    IF EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_ld' )
    BEGIN
        DROP TABLE [test].[test_ld]
    END
    
    ELSE IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_ld' )
    BEGIN
        CREATE TABLE [test].[test_ld] 
        WITH
            (
                DISTRIBUTION = REPLICATE
                , CLUSTERED COLUMNSTORE INDEX
            )
        AS
        SELECT  CAST(src.[test_code] as varchar(5)) as [test_code],
                CAST(NULLIF(src.[test_period], '') as varchar(5)) as [test_period],
                CAST(NULLIF(src.[test_id], '') as varchar(8)) as [test_id]
        FROM    [test].[test_temp] as src
        
            
    END
    
    IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_hd' )
    BEGIN
        RENAME OBJECT [test].[test] TO [test_hd]
    END
    
    IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test' )
    BEGIN
        RENAME OBJECT [test].[test_ld] TO [test]
    END
    
    IF EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_hd' )
    BEGIN
        DROP TABLE [test].[test_hd]
    END
END
    ;

The error happens when there is another stored procedure runs at the same time to load data to another table and it requires the [test].[test] table which cause invalid object for [test].[test].

Normally, the [test].[test_proc] would finish the data load first before other store procs depend on it. But in rare occasion, the data is considerably large, it took more time to process and can cause the invalid object error.

Is there a locking mechanism that I can apply to the stored procedure [test].[test_proc] so that if the two store procs happen to run at the same time, the [test].[test_proc] would finish first then the remaining store procedure can start reading the data from [test].[test] table ?

Answer 1

As you do not have access to traditional SQL Server locking procs like sp_getapplock and the default transaction isolation level of Azure Synapse Analytics, dedicated SQL pools is READ UNCOMMITTED you have limited choices.

You could route all access to this proc through a single Synapse Pipeline and set its concurrency setting to 1. This would ensure only one pipeline execution could happen at once, causing subsequent calls to the same pipeline to queue up.

Set the pipeline concurrency in the Pipeline settings here:

So you could have a single main pipeline that routes to others, eg using the Switch or If activities and ensure the proc cannot be called by other pipelines - should work.