Azure Synapse Analytics中如何为存储过程实现表锁机制？

你当前采用的是蓝绿表切换的全量刷新逻辑，报错的核心原因有两个：

• Azure Synapse 专用SQL池的RENAME属于元数据DDL操作，默认自动提交，无法通过显式事务将「重命名旧表为hd」「重命名新ld表为test」两个操作合并为原子操作，两个操作之间必然存在[test].[test]对象名无绑定的时间窗口
• 你没有对整个刷新流程加跨会话的并发控制，当数据量变大刷新耗时变长时，并发的读请求刚好命中这个空窗期，就会触发对象无效错误
  常规的表锁、事务锁无法锁定元数据切换的整个流程，需要用应用级锁实现跨存储过程的并发阻塞。

使用Synapse原生支持的sp_getapplock自定义应用锁实现互斥控制：

• 刷新存储过程启动时申请排他锁，整个建表、切换、删旧表的全流程持锁，完成后释放
• 所有依赖[test].[test]的存储过程，在读表前申请同个锁资源的共享锁，拿不到锁就持续等待，直到刷新流程完成释放排他锁后，才能继续读表
  这种方式可以完全避开空窗期，不会出现对象不存在的错误。

第一步：改造全量刷新存储过程 #

修复原有逻辑中首次执行报错、重名冲突问题的同时，加入应用锁逻辑，完整代码如下：

CREATE PROCEDURE [test].[test_proc] AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @lockResult INT;

    -- 申请排他应用锁，锁资源名全局唯一即可
    EXEC @lockResult = sp_getapplock 
        @Resource = 'Refresh_lock_test_table',
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = -1; -- -1代表无限等待直到成功获取锁

    -- 锁获取失败直接抛错
    IF @lockResult < 0
    BEGIN
        RAISERROR('Failed to acquire refresh lock for test table', 16, 1);
        RETURN;
    END

    -- 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

    -- 构建新的全量数据到临时加载表
    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;

    -- 清理残留的旧版本表
    IF EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_hd' )
    BEGIN
        DROP TABLE [test].[test_hd];
    END

    -- 切换旧表为历史版本
    IF EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test' )
    BEGIN
        RENAME OBJECT [test].[test] TO [test_hd];
    END

    -- 切换新加载表为正式表
    RENAME OBJECT [test].[test_ld] TO [test];

    -- 删除旧版本表
    IF EXISTS ( SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'test' AND name = 'test_hd' )
    BEGIN
        DROP TABLE [test].[test_hd];
    END

    -- 释放排他锁
    EXEC sp_releaseapplock
        @Resource = 'Refresh_lock_test_table',
        @LockOwner = 'Session';
END;

第二步：改造依赖该表的存储过程 #

在所有访问[test].[test]的逻辑前，加共享锁申请逻辑，示例如下：

-- 访问test表前先申请共享锁
DECLARE @lockResult INT;
EXEC @lockResult = sp_getapplock 
    @Resource = 'Refresh_lock_test_table',
    @LockMode = 'Shared',
    @LockOwner = 'Session',
    @LockTimeout = 1800; -- 最多等待30分钟，可根据业务最大刷新时长调整

IF @lockResult < 0
BEGIN
    RAISERROR('Failed to acquire read lock for test table, wait timeout', 16, 1);
    RETURN;
END

-- 此处编写原有读取[test].[test]的业务逻辑
-- SELECT * FROM [test].[test] ......

-- 业务逻辑执行完后释放共享锁
EXEC sp_releaseapplock
    @Resource = 'Refresh_lock_test_table',
    @LockOwner = 'Session';

• 应用锁是实例级别的，所有会话访问同一个@Resource名的锁时都会遵循互斥规则，不要修改锁的资源名
• 不要将锁所有者设为Transaction，因为Synapse不允许在显式事务中执行RENAME这类DDL操作，会触发报错
• 锁超时时间不要设得过短，要大于该表全量刷新的最大历史耗时，避免正常等待时触发超时错误

内容的提问来源于stack exchange，提问作者Ken Masters