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?

Bigford

Are you using regular SqlConnection/SqlCommmand/SqlDataAdapter or are you using an ORM (such as EF or L2S or other)? And is the code using explicit transactions or TransactionScopes? Also, can you post a code sample around the error that is logging/throwing the exception? – Lucent

Check this answer: #407820 It is about the same problem. – Burglarious

@SimonGoldstone: If I could narrow the problem down I wouldn't ask this question. I'm not asking "my code wouldn't work, please help ASAP", I'm asking what I have to look for in my code. – Bigford

So in order to narrow down the problem, Are you using regular SqlConnection/SqlCommmand/SqlDataAdapter or are you using an ORM (such as EF or L2S or other)? And is the code using explicit transactions or TransactionScopes? – Lucent

@SimonGoldstone: The code uses SQL??? stuff directly without any middleware and it uses SqlTransaction for transactions. – Bigford

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.

Lucent

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.

Extrapolate

This solution solved my problem since I'm using EF Database first model and I used a "non transaction connection" to execute queries to other tables within the transaction. – Puncture

I find that the TransactionScope works really well inside EF too. I haven't used BeginTransaction in ages now. Pretty much all my transaction work is done inside TransactopScopes. – Lucent

Thanks for posting this. I had exactly the same issue. +1 even though it isn't the answer to the OP's particular situation. – Ashore