SQLServer数据库事务处理实操技巧

事务日志文件撑爆磁盘，是很多DBA的噩梦。根本原因往往不是日志文件本身，而是数据库处于完整恢复模式（Full Recovery Model）下，却从未做过事务日志备份。在完整恢复模式下，SQL Server会一直保留所有已完成事务的日志记录，直到你执行日志备份。如果你不需要时间点恢复，直接把数据库切换到简单恢复模式（Simple Recovery Model），问题就解决了。但生产环境通常需要完整恢复模式，这时候必须建立定期的日志备份作业，比如每15分钟备份一次，既能防止日志无限增长，又能保证数据可恢复到最近15分钟内的任意时间点。

很多开发者以为ROLLBACK会回滚所有操作，但在嵌套事务中，一个ROLLBACK就会让@@TRANCOUNT直接归零，回滚所有层级的事务。举个例子，你写了一个存储过程，内部开启了事务，调用它的外层代码也开启了事务。如果内层遇到错误执行了ROLLBACK，外层代码再执行COMMIT时就会报错，因为事务已经不存在了。正确的做法是使用@@TRANCOUNT来判断事务层级，内层只记录错误状态，由最外层统一决定提交还是回滚。另外，SAVE TRANSACTION可以创建保存点，让你在不影响整个事务的情况下回滚到特定位置，这在处理批量数据校验时特别实用。

默认情况下，SQL Server是自动提交模式，每条语句都是一个独立的事务。但当你执行SET IMPLICIT_TRANSACTIONS ON后，情况就变了，SQL Server会像Oracle那样，在执行任何INSERT、UPDATE、DELETE、SELECT等语句时自动开启一个新事务，你必须显式执行COMMIT或ROLLBACK才能结束。这个设置很容易被忽略，尤其是通过ODBC或OLE DB连接时，某些驱动程序默认会开启它。结果就是你以为数据已经提交了，实际上事务还挂着，锁没释放，连接池耗尽，系统响应越来越慢。排查时先检查sys.dm_exec_sessions中的transaction_isolation_level和open_transaction_count，看看是不是有大量未提交的隐式事务。

死锁发生时，SQL Server会自动选择一个牺牲品来回滚，释放资源。但被牺牲的事务会收到1205号错误，你的应用程序必须捕获这个错误并实现重试逻辑。光靠事后查看错误日志远远不够，你需要开启跟踪标志1222或1204，让死锁的详细信息写入SQL Server错误日志。更推荐的是使用扩展事件（Extended Events）来监控死锁，系统默认就有一个system_health会话，里面已经记录了死锁的xml_deadlock_report事件。你可以从这个会话里提取死锁图，直观地看到两个事务各自持有哪些锁、又在等待哪些锁，从而定位到具体的表和索引，决定是调整查询顺序、添加索引还是使用快照隔离级别。

-- 从system_health会话中提取最近死锁报告
SELECT 
    xed.value('@timestamp', 'datetime2') AS DeadlockTime,
    xed.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        N'system_health*.xel', NULL, NULL, NULL)
) AS xe
CROSS APPLY event_data.nodes('//event[@name="xml_deadlock_report"]') AS nodes(xed)
ORDER BY DeadlockTime DESC;

读已提交快照隔离（RCSI）和快照隔离（SI）能大幅减少读写阻塞，但代价是tempdb的负载会显著增加，因为行版本都存储在tempdb里。开启RCSI后，更新操作会在tempdb中生成行版本，读取操作直接从行版本中获取事务开始时的数据快照，不再请求共享锁。这听起来完美，但你要注意，更新冲突检测机制不同。RCSI下，如果两个事务同时更新同一行，后提交的会直接覆盖先提交的，不会报错。而SI模式下，后提交的事务会收到3960号错误，提示更新冲突。如果你的应用程序没有处理这个错误的逻辑，从RCSI切换到SI时可能会出问题。另外，tempdb的IO性能会成为瓶颈，建议把tempdb放在高速存储上，并分成多个等大的数据文件。

