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.