SELECT seems to be blocking UPDATE statements

Question

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

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()):

 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:

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.

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

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:

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

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

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

2. iterate these lists and feed their content to UPDATE, which is enclosed in a 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();
    }
}

Answer 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:

• 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)
• 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

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.