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.

Does the transaction span multiple databases on the one server? — Chris Bednarski, May 18 '10 at 10:13
.NET and no. Its just a connection with a nested transaction. The nested is required and the outer is there for performance reasons (1 vs 25 transaction). — , May 18 '10 at 11:12

score 8 · Accepted Answer · edited May 23 '17 at 10:34

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.

score 2 · Answer 2 · answered May 18 '10 at 09:40

2

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.

answered May 18 '10 at 09:40

Greg Hewgill

951,095
183
1,149
1,285

Each thread has its own connection. I call a function which begins a transaction then in a loop i call another function which also begins a transaction. The subfunction is called all the time and almost everywhere. The first one not so much. – May 18 '10 at 09:44
@acidzombie24: Then you have two general approaches: (1) create and maintain multiple connections per thread, or (2) use a connection pool. Connection pools are a good thing. – Greg Hewgill May 18 '10 at 09:47
1) IS already being done and i am unsure what 2 is but looking it up it looks like i am doing that too. I need a way for my nested transaction (made on purpose) to not bother beginning another. I use the subfunc all the time and it needs a transaction. The one above it does not but performance (sqlite) was better with a transaction there. Thus why i purposely have one connection with two/nested transactions – May 18 '10 at 09:56
@acidzombie24: If your program is constructed such that you know when you already have a transaction open and don't need to open another one in the subfunction, then just pass a flag to the subfunction that means "don't open a transaction this time". – Greg Hewgill May 18 '10 at 10:11
Greg: I was thinking thats what i need to do but was hoping i wouldnt need to do it. (I like how sqlite has that built in). I guess if thats what i need to do i'll do it. – May 18 '10 at 10:16

score 0 · Answer 3 · answered Oct 27 '16 at 18:35

You can check whether a transaction is already open by checking if cmd.Transaction is null.

Then wrap your code accordingly so if a transaction is already open, then the calling function owns that transaction and will commit it / roll it back appropriately.

//begin transaction unless one was already started
bool newTransaction = cmd.Transaction == null;
if (newTransaction) cmd.Transaction = cmd.Connection.BeginTransaction();

try {
    // do Stuff Here
    cmd.ExecuteNonQuery();
    cmd.ExecuteNonQuery();

    // commit if it's our to commit
    if (newTransaction) cmd.Transaction.Commit();

} catch (SqlException ex) {
    if (newTransaction && cmd.Transaction != null) cmd.Transaction.Rollback();
    throw;
}

Then the parent function can pass in a command and optionally choose to begin it's own transactional block, or if not, one will be created and commited by the called function.

Check if a connection is in a transaction

3 Answers3

Linked