跨数据库或跨服务器的事务需要用到Microsoft分布式事务协调器（MSDTC）。很多人在链接服务器上执行BEGIN DISTRIBUTED TRANSACTION时遇到8501号错误，根本原因是MSDTC服务没启动，或者两台服务器之间的MSDTC安全配置不匹配。MSDTC要求网络访问、事务管理器通信都正确设置，防火墙还要允许135端口和动态分配的RPC端口范围。一个常见的排查思路是使用DTCPing工具测试两台机器之间的MSDTC通信是否正常。另外，分布式事务的性能开销很大，两阶段提交协议需要多次网络往返，在高并发场景下很容易成为瓶颈。如果只是跨同一实例的多个数据库，根本不需要分布式事务，普通事务完全够用。

读未提交（READ UNCOMMITTED）能避免读阻塞，但会读到脏数据，只适合对数据一致性要求极低的报表场景。读已提交（READ COMMITTED）是默认级别，解决了脏读但会出现不可重复读。可重复读（REPEATABLE READ）通过持有共享锁直到事务结束来防止不可重复读，但幻读依然存在。可序列化（SERIALIZABLE）是最严格的级别，通过键范围锁防止幻读，但并发性最差。实际工作中，绝大多数场景用读已提交快照隔离就能平衡一致性和并发性。如果你需要保证两次读取之间数据不变，但又不想锁住太多行，可以考虑在查询中使用UPDLOCK提示，把共享锁升级为更新锁，防止其他事务修改这些行，同时不阻止读取。

-- 使用UPDLOCK防止数据在事务期间被修改，但不阻塞读取
BEGIN TRANSACTION;
SELECT * FROM Orders WITH (UPDLOCK) 
WHERE OrderDate = '2025-01-15';
-- 基于查询结果做业务判断，然后更新
UPDATE Orders SET Status = 'Confirmed' 
WHERE OrderDate = '2025-01-15';
COMMIT;

一个事务从开始到提交的时间越长，持有的锁就越久，阻塞的会话就越多，事务日志也无法截断，导致日志文件持续增长。识别长事务可以用sys.dm_tran_database_transactions视图，结合sys.dm_exec_sessions和sys.dm_exec_requests找到具体执行的SQL语句。重点关注那些database_transaction_log_record_count很高但状态一直是活动的会话。有时候长事务并不是因为SQL本身慢，而是应用程序在事务中间做了非数据库操作，比如调用外部API、发送邮件、等待用户输入。这种设计必须避免，所有外部调用都应该放在事务之外，确保事务只包裹纯粹的数据库操作，且尽可能短小精悍。

SET XACT_ABORT ON是写事务代码时最容易被忽略的关键设置。默认情况下，XACT_ABORT是OFF的，这意味着某些错误（比如违反约束）只会终止当前语句，事务仍然保持打开状态，后续语句会继续执行。这会导致数据不一致，因为你以为出错了事务会回滚，实际上它还在跑。开启XACT_ABORT ON后，任何运行时错误都会自动回滚整个事务并终止批处理。唯一的例外是TRY...CATCH块中的错误，XACT_ABORT不会影响CATCH块的执行。所以规范的事务代码模板应该是：开启XACT_ABORT ON，使用TRY...CATCH，在CATCH块中判断XACT_STATE()的值。如果XACT_STATE()返回-1，说明事务已经不可提交，只能回滚；返回1说明事务仍然有效，可以选择提交或回滚；返回0说明没有活动事务。

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- 业务操作
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        ROLLBACK;
        -- 记录日志，事务已不可提交
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        ROLLBACK;
        -- 事务仍可提交，但这里选择回滚
    END
    THROW;
END CATCH;

