TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Maik Brauer
NA
12
43.2k
Hard - Datebaseconnection closure (sometimes) after CommandTimout of 30 seconds
Feb 10 2012 3:48 AM
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
Reply
Answers (
2
)
SQL statement for getting all columns of table
Creating a custom search stored procedure