如何解决SQL Server中的竞态条件？已尝试事务仍报错

事务隔离级别（包括SERIALIZABLE）主要针对数据操纵语言(DML)（如SELECT/INSERT/UPDATE/DELETE）的并发一致性问题，对数据定义语言(DDL)（如CREATE/DROP TABLE这类元数据操作）的并发管控能力有限。

你的场景中，多个会话执行DROP TABLE IF EXISTS后，在WAITFOR DELAY的1秒窗口期内，其他会话可以绕过事务隔离级别限制创建TABLE_A，导致当前会话执行SELECT ... INTO时触发“对象已存在”的错误——因为DDL操作的锁机制和DML完全不同，SERIALIZABLE不会阻止其他会话修改元数据。

可以利用SQL Server的sp_getapplock存储过程，为这段操作申请一个排他性的应用锁，确保同一时间只有一个会话执行DDL相关逻辑。修改后的存储过程如下：

CREATE PROCEDURE dbo.TestRaceCondition
AS
BEGIN
    DECLARE @LockResult INT;

    -- 申请排他应用锁，锁名称自定义，超时时间设为-1（无限等待）
    EXEC @LockResult = sp_getapplock 
        @Resource = 'TestRaceCondition_TableALock',
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = -1;

    -- 确保锁申请成功
    IF @LockResult >= 0
    BEGIN
        BEGIN TRY
            DROP TABLE IF EXISTS dbo.TABLE_A;

            WAITFOR DELAY '00:00:01';

            SELECT 1 ID INTO dbo.TABLE_A;
        END TRY
        BEGIN CATCH
            -- 捕获异常并抛出
            THROW;
        END CATCH
        FINALLY
            -- 释放应用锁
            EXEC sp_releaseapplock 
                @Resource = 'TestRaceCondition_TableALock',
                @LockOwner = 'Session';
        END
    END
    ELSE
    BEGIN
        -- 锁申请失败时抛出错误
        THROW 50000, '无法获取应用锁，无法执行操作', 1;
    END
END
GO

1. sp_getapplock会基于指定的资源名称创建一个会话级别的排他锁，其他会话调用同一资源名称的sp_getapplock时会进入等待，直到锁被释放。
2. 利用TRY/CATCH/FINALLY确保无论操作成功还是失败，应用锁都会被释放，避免出现死锁或锁残留。
3. 这种方式直接管控了DDL操作的并发，从根源上避免了多个会话同时创建/删除同一表的冲突。

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