InvalidOperationException when executing SqlCommand with transaction
- by Serhat Özgel
I have this code, running parallel in two separate threads. It works fine for a few times, but at some random point it throws InvalidOperationException:
The transaction is either not associated with the current connection or has been completed.
At the point of exception, I am looking inside the transaction with visual studio and verify its connection is set normally. Also command.Transaction._internalTransaction. _transactionState is set to Active and IsZombied property is set to false.
This is a test application and I am using Thread.Sleep for creating longer transactions and causing overlaps.
Why may the exception being thrown and what can I do about it?
IDbCommand command = new SqlCommand("Select * From INFO");
IDbConnection connection = new SqlConnection(connectionString);
command.Connection = connection;
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery(); // Sometimes throws exception
Thread.Sleep(forawhile); // For overlapping transactions running in parallel
transaction.Commit();
}
catch (ApplicationException exception)
{
if (transaction != null)
{
transaction.Rollback();
}
}
finally
{
connection.Close();
}
