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

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 [trans_globalreference_edw_tedw].[parent_cust_industry_proc]
        @pipeline_name VARCHAR(100),
    @pipeline_run_id VARCHAR(100),
    @pipeline_trigger_name VARCHAR(100),
    @pipeline_trigger_id VARCHAR(100),
    @pipeline_trigger_type VARCHAR(100),
    @pipeline_trigger_date_time_utc DATETIME2
AS
BEGIN TRY

-- LOAD TYPE: Full refresh


IF EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'trans_globalreference_edw_tedw' AND name="parent_cust_industry_ld" )
BEGIN
    DROP TABLE [trans_globalreference_edw_tedw].[parent_cust_industry_ld]
END

ELSE IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'trans_globalreference_edw_tedw' AND name="parent_cust_industry_ld" )
BEGIN
    CREATE TABLE [trans_globalreference_edw_tedw].[parent_cust_industry_ld] 
    WITH
        (
            DISTRIBUTION = REPLICATE
            , CLUSTERED COLUMNSTORE INDEX
        )
    AS
    SELECT  CAST(src.[parent_cust_ind_code] as varchar(5)) as [parent_cust_ind_code],
            CAST(NULLIF(src.[cancel_on_period], '') as varchar(5)) as [cancel_on_period],
            CAST(NULLIF(src.[created_by_qlook_id], '') as varchar(8)) as [created_by_qlook_id],
            CAST(CAST(NULLIF(NULLIF(src.[creation_date_time], ''), '?') as datetime2(6)) as varchar(35)) as [creation_date_time],
            CAST(NULLIF(src.[effective_on_period], '') as varchar(5)) as [effective_on_period],
            CAST(CAST(NULLIF(NULLIF(src.[last_update_date_time], ''), '?') as datetime2(6)) as varchar(35)) as [last_update_date_time],
            CAST(NULLIF(src.[last_updated_by_qlook_id], '') as varchar(8)) as [last_updated_by_qlook_id],
            CAST(NULLIF(src.[parent_cust_ind_name], '') as nvarchar(50)) as [parent_cust_ind_name],
            CAST(CAST(NULLIF(NULLIF(src.[update_date_time], ''), '?') as datetime2(0)) as varchar(35)) as [update_date_time],
            CAST(src.[ingest_partition] AS [varchar](100)) AS [ingest_partition],
            CAST(src.[ingest_channel] AS [varchar](100)) AS [ingest_channel],
            CAST(src.[file_path] AS [varchar](100)) AS [file_path],
            CAST(src.[root_path] AS [varchar](100)) AS [root_path],
            CAST(@pipeline_name AS [varchar](100)) AS [pipeline_name] ,
            CAST(@pipeline_run_id AS [varchar](100)) AS [pipeline_run_id],
            CAST(@pipeline_trigger_name AS [varchar](100)) AS [pipeline_trigger_name],
            CAST(@pipeline_trigger_id AS [varchar](100)) AS [pipeline_trigger_id],
            CAST(@pipeline_trigger_type AS [varchar](100)) AS [pipeline_trigger_type],
            CAST(@pipeline_trigger_date_time_utc AS [datetime2](7)) AS [pipeline_trigger_date_time_utc],
            CAST(GETDATE() AS [datetime2](7)) AS [trans_load_date_time_utc],
            CAST('I' AS CHAR(1)) as adle_transaction_code,
            CAST(hashbytes('sha2_256',upper(trim(coalesce([parent_cust_ind_code], '')))) as VARBINARY(32)) AS [hash_key]
    FROM    [trans_globalreference_edw_tedw].[parent_cust_industry_temp] as src
    
        
END

IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'trans_globalreference_edw_tedw' AND name="parent_cust_industry_hd" )
BEGIN
    RENAME OBJECT [trans_globalreference_edw_tedw].[parent_cust_industry] TO [parent_cust_industry_hd]
END

IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'trans_globalreference_edw_tedw' AND name="parent_cust_industry" )
BEGIN
    RENAME OBJECT [trans_globalreference_edw_tedw].[parent_cust_industry_ld] TO [parent_cust_industry]
END

IF EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'trans_globalreference_edw_tedw' AND name="parent_cust_industry_hd" )
BEGIN
    DROP TABLE [trans_globalreference_edw_tedw].[parent_cust_industry_hd]
END
;



END TRY

BEGIN CATCH
    DECLARE @db_name VARCHAR(200),
            @schema_name VARCHAR(200),
            @error_nbr INT,
            @error_severity INT,
            @error_state INT,
            @stored_proc_name VARCHAR(200),
            @error_message VARCHAR(8000),
            @created_date_time DATETIME2

    SET @db_name=DB_NAME()
    SET @schema_name=SUBSTRING (@pipeline_name, CHARINDEX('2', @pipeline_name) + 1, LEN(@pipeline_name) - CHARINDEX('2', @pipeline_name) - 3 )
    SET @error_nbr=ERROR_NUMBER()
    SET @error_severity=ERROR_SEVERITY()
    SET @error_state=ERROR_STATE()
    SET @stored_proc_name=ERROR_PROCEDURE()
    SET @error_message=ERROR_MESSAGE()
    SET @created_date_time=GETDATE()

    EXECUTE [adle_platform_orchestration].[elt_error_log_proc]
        @db_name,
        'ERROR',
        @schema_name,
        @error_nbr,
        @error_severity,
        @error_state,
        @stored_proc_name,
        'PROC',
        @error_message,
        @created_date_time,
        @pipeline_name,
        @pipeline_run_id,
        @pipeline_trigger_name,
        @pipeline_trigger_id,
        @pipeline_trigger_type,
        @pipeline_trigger_date_time_utc
        ;
    THROW;
END CATCH

;
GO

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

Normally, the [trans_globalreference_edw_tedw].[parent_cust_industry] would finish the data load first before other store procs depend on it. But in a rare occasion, the data is vastly 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 [trans_globalreference_edw_tedw].[parent_cust_industry_proc] so that if the two store procs happen to run at the same time, the [trans_globalreference_edw_tedw].[parent_cust_industry_proc] would finish first then the remaining store procedure can start reading the data from parent_cust_industry table ?

Leave a Comment