T-SQL事务与SqlTransaction提交行为不一致的原因排查

实验背景 #

对比两组实验的行为差异：

• 第一组：使用T-SQL原生BEGIN TRAN ... COMMIT TRAN语句
• 第二组：使用.NET SqlClient的SqlTransaction对象
  其余操作完全一致，实验步骤如下：

1. 在数据库中创建临时表
2. 启动事务
3. 执行含Truncate、Insert、大数据量SELECT的操作，获取表的排他锁
4. 启动独立连接的后台任务，对该表执行只读查询（需等待锁释放）
5. 在原事务的DataReader迭代过程中主动抛出异常
6. 等待并显示后台任务的查询结果

示例代码 #

using System;
// TODO: Add package reference to System.Data.SqlClient
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

await Logic.ExperimentAsync(useBackendTransaction: false);
await Logic.ExperimentAsync(useBackendTransaction: true);

/////////////////////////////////////////////////////////////////////////

static class Logic
{
    // TODO: initialize a valid connection string
    public const string ConnectionString = "";

    static readonly string RecreateTable = @"
DROP TABLE IF EXISTS [TempTable];
CREATE TABLE [TempTable] ([Value] int);";

    static readonly string TruncateInsertAndSelect = @"
---- BEGIN TRAN MssqlTransaction;

TRUNCATE TABLE [TempTable];

INSERT INTO [TempTable]([Value])
VALUES      (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);


WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

SELECT k AS id
FROM Nums
WHERE k <= 100000000;

---- COMMIT TRAN MssqlTransaction;";

    static readonly string Select = @"
SELECT TOP 1 SUM([Value]) FROM [TempTable];";

    public static async Task ExperimentAsync(bool useBackendTransaction)
    {
        await using var aConn = new SqlConnection(ConnectionString);
        await aConn.OpenAsync();

        // Recreate table
        await using var aCmd = aConn.CreateCommand();
        aCmd.CommandText = RecreateTable;
        await aCmd.ExecuteNonQueryAsync();

        // Add data and select
        aCmd.CommandText = useBackendTransaction
            ? TruncateInsertAndSelect
            : TruncateInsertAndSelect.Replace("---- ", String.Empty);
        aCmd.Transaction = useBackendTransaction
            ? aConn.BeginTransaction("BackendTransaction")
            : null;

        bool aErrors = false;
        Task<string> aSecondarySelectTask = null;
        try
        {
            await using var aRdr = await aCmd.ExecuteReaderAsync();
            // In `useBackendTransaction = false' mode, SQL Profiler logs
            // a successfull commit of "MssqlTransaction" at this point.

            aSecondarySelectTask = SecondarySelectAsync();

            while (await aRdr.ReadAsync())
                throw new Exception();  // Throw on purpose when reader is active
        }
        catch
            { aErrors = true; }
        
        if (aCmd.Transaction != null)
        {
            if (!aErrors)
                await aCmd.Transaction.CommitAsync();
            else
                await aCmd.Transaction.RollbackAsync();
        }

        var aPrefix = useBackendTransaction
            ? "Using backend transactions"
            : "  Using MSSQL transactions";
        Console.WriteLine($"{aPrefix}: {await aSecondarySelectTask}");
    }

    private static async Task<string> SecondarySelectAsync()
    {
        var aConn = new SqlConnection(ConnectionString);
        await aConn.OpenAsync();

        await using var aCmd = aConn.CreateCommand();
        aCmd.CommandText = Select;

        return await aCmd.ExecuteScalarAsync(CancellationToken.None) is int aRet
            ? aRet.ToString(System.Globalization.CultureInfo.InvariantCulture)
            : "(null)";
    }
}

实验结果 #

Using MSSQL transactions: 45
Using backend transactions: (null)

差异原因分析 #

1. 事务控制主体与提交时机的本质区别 #

• T-SQL事务：事务的创建、提交完全由SQL Server引擎在批处理内执行。整个BEGIN TRAN到COMMIT TRAN的语句是一个完整的批处理，数据库会按顺序执行所有语句：先完成Truncate、Insert，再执行大数据量SELECT，最后执行COMMIT。即使SELECT的结果集需要流式返回给客户端（即DataReader未读取完所有数据），COMMIT语句执行后事务立即提交，表上的排他锁随之释放。因此后台任务的查询能立即读到已提交的Insert数据，返回SUM(0-9)=45。
• SqlTransaction：事务由.NET SqlClient客户端控制，事务的生命周期从BeginTransaction开始，直到客户端调用CommitAsync或RollbackAsync才结束。当调用ExecuteReaderAsync打开DataReader时，事务仍处于活跃状态，表上的排他锁被持续持有。由于代码在DataReader的ReadAsync阶段主动抛出异常，后续触发了RollbackAsync，事务内的所有操作（Truncate、Insert）全部回滚，TempTable回到空状态。后台任务等待锁释放后，读取空表的SUM结果为null。

2. 操作逻辑的关键点 #

• T-SQL事务的COMMIT不受客户端读取结果集的影响，批处理执行到COMMIT就完成事务提交；
• SqlTransaction必须显式调用Commit才会提交，只要事务未结束，锁就不会释放，且异常触发的回滚会撤销所有事务内的修改。

注意事项 #

如果需要让SqlTransaction的行为贴近T-SQL事务，需确保在执行完数据修改操作后立即提交事务，再执行SELECT获取结果集（但这样会失去事务的原子性保障），或者调整异常处理逻辑，避免不必要的回滚。

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