SELECT似乎阻塞了更新语句

Within a C# method, I execute the following SQL query that returns multiple rows:

在c#方法中，我执行以下SQL查询，该查询返回多个行:

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

I then iterate the results and call a method that is supposed to update the table:

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

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
}

For a while this worked fine, but suddenly (after executing some SELECT ... INNER JOIN queries on the same table) stopped. I create a transaction scope over the first SELECT (in the same method that calls UpdateReference()):

在一段时间内，这个方法运行得很好，但是突然(执行了一些SELECT之后……)同一表上的内部连接查询)停止。我在第一个SELECT(在调用UpdateReference())的同一方法中创建一个事务范围:

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

I get the following exception:

我有以下例外:

The transaction has aborted.

事务已中止。

If I remove the the transaction scope, timeout exception occurs after some time while calling UPDATE:

如果我删除事务范围，在调用UPDATE时超时会发生:

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

超时过期。在操作完成之前或服务器没有响应的超时时间。

But this doesn't seem to be an SQL Server problem. What is also odd is that for some records, there are no such problems - they only occur when the first SELECT is used on certain table records.

但这似乎不是SQL服务器的问题。同样奇怪的是，对于某些记录来说，不存在这样的问题——它们只出现在某些表记录上使用第一个SELECT时。

Here's what I found out so far:

以下是我到目前为止发现的:

• if I execute the queries (from code) independently, everything works fine;
• 如果我独立地执行查询(来自代码)，一切都会正常工作;
• both queries work as expected if I execute them independently in SQL Management Studio
• 如果我在SQL Management Studio中独立地执行这两个查询，那么它们都可以正常工作

One solution that seems to work (for now?) is to store results of the first query to lists, then call update on list elements after SELECT has finished:

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

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();
}

I'm not sure if this solution is any good (besides using more memory than optimal) or what other potential problems it may cause. I would be grateful for any insight into what is going on here and how best to resolve it.

我不确定这个解决方案是否有什么好处(除了使用更多的内存而不是最优内存)，也不确定它可能导致什么其他潜在问题。我将感激对这里发生的一切以及如何最好地解决它的任何见解。

UPDATE 1

更新1

For clarity sake, this is how I fixed the problem:

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

1. execute SELECT outside TransactionScope, store results into lists;

   执行SELECT out TransactionScope，将结果存储到列表中;

2. iterate these lists and feed their content to UPDATE, which is enclosed in a TransactionScope

   迭代这些列表，并将其内容提供给UPDATE，该内容包含在TransactionScope中

Feel free to criticize/improve this solution:

请随意批评/改进这个解决方案:

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();
    }
}

1 个解决方案

#1

Yes, select usually takes locks; during the query itself, for stability; but if there is a transaction (depending on the isolation level), those locks can persist after the query for the entire transaction; key-range locks in particular. Of course, code in the same transaction won't be adversely impacted by these locks. Of particular importance is where exactly your connections are created opened, and how many you are using:

是的，select通常使用锁;在查询过程中，为了稳定;但是，如果存在一个事务(取决于隔离级别)，这些锁可以在查询整个事务之后继续存在;尤其是键范围锁。当然，相同事务中的代码不会受到这些锁的不利影响。特别重要的是，你的连接是在哪里被打开的，以及你使用了多少连接:

• a connection will only auto-enlist in the ambient transaction if it is created and opened inside that transaction; if you open a connection then create an ambient transaction, the connection does not auto-enlist
• 只有在环境事务中创建并在该事务中打开连接时，该连接才会自动登记;如果您打开一个连接，然后创建一个环境事务，该连接不会自动登记
• if you have a single connection inside a transaction scope, it will usually use the LTM; it typically only escalates to DTC if you use more than one connection instance; DTC is a bit fiddly to configure on the network (dtcping can help)
• 如果事务范围内只有一个连接，则通常使用LTM;它通常只在使用多个连接实例时升级到DTC;在网络上配置DTC有点麻烦(dtcping可能有帮助)
• in your case, you want a reader and an execute at the same time; I suspect that at the moment you are doing this using multiple connections; one other option is to enable MARS which will allow you to do both operations on a single connection
• 在你的情况下，你想要一个读者和一个执行同时;我怀疑此时你是在使用多个连接;另一个选择是启用MARS，这将允许您在一个连接上同时执行两个操作

However! Personally, I suspect the simplest option in your case is to do the query first outside of the transaction and into a list (or similar) - i.e. not lazily spooling. Then do the work, and apply any updates. If possible, I would try to avoid a single transaction that spans hundreds/thousands of separate commands - if you can possibly batch that work, that would be preferable.

然而!我个人认为，在您的案例中，最简单的选项是首先在事务之外执行查询，然后将其放入一个列表(或类似的列表)——即不延迟假脱机。然后进行工作，并应用任何更新。如果可能的话，我将尽量避免一个事务跨成百上千个不同的命令—如果您可以批量处理该事务，那将是更好的选择。