c# - SELECT 似乎阻止了 UPDATE 语句

Question

在 C# 方法中，我执行以下返回多行的 SQL 查询:

SELECT [Data], [Version] 
FROM [dbo].[Table] 
WHERE [Id]=@uniqueId AND [ReferenceId] IS NULL 
ORDER BY [Version] Asc

然后我迭代结果并调用一个应该更新表的方法:

while (sqlDataReader.Read())
{
    SqlBytes data = sqlDataReader.GetSqlBytes(0);
    SqlInt64 version = sqlDataReader.GetSqlInt64(1);

    UpdateReference(data, version);
}


UpdateReference(data, version)
{
    // do database unrelated stuff with data

    UPDATE [dbo].[Table] 
    SET [dbo].[Table].[ReferenceId]=..., [dbo].[Table].[Data]=...
    WHERE [dbo].[Table].[Id]=@uniqueId AND [dbo].[Table].[Version]=@version
}

有一段时间这工作正常，但突然(在同一张表上执行一些 SELECT ... INNER JOIN 查询之后)停止了。我在第一个 SELECT 上创建了一个事务范围(使用与调用 UpdateReference() 相同的方法):

 using (TransactionScope scope = new TransactionScope())
    SELECT ...
    while (sqlDataReader.Read()) ... UpdateReference();

我得到以下异常:

The transaction has aborted.

如果我删除事务范围，则在调用 UPDATE 一段时间后会发生超时异常:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

但这似乎不是 SQL Server 的问题。同样奇怪的是，对于某些记录，不存在此类问题 - 它们仅在对某些表记录使用第一个 SELECT 时才会出现。

这是我到目前为止发现的:

• 如果我(通过代码)独立执行查询，一切正常；
• 如果我在 SQL Management Studio 中独立执行这两个查询，它们将按预期工作

一个似乎可行的解决方案(目前？)是将第一个查询的结果存储到列表中，然后在 SELECT 完成后调用列表元素的更新:

List<long> versionList = new List<long>();     
List<byte[]> dataList = new List<byte[]>();   

using (TransactionScope scope = new TransactionScope())
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();                    

        // Execute SELECT ...
        using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection))
        {
            ...

            using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
            {                                       
                while (sqlDataReader.Read())
                {
                    SqlBytes data = sqlDataReader.GetSqlBytes(0);
                    SqlInt64 version = sqlDataReader.GetSqlInt64(1);

                    // Store result to lists
                    versionList.Add(version.Value);             
                    dataList.Add((byte[])data.ToSqlBinary(););
                }
            }
        }       
    }   

    // Everything works as expected if this loop is placed here; but if it is placed within the above SqlConnection using clause, an exception is thrown:
    // "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configurationfor MSDTC using the Component Services Administrative tool."
    for (int i = 0; i < versionList.Count; i++)
    {
       UpdateReference(dataList[i], versionList[i]);
    }

    scope.Complete();
}

我不确定这个解决方案是否有任何好处(除了使用比最佳内存更多的内存)或者它可能导致的其他潜在问题。如果您能深入了解这里发生的事情以及如何最好地解决它，我将不胜感激。

更新 1

为了清楚起见，我是这样解决问题的:

1. 在 TransactionScope 之外执行 SELECT，将结果存储到列表中；

2. 迭代这些列表并将它们的内容提供给 UPDATE，即 包含在 TransactionScope 中

随时批评/改进此解决方案:

Method1()
{
    List<long> versionList = new List<long>();     
    List<byte[]> dataList = new List<byte[]>();   

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();                    

        // Execute SELECT ...
        using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection))
        {
            ...

            using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
            {                                       
                while (sqlDataReader.Read())
                {
                    SqlBytes data = sqlDataReader.GetSqlBytes(0);
                    SqlInt64 version = sqlDataReader.GetSqlInt64(1);

                    // Store result to lists
                    versionList.Add(version.Value);             
                    data.Add((byte[])data.ToSqlBinary());
                }
            }
        }

        // Call update
        for (int i = 0; i < versionList.Count; i++)
        {
            UpdateReference(dataList[i], versionList[i]);       
        }   
    }   
}

UpdateReference(data, version)
{
    ...

    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
            connection.Open();

            UPDATE [dbo].[Table] 
            SET [dbo].[Table].[ReferenceId]=..., [dbo].[Table].[Data]=...
            WHERE [dbo].[Table].[Id]=... AND [dbo].[Table].[Version]=@version
        }

        scope.Complete();
    }
}

Answer 1

是的，select 通常需要锁；在查询本身期间，为了稳定性；但是如果有一个事务(取决于隔离级别)，这些锁可以在查询整个事务后保持不变；特别是 key 范围锁。当然，同一事务中的代码不会受到这些锁的不利影响。特别重要的是确切您的连接是在哪里创建的，以及您正在使用多少:

• 如果连接是在该事务中创建和打开，则该连接将仅在环境事务中自动登记；如果您打开一个连接然后创建一个环境事务，该连接不会自动征用
• 如果您在事务范围内有一个连接，它通常会使用 LTM；如果您使用多个连接实例，它通常只会升级为 DTC； DTC 在网络上的配置有点繁琐(dtcping 可以提供帮助)
• 在您的情况下，您希望同时有一个读取器和一个执行器；我怀疑目前您正在使用多个连接进行此操作；另一种选择是启用 MARS，这将允许您在单个连接上执行这两项操作

但是!就个人而言，我怀疑在你的情况下最简单的选择是首先在事务的外部进行查询并进入列表(或类似列表) - 即不是懒惰假脱机。 然后开始工作，并应用任何更新。如果可能，我会尽量避免跨越数百/数千个单独命令的单个事务 - 如果您可以批处理该工作，那将是更可取的。