Hard - Datebaseconnection closure (sometimes) after CommandTimout of 30 seconds

[mbs-systems]

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 ueber 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

[techgnome]

If the connection is closed, you can't initiate the roll val... it's as simple as that... The command timeout shouldn't be closing the connection anyways... are you sure that's what's going on?

Try this... see if it helps...

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = SQLconnection;

myCommand.CommandText = "DELETE FROM tbl_loganalyzer where DATEDIFF(dd,Zeitpunkt, GETDATE())>" + 30;

String retValue;

using (SqlTransaction myTrans = SQLconnection.BeginTransaction()) 
{
  myCommand.Transaction = myTrans;
  try
    {
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      retValue = "SUCCESS!";
    } catch (Exception ex)
    {
       MessageBox.Show("ROLLBACK" + SQLconnection.State);
       retValue = Convert.ToString(ex);
    } finally
    {
       // really, you should check the connection state before blindly closing it...
       SQLconnection.Close();
    }
}
return retValue;

when you use the using with a transaction, unless it has been explicitly Committed, it will automatically rollback when it falls out of scope and is disposed.

-tg

[mbs-systems]

This behaviour is strange as I said:

--------snipped from my initial inquiry above-----------------------------------
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)!
-------------------------------------------

I do not understand why sometimes the command timeout ends up in a Connection close and why sometimes not.
Come back to your question, who do I know that the connection is closed: I have checked after the command timeout
exception the SQLconnection state, which was "Closed" in this case. Then of course the rollback can't work.
But somethimes the connection is still open, independed if the command timeout exception took place (Racecondition?)

You can as well subscribe the ConnectionState event from SQLconnection an raise a checkbox or a console log in case
the connection will be closed. But it is definitely strange that it is mercurial.

Anyway will try your proposal to use the "using"-block. But if there is any explanation, just to get myself satisfied, what is
happening in the SQLconnection close case, it would be great. Many Thanks.

--Maik

[techgnome]

Normally in these kinds of cases I do a couple things... first I generally only use sprocs... but that's another story... the other thing you may want to try is simply extending your command timeout ... give it enough time to do what it needs to do.

-tg

[szlamany]

Set the command timeout for just this delete operation to 0 - which is unlimited.

There is no risk in doing this.

Also - there is no reason to BEGIN TRAN / ROLLBACK on a single DELETE statement. It's already as ATOMIC as possible - it's a single statement.

It's either going to run or it's going to rollback all on it's own without your intervention.

Do not use BEGIN TRAN / ROLLBACK on a single statement - I'm actually suspicious that this might be causing the operation to take longer but I'm hard pressed to explain to myself why I have this suspicion.

[mbs-systems]

Yes, I know. extending the CommandTimeout value to a reasonable value, wherever that value looks like, is a way to solve this issue as well.

But I am anyway interested why we have this kind of contingency that it is
sometimes working and sometimes not. Don't get me wrong, it is just for my
own interest. Thanks.

[szlamany]

You have to check the status of the CONNECTION in the try/catch - you can't just assume that your db condition is still open - as TG said...

...actually - it's bad practice in my opinion to execute more code in a catch - you just got an error so your condition is "unknown" - right? Set some booleans about how to proceed once you leave the TRY / CATCH and then do more logic in further TRY / CATCH's.

Did you see my post about not using the BEGINTRAN / COMMIT on a single statement??

[szlamany]

I've found backup to my suspicion about overhead being added by BEGINTRAN/ROLLBACK - which you should never use on a single SQL statement

http://social.msdn.microsoft.com/For...a-b2df76c911ef

I'm sure I could search further and get more...

Change your code to remove the begintran/rollback - increase the timeout to 0.

Also you should be using more USING statements - and opening your connection later - look at this VB code I've got. It's about as clean as you can get (I believe).

Try
    Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.awc_GetUserName"
            cmd.Connection = dcn
            cmd.CommandTimeout = 0
            DetermineParameters(dcn, cmd)
            cmd.Parameters("@CtrlVal1").Value = ctrlval1
            dcn.Open()
            strMessage = cmd.ExecuteScalar().ToString
            .NewObject("login", "true")
            .Seperate()
            .NewObject("username", "true")
            .Seperate()
        End Using
    End Using
Catch ex As Exception
    strSuccess = ""
    strMessage = ex.Message.Replace("""", "'").Replace("\", "\\")
End Try

No reason to close the connection - it's closed on the END USING automagically. Everything cleaned up on the dispose of the object in whatever fashion the coders of the dispose feel are best - without me having to check status of connection or whether I errored out...

[techgnome]

Cascading deletes? That's where I'm a little fuzzy, I've never really had deletes go bad on me, and I usually do them via sprocs, where I'm rarely deleting from one table anywho, there's always related data... so they're always in transactions within the SProc... the ONLY time I've EVER use the transactionscope object in VB is when we were dealing with data from multiple databases/systems across multiple servers... and then it was to ensure that we had complete transactional control such that if one system failed to update, the other is also rolled back...

-tg

[szlamany]

The only time to use client-side transaction management is when you are doing more than a single ATOMIC DML statement and want to rollback in the event that one does not complete properly. That's if you use INLINE SQL from the client...

If you are using SPROCS you NEVER use client-side transaction management. There would be no point - as the SPROC becomes the ATOMIC container.

Even cascading delete's are ATOMIC as far as the DML statement - let the engine put the markers in the transaction log based on what it wants to do - no reason to micromanage that from the client side.

[techgnome]

In our case we were using a Distributed Transaction scenario... because we were crossing physical server boundaries. But I should point out NOT FROM the client... all of those DTs were initiated by the application server... all the client does was to initiate the call over a webservice to the app server... the app server would then create the DT and run the appropriate sprocs in each server/database... that system was a monster... it had its fingers in the data of a dozen other systems... and then it had to play ringleader from time to time synching the data from one system with the data in the other, and of course, because of architectual differences, the data isn't easily copied from one to the other... one had historical auding, while the other had what I call hysterical auditing (in other words, the "auditing" was laughable because the history of the data isn't maintained... and yet, the user needs to be able to roll back changes... sigh.. that was fun) ...

"If you are using SPROCS you NEVER use client-side transaction management. There would be no point - as the SPROC becomes the ATOMIC container." -- I agree... and 75&#37; of the time, that's how we operated... but when you've got one foot in System A on Server X and the other foot in System B on Server Y ... and you're doing transactional updates... AND potentially on a sat connection... that "client" transaction becomes very important... but again, we NEVER instanciated the DTs from the client, but from the app server, and ONLY in those cases where we were updating data and straddling both servers.

-tg