SQLTransaction Commit method

[Jay]

I'm currently debugging an application. Unfortunately, it is one of those
problems that happens sporadically, and I haven't found a reliable way to
reproduce it.

The basic structure of my code is:

try
{
// execute some update stored procedures

sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
// throw exception to indicate to higher level code that an error
occurred.
}

When the problem occurs, the symptom I see is that my data is saved, but a
rollback is attempted anyway, and an InvalidOperationException (this
SQLTransaction has completed; it is no longer usable) is thrown. So, I'm
trying to understand how my Commit could execute (the last line of my try
block), yet my catch block get executed.

Is it possible for the database to commit a transaction successfully, but an
exception still be thrown by the SQLTransaction Commit method?

The error seems to occur when the database has a heavy load. For example,
could the commit method timeout (the database executes the commit, but word
doesn't get back to ADO.NET before the timeout expires)?

 

[W.G.Ryan]

That code looks solid, hard to imagine what's happening. Since you can't
predict when it happens, I'd consider wrapping the Commit in a more precise
handler. The basic thinking with the general catch is that no matter what
goes wrong, roll back the transaction. But if you are trying to debug it,
I'd wrap it in a SQL/OLEdbException first. Is there Nothing after the
Commit statement? I'm guessing there isn't, but this is sure a weird
problem.

 

[Nemachtiani]

Same behaviour

I know its been long time since Jay post it but, i got this uncommon behaviour recently.

My code has something after the Commit and it seems like the Commit itself is throwing an exception because the next line after it didn't run.

Besides this, i have seen that the commit really does a commit because i got the info in the database consisten, as if no exception were thrown, a partner told me that the info could not been registered, but i haven`t seen this scenario, usually the info is there and is consistent.

Our code does the sqlConnection.Open and sqlTransaction instance outside the Try catch block, the Commit is inside the try block, the RollBack in the catch block.

We see this behavior usually happens when loading big amounts of data and as we got Replications we guess this is making the sqlTransaction instance loose the connection, we saw with a Net reflector that the sqlTrasaction Commit method loose the connection it throws the "This sqlTransaction has completed, is not longer usable".

This are all we got, hope can find a way to get over it.

cheers