SSMS中执行列不匹配的INSERT/SELECT导致SQL Server锁死原因问询

锁库核心成因 #

你执行的SQL逻辑中，DELETE FROM sourceTable会在事务未提交前给整张sourceTable加上排他锁，后续INSERT语句抛出列不匹配错误时，事务并没有按你预期自动回滚，排他锁一直被会话持有，所有访问sourceTable的业务请求都会被阻塞，最终表现为整个数据库被锁死。

为什么仅SSMS环境会复现该问题 #

核心差异是两者的事务相关默认配置不同：

• 生产环境的应用连接默认开启XACT_ABORT ON，该配置下事务内出现运行时错误时，会自动终止整个批处理并回滚全部未提交事务，DELETE操作加的排他锁会被立即释放，不会出现长时间阻塞。
• SSMS的默认配置为XACT_ABORT OFF，遇到213这类列不匹配错误时，只会终止当前出错的INSERT语句，后续的COMMIT、删除临时表逻辑都不会执行，事务会一直处于未提交的挂起状态。只要你没有主动关闭查询窗口或者手动回滚事务，排他锁就会一直被持有，导致所有业务请求被阻塞。

• 所有涉及事务的逻辑必须加异常捕获机制，出错时主动回滚事务，参考写法：

BEGIN TRY
    BEGIN TRANSACTION;

    DELETE FROM sourceTable;

    -- 必须显式列明插入列和查询列，禁止用*
    INSERT INTO sourceTable (col1, col2, col3, ...)
    SELECT col1, col2, col3, ... FROM #tempTable;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH

• 禁止使用SELECT *、INSERT INTO ... SELECT *这类模糊写法，所有列必须显式声明，避免表结构变更时出现列数量、顺序不匹配的问题。
• 全表删除再插入的逻辑对并发非常不友好，锁持有时间过长，建议改为MERGE或者增量更新的方式，尽可能缩小锁的范围和持有时间。

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