TransactionScope Prematurely Completed
Solution 1
Don't forget to supress your select statements from your TransactionScope. In SQL Server 2005 and above, even when you use with(nolock), locks are still created on those tables the select touches. Check this out, it shows you how to setup and use TransactionScope.
using(TransactionScope ts = new TransactionScope
{
// db calls here are in the transaction
using(TransactionScope tsSuppressed = new TransactionScope (TransactionScopeOption.Suppress))
{
// all db calls here are now not in the transaction
}
}
Solution 2
I've found that this message can occur when a transaction runs for a longer period than the maxTimeout
for System.Transactions
. It doesn't matter that TransactionOptions.Timeout
is increased, it can't exceed maxTimeout
.
The default value of maxTimeout
is set to 10 minutes and its value can only be modified in the machine.config
Add the following (in the configuration level) to the machine.config
to modify the timeout:
<configuration>
<system.transactions>
<machineSettings maxTimeout="00:30:00" />
</system.transactions>
</configuration>
The machine.config can be found at: %windir%\Microsoft.NET\Framework\[version]\config\machine.config
You can read more about it in this blog post: http://thecodesaysitall.blogspot.se/2012/04/long-running-systemtransactions.html
Solution 3
I can reproduce the problem. It is a transaction timeout.
using (new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 0, 1)))
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (var sqlCommand = connection.CreateCommand())
{
for (int i = 0; i < 10000; i++)
{
sqlCommand.CommandText = "select * from actor";
using (var sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
}
}
}
}
}
}
Throws System.InvalidOperationException
with this message:
The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
To solve the problem make your query run faster or increase the timeout.
Solution 4
If an exception happens inside a TransactionScope
it is rolled back. This means that TransactionScope
is done. You must now call dispose()
on it and start a new transaction. I'm honestly not sure if you can reuse the old TransactionScope
or not, I've never tried, but I'd assume not.
Solution 5
My issue was a stupid one, if you sit on a debug break through the timeout you will get this. Face Palm
Man, programming makes you feel thick some days...
Hungry Beast
Updated on July 08, 2022Comments
-
Hungry Beast almost 2 years
I have a block of code that runs within a TransactionScope and within this block of code I make several calls to the DB. Selects, Updates, Creates, and Deletes, the whole gamut. When I execute my delete I execute it using an extension method of the SqlCommand that will automatically resubmit the query if it deadlocks as this query could potentially hit a deadlock.
I believe the problem occurs when a deadlock is hit and the function tries to resubmit the query. This is the error I receive:
The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
This is the simple code that executes the query (all of the code below executes within the using of the TransactionScope):
using (sqlCommand.Connection = new SqlConnection(ConnectionStrings.App)) { sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQueryWithDeadlockHandling(); }
Here is the extension method that resubmits the deadlocked query:
public static class SqlCommandExtender { private const int DEADLOCK_ERROR = 1205; private const int MAXIMUM_DEADLOCK_RETRIES = 5; private const int SLEEP_INCREMENT = 100; public static void ExecuteNonQueryWithDeadlockHandling(this SqlCommand sqlCommand) { int count = 0; SqlException deadlockException = null; do { if (count > 0) Thread.Sleep(count * SLEEP_INCREMENT); deadlockException = ExecuteNonQuery(sqlCommand); count++; } while (deadlockException != null && count < MAXIMUM_DEADLOCK_RETRIES); if (deadlockException != null) throw deadlockException; } private static SqlException ExecuteNonQuery(SqlCommand sqlCommand) { try { sqlCommand.ExecuteNonQuery(); } catch (SqlException exception) { if (exception.Number == DEADLOCK_ERROR) return exception; throw; } return null; } }
The error occurs on the line:
sqlCommand.ExecuteNonQuery();
-
Hungry Beast almost 14 yearsEven if the exception is caught the transaction is rolled back?
-
Donnie almost 14 yearsI've never experimented with it as for me, exception = error = stop and rollback. However, it seems so from what you're describing.
-
RB Davidson over 12 yearsThanks for the tip on suppressing transactions on select statements. That helped solve a timeout problem that was driving me crazy.
-
Jacob about 12 yearsFantastic answer. That was driving me crazy on a collection of selection/insertion collection of sql instructions. Adding the Suppress option automatically solves the problem.
-
rossipedia over 11 yearsHoly crap thank you! I've been wrestling with this all day. Such a simple solution.
-
Bram Vandenbussche over 11 yearsKeep in mind that the configuration file is case sensitive so that it should be "machineSettings" and "maxTimeout". Too bad you can't override this in you app.config file :(
-
StuartLC about 11 yearsThe overzealous locking is usually a result of the default
Serializable
TS isolation level. This is usually best addressed by wrapping TS creation in a classfactory which sets serialization to something more sane like Read Comitted -
thewhiteambit about 9 yearsThis is false, and is not what happens on exceptions. You also don't have to call Dispose() then. When TransactionScope is generated in a using statement, the using statement will Dispose() the TransactionScope on Exceptions.
-
thewhiteambit about 9 yearsOn Dispose() the TransactionScope will either rollback or commit, depending on weather TransactionScope.Complete() was called or not. That is why Complete() has to be called as the very last thing before ending the using block. You could of course also use a try-finally block an Dispose() by hand. However this does not change anything assumed wrong about Dispose(), Exceptions, Rollback behaviour. Sorry, had to vote down.
-
Shaul Behr almost 9 yearsAlso note that you must put this at the end of the config section, otherwise you will get an error.
-
Ziggler almost 9 yearsI also have the same issue but could understand the answer. I am giving like this. using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
-
John almost 8 yearsI don't get it. What does this has to do with the error message? The linked article is about distributed transactions. And why would you want to suppress anything - how much you lock is for the isolation level to decide.
-
Johan Boulé over 7 yearsI agree with @John, the answer doesn't really nail down the problem.
-
Kiquenet about 7 yearsCan I override this in app.config file ?
-
Kiquenet about 6 yearsDo you can enable/disable programmatically using
BAT or ps1
?