SQL Server 2019存储过程中如何正确使用事务保证数据一致性？

在SQL Server中，存储过程内写BEGIN TRANSACTION无效，因默认自动提交模式使每条语句独立成事务；正确做法是事务由调用方显式开启和结束，存储过程仅负责业务逻辑与异常响应，并通过SAVEPOINT支持局部回滚。

存储过程里写 BEGIN TRANSACTION 无效？调用方必须开事务

绝大多数人写的存储过程，ROLLBACK 不生效，根本原因不是语法错，而是事务上下文根本没建立起来。SQL Server 默认是自动提交模式，每条语句单独成事务——你在存储过程里写 BEGIN TRANSACTION，执行完就自动提交了，后续的 ROLLBACK 没有可回滚的内容。

正确做法是：事务边界必须由调用方显式控制，而不是藏在存储过程内部。存储过程只负责业务逻辑和异常响应，不负责启停事务。

• 调用前用 BEGIN TRANSACTION 开启事务
• 再执行存储过程（过程内可含 SAVEPOINT 或 ROLLBACK TO）
• 最后由调用方决定 COMMIT 还是 ROLLBACK
• 若过程内需局部回滚，必须配 SAVEPOINT，且不能跨过程边界复用

DECLARE EXIT HANDLER 在 SQL Server 里不存在，改用 TRY…CATCH

MySQL 的 DECLARE EXIT HANDLER 在 SQL Server 中不适用。SQL Server 必须用 TRY…CATCH 块捕获错误，并在 CATCH 中手动判断是否需要回滚。

注意：@@TRANCOUNT 是关键指标——只有它 > 0 时，ROLLBACK 才有意义；否则会报错 “The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION”。

• TRY 块中执行 DML 操作
• CATCH 块里先查 @@TRANCOUNT，再决定 ROLLBACK 或 ROLLBACK TO @savepoint
• 建议在 CATCH 中记录错误日志（如 INSERT INTO error_log），避免失败静默
• 避免在 CATCH 里再执行可能失败的操作（如写日志表时磁盘满），否则会掩盖原始错误

批量更新必须分批 + 主键升序，否则容易死锁或锁表

一次更新上万行，全塞进一个事务，不仅慢，还极易触发 1205 死锁或长时间阻塞其他查询。InnoDB（MySQL）和 SQL Server 都要求访问顺序一致，否则并发时锁顺序冲突。

SQL Server 下更需注意：用 UPDATE … FROM 或 JOIN 替代 IN (SELECT ...)，后者在某些版本下会锁全表或无法利用索引。

• 按主键升序处理，加 ORDER BY id 强制访问顺序
• 拆成每 500 行一批，用 WHILE 循环 + OFFSET-FETCH 或临时表分页
• 传入 ID 列表优先用表变量（@batch_ids TABLE(id INT PRIMARY KEY)），别拼逗号字符串
• 每次批处理后检查 @@ROWCOUNT，为 0 时及时退出，避免空跑

事务隔离级别选错，会导致“数据对得上但业务不对”

默认的 READ COMMITTED 能防脏读，但无法阻止不可重复读和幻读。比如库存扣减场景：事务 A 查到库存 100，事务 B 同时下单扣减 1，A 再查还是 100（快照），接着执行 UPDATE SET stock = stock - 1，结果变成 99——但实际应剩 98。

这类问题不是事务没起作用，而是隔离级别太低，读写不一致。解决不靠加锁语句堆砌，而靠匹配业务语义的隔离策略。

• 高一致性要求场景（如金融扣款），改用 REPEATABLE READ 或带 UPDLOCK, HOLDLOCK 的 SELECT
• 避免在事务中长时间等待用户输入或外部 API 响应，长事务 = 长锁持有 = 并发瓶颈
• 用 sp_who2 或 sys.dm_exec_requests 查看阻塞链，比猜更准

事务真正生效的验证点很朴素：查表确认数据已还原，再查 sys.dm_tran_active_transactions 确认事务已退出。很多“回滚成功”的假象，其实只是日志没刷、连接没断、或者你 SELECT 读到了旧版本快照。