Hard - Datebaseconnection closure (sometimes) after CommandTimout of 30 seconds
After a delete query has been send via sqlconnection in C# which will take a while due to the fact that there are a lot
of rows to delete, it will close the database connection sometimes after reaching the command timeout before the rollback can take place.
So the default value of the command timout, when not defined is 30 seconds.
Sometimes the Rollback is working when the CommandTimeout happened because the Connection is still open. And sometimes this timeout has closed
the db connection somehow, therefore the rollback will run into an error. (Zombiecheck)!
Here the Code which will be executed:
SqlConnection SQLconnection = new SqlConnection(<MySQConnectionString>);
SQLconnection.Open();
SqlTransaction myTrans = SQLconnection.BeginTransaction();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = SQLconnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "DELETE FROM tbl_loganalyzer where DATEDIFF(dd,Zeitpunkt, GETDATE())>" + 30;
myCommand.ExecuteNonQuery();
myTrans.Commit();
return "SUCCESS";
}
catch (Exception ex)
{
MessageBox.Show("ROLLBACK" + SQLconnection.State);
myTrans.Rollback();
return Convert.ToString(ex);
}
finally
{
SQLconnection.Close();
}
This is the Exception which will not be catched in the Try Catch Block:
Informationen ¸ber das Aufrufen von JIT-Debuggen
anstelle dieses Dialogfelds finden Sie am Ende dieser Meldung.
************** Ausnahmetext **************
System.InvalidOperationException: SqlTransaction wurde beendet und kann nicht mehr verwendet werden.
bei System.Data.SqlClient.SqlTransaction.ZombieCheck()
bei System.Data.SqlClient.SqlTransaction.Rollback()
Does anybody know why this is happening, and why it is working sometimes? Are there any race conditions internally?
Is this a know issue? How can it be solved, instead of increasing the command timeout value?
Cheers,
Maik