事务性复制能实现近乎实时的数据同步，但它本质上是一个日志读取器代理不断扫描发布数据库的事务日志，把标记为复制的事务分发到订阅服务器。如果发布端产生大量事务，日志读取器可能跟不上，导致订阅端延迟增加。延迟过大的时候，订阅端的数据和发布端可能相差几分钟甚至几小时。监控复制延迟可以用复制监视器或者查询distribution数据库中的MSrepl_commands表。减少延迟的方法包括优化日志读取器代理的性能参数，比如增加ReadBatchSize和QueryTimeout，或者把发布端和分发端放在性能更强的服务器上。另外要注意，如果发布端执行了大批量更新，事务日志会瞬间膨胀，日志读取器处理这些记录需要时间，订阅端的同步窗口会明显拉长，这一点在业务高峰期做批量操作前必须评估。

内存优化表（In-Memory OLTP）的事务处理方式和基于磁盘的表完全不同。它使用乐观并发控制，没有锁和闩锁，事务通过时间戳和行版本实现隔离。在内存优化表上，所有隔离级别都被映射为快照隔离、可重复读快照隔离或可序列化快照隔离三种之一。这意味着即使你指定了READ UNCOMMITTED，实际执行的也是快照隔离，不会读到脏数据。但这也带来了验证阶段的概念，事务提交时会进行验证，如果检测到依赖的行版本被其他事务修改了，就会抛出41301号错误，事务自动回滚。这种错误在基于磁盘的表中不会出现，所以迁移到内存优化表时，应用程序必须处理这种提交失败并实现重试。另外，内存优化表的事务不能跨数据库，也不能参与分布式事务，这些都是设计时需要提前考虑的约束。

事务日志文件内部被划分为多个虚拟日志文件（VLF），日志的截断和重用都是以VLF为单位的。如果日志文件增长时每次增长量太小，比如每次只增长1MB，就会产生大量细碎的VLF，导致日志管理开销增大，恢复时间变长，甚至影响事务日志写入性能。你可以用DBCC LOGINFO查看当前数据库的VLF数量和大小分布。理想的VLF数量是几十个，每个VLF大小在几百MB左右。修复方法是先把日志文件收缩到最小，然后一次性扩大到目标大小，比如直接ALTER DATABASE修改日志文件大小为32GB，这样SQL Server会创建少量但足够大的VLF。但要注意，收缩日志文件会破坏日志链，收缩后必须立即做一次完整备份或日志备份来重建日志链。

-- 查看当前数据库的VLF信息
DBCC LOGINFO;
-- 返回的行数就是VLF数量，Status=2表示该VLF正在使用中

死锁、超时、网络抖动都可能导致事务失败，应用程序必须有健壮的重试机制。重试不是简单的循环执行，需要加入指数退避策略，比如第一次重试等待100毫秒，第二次200毫秒，第三次400毫秒，避免在数据库压力大时雪上加霜。同时要区分可重试错误和不可重试错误，1205死锁错误、41301内存优化表验证失败错误可以重试，但主键冲突、外键约束违反这类业务逻辑错误重试没有意义。重试次数一般设置为3到5次，总等待时间控制在30秒以内，超过这个时间就应该向上层抛出异常，避免请求堆积。另外，重试逻辑要保证幂等性，如果事务中包含了INSERT操作，重试时可能导致重复插入，需要在业务逻辑上做去重判断，或者使用MERGE语句。

事务日志的写入性能直接决定了事务吞吐量的上限。日志写入是顺序写的，理论上机械硬盘也能胜任，但高并发小事务场景下，日志写入的延迟会成为瓶颈。把事务日志文件放在低延迟、高IOPS的存储上，比如NVMe SSD或企业级SSD，能显著降低提交延迟。数据文件可以放在相对慢一些的存储上，因为数据页的写入有检查点机制异步完成。另外，日志文件的磁盘队列长度（Avg. Disk sec/Write）应该低于5毫秒，如果持续高于20毫秒，说明存储性能不足。在虚拟化环境中，还要注意存储的QoS设置，避免其他虚拟机争抢IO资源。一个常见的优化是把tempdb、日志文件、数据文件分别放在不同的物理磁盘上，减少IO争用，这个原则在物理机和云服务器上都适用。