The transaction operation cannot be performed because there are pending requests working

Question

background

I have some code which opens a sql connection, begins a transaction and performs some operations on the DB. This code creates an object from the DB (dequeue), gets some values and saves it back. The whole operation needs to take place in a transaction. All the code works perfectly without the transaction.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var transaction = connection.BeginTransaction();
    try
    {                       
        var myObject = foo.Dequeue(connection, transaction);

        var url = myObj.GetFilePathUri(connection, transaction);

        //some other code that sets object values

        myObj.SaveMessage(connection, transaction);
        transaction.Commit(); //error here
    }
    catch(Exception ex)
    {                    
        transaction.Rollback();
        //logging                
    }
    finally
    {
        //cleanup code
    }
}

dequeue method code

public foo Dequeue(SqlConnection connection, SqlTransaction transaction)
{
    using (var command = new SqlCommand(DEQUEUE_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
    {
        var reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            ID = (Guid) reader["ID"];
            Name = reader["Name"].ToString();
            return this;
        }
        return null;
    }
}

Get Path Code

public string GetFilePathUri(SqlConnection connection, SqlTransaction transaction)
{
    using (var command = new SqlCommand(FILEPATH_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
    {
        var reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            return reader["Path"].ToString();
        }
        return "";
    }
}

Save Code

public void SaveMessage(SqlConnection connection, SqlTransaction transaction)
{
    using (var command = new SqlCommand(SAVE_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
    {
        command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = ID;
        command.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
        //other object params here
        command.ExecuteNonQuery();
    }
}

The problem

When transaction.Commit() is called, I get the following error:

The transaction operation cannot be performed because there are pending requests working on this transaction.

What am I doing wrong?

EDIT: Quick edit to say I have read the other questions about this problem on SO, but couldn't find any related to ADO.net

Answer 1

I have had this issue before and the problem was the reader needed to be closed. Try this:

public foo Dequeue(SqlConnection connection, SqlTransaction transaction)
{
    using (var command = new SqlCommand(DEQUEUE_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
    {
        var reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            ID = (Guid) reader["ID"];
            Name = reader["Name"].ToString();
            reader.Close();//Closing the reader
            return this;
        }
        return null;
    }
}


public string GetFilePathUri(SqlConnection connection, SqlTransaction    transaction)
{
    string filePathUri = "";
    using (var command = new SqlCommand(FILEPATH_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
    {
        var reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            filePathUri = reader["Path"].ToString();
        }
        reader.Close();//Closing the reader
    }
    return filePathUri;
}

Answer 2

I had this problem when I forgot to use await on an async method that was doing the DB call - the connection was being disposed while the transaction was running because the program was not waiting for the query to complete before trying to dispose everything.

Answer 3

We ran into this problem and while the accepted answer probably would've worked, it wouldn't have been the most correct thing to do -- it did, however, give us a clue as to what we were doing wrong.

In our case, there was more data to read. Here's a simplified version of the problem (using Dapper):

public Foo GetFooById(int fooId, IDbTransaction transaction = null) {
  var sql = @"
    SELECT * FROM dbo.Foo    WHERE FooID = @FooID;
    SELECT * FROM dbo.FooBar WHERE FooID = @FooID;
    SELECT * FROM dbo.FooBaz WHERE FooID = @FooID;
  ";

  using var data = await _connection.QueryMultipleAsync(sql, new { fooId }, transaction);
  var foo = data.ReadSingle<Foo>();
  foo.Bars = data.Read<Bar>();
  // Oops, didn't read FooBaz.
  return foo;
}

Even though data gets disposed, it leaves a "pending request" on the transaction, which then blows up later when transaction.Commit() is called.

The solution is to either read FooBaz records out of data or get rid of the unread SELECT.

Answer 4

For who will come here in the future, I had this issue with a combination of IQueryable and AutoMapper.

I passed an IQueryable for mapping to Mapper.Map<>(myData) by thinking that since it maps data from a class to another, it also materializes it.

Apparently, I was wrong and the issue mentioned in the question was thrown when trying to transaction.Commit().

Solution

As easy as forcing the materialization with a .ToList() in Mapper.Map<>(myData.ToList()).

Just wasted an hour finding that, I hope this will save some time for someone else.