SqlExceptions and Rolling Back Transactions

[Cole Shelton]

Hi all,

I have a question about transactions and exceptions. I wrote some code that
would handle an exception and rollback a transaction. e.g.

SqlTransaction tran = null;
try {
tran = conn.BeginTran();
// Execute Update, Inserts etc..
tran.Commit();
}
Catch(Exception e)
{
tran.Rollback();
}

However, when a SqlException occurs, i get an exception on the rollback line
saying that no corresponding transaction can be found. I am assuming that a
SqlException automatically causes the Transaction to Rollback? Is this
Correct?

So, if that is the case, I will want to only do a rollback when I catch a
non-SqlException?

Please let me know how you guys handle these situations.

Thanks
Cole

 

[William Ryan]

If you get a SQL Exception, in many cases the statement never executed in
the first place, for instance, if you got the table name wrong or column
name, you'd never execute anything. So for example, if I had only one
statement and it blew up, there would be nothign to roll back. With that
said, your strategy for exception handling depends on your specific
situation. If you are catching Exception e, you are catching everything
that could happen, from OutOfMemoryException to NullReference. Either way,
I'm guessing your logic is such that you'll want to roll back the
transaction if any of it fails. There could be a case where you'd want to
trap specific things or just shoot for the general exc handler. Overall your
code looks Kosher, but depending on what's causing the problem , you may
have to handle it a few ways. BTW, have you done a Debug.Assert(conn.State
== connectionstate.Open)? That could also be part of the problem.

You may also want to consider wrapping this all in a stored proc if your
tasks are complex and handling everything there...

HTH,

Bill

 

[Angel Saenz-Badillos[MS]]

The following error:
'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

Is a known bug with, here is the KB article :
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309335
Basically the Server is rolling back the transaction after getting a very
bad exception (severity 16 or higher I believe) and the client is trying to
Rollback a transaction that has already been terminated. You can check the
@@TranCount to verify that the transaction is no longer active in your
scenario.

Angel