Check if a connection is in a transaction

Question

I am getting a SqlConnection does not support parallel transactions. exception and this answer mentions its when a connection tries to open two transactions. This is exactly what i am doing. I thought nested transactions were ok (i was using sqlite for the prototype).

How do i check if the connection is already in a transaction? I am using Microsoft SQL Server Database File.

Answer 1

Are you doing this from multiple threads? If so, then asking won't help because between the time you ask and the time you begin a new transaction, some other thread could have begun its own transaction. You will want to use a connection pool to avoid this sort of race condition.

Answer 2

I think when your code does not know it only a Try/Catch around will do it. What language?

Answer 3

After some searching, I found this other Stack Overflow question. It turns out that you cannot nest transactions in ADO.NET. When you try, you probably end up starting two unrelated transactions, which gives the parallel transactions error.

To see if a connection is currently in a transaction, you could:

var com = yourConnection.CreateCommand();
com.CommandText = "select @@TRANCOUNT";
var trancount = com.ExecuteScalar();

This returns the number of nested transactions.

Note that you can nest transactions manually, without using the SqlTransaction object. For example:

var com = yourConnection.CreateCommand();
com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "INSERT INTO TestTable (name) values ('Joe');";
com.ExecuteNonQuery();
com.CommandText = "COMMIT TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "ROlLBACK TRANSACTION";
com.ExecuteNonQuery();

com.CommandText = "SELECT COUNT(*) FROM TestTable";
Console.WriteLine("Found {0} rows.", com.ExecuteScalar());

This prints 0, because the nested transaction was aborted entirely.