When does "SqlConnection does not support parallel transactions" happen?

Question

I have a ton of rather working code that's been here for months and today I saw the following exception logged:

System.InvalidOperationException
SqlConnection does not support parallel transactions.
    at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(
       IsolationLevel iso, String transactionName)
    at System.Data.SqlClient.SqlConnection.BeginTransaction(
       IsolationLevel iso, String transactionName)
    at my code here

and I'd like to investigate why this exception was thrown. I've read MSDN description of BeginTransaction() and all it says is that well, sometimes this exception can be thrown.

What does this exception mean exactly? What is the deficiency in my code that I should be looking for?

Answer 1

You'll get this if the connection already has an uncommitted transaction and you call BeginTransaction again.

In this example:

class Program
{
    static void Main(string[] args)
    {
        using (SqlConnection conn = new SqlConnection("Server=.;Database=TestDb;Trusted_Connection=True;"))
        {
            conn.Open();

            using (var tran = conn.BeginTransaction())
            {
                using (var cmd = new SqlCommand("INSERT INTO TESTTABLE (test) values ('" + DateTime.Now.ToString() + "')", conn))
                {
                    cmd.Transaction = tran;
                    cmd.ExecuteNonQuery();
                }

                using (var tran2 = conn.BeginTransaction())    // <-- EXCEPTION HERE
                {
                    using (var cmd = new SqlCommand("INSERT INTO TESTTABLE (test) values ('INSIDE" + DateTime.Now.ToString() + "')", conn))
                    {
                        cmd.Transaction = tran2;
                        cmd.ExecuteNonQuery();
                    }

                    tran2.Commit();
                }

                tran.Commit();
            }
        }
    }
}

... I get exactly the same exception at the second BeginTransaction.

Make sure the first transaction is committed or rolled back before the next one.

If you want nested transactions, you might find TransactionScope is the way forward.

Answer 2

The same problem occurs when using the 'wrong' method for a transaction, this happened after we upgraded to a newer version of the Entity Framework.

In the past we were using the following method to create a transaction and mixed EF strong typed linq queries with Sql queries, but since the Connection property did not exist anymore, we replaced all db. with db.Database, which was wrong:

// previous code
db.Connection.Open();
using (var transaction = db.Connection.BeginTransaction())
{
    // do stuff inside transaction
}
// changed to the following WRONG code
db.Database.Connection.Open();
using (var transaction = db.Database.Connection.BeginTransaction())
{
    // do stuff inside transaction
}

Somewhere they changed the behaviour of that transaction method behaviour with a newer version of the Entity Framework and the solution is to use:

db.Database.Connection.Open();
using (var transaction = db.Database.BeginTransaction())
{
    // do stuff inside transaction
}

Notice that the transaction is now callen on Database instead of Connection.