Rollback at stored procedure vs rollback at C# code

Question

I have a procedure Proc1 that calls another procedure Proc2, and both with "BEGIN TRANSCTION" If i get an error at the second procedure, it will rollback both procedures. OK!

But inside my C# code, i have also this

...
   dbCommand.Transaction.Commit();
                dbCnn.Close();
            }
            catch (Exception ex)
            {
                if (dbCommand.Transaction != null)
                    **dbCommand.Transaction.Rollback();**
                if (dbCnn.State == ConnectionState.Open)
                    dbCnn.Close();

                throw ex;
            }

Does "dbCommand.Transaction.Rollback();" knows which transaction I am talking about? And if I have two transactions ate the same procedure, without label, will it be a problem for C# rollback the right transaction?

There is no such thing as "two transactions" - you can BEGIN TRANSACTION all you like, and you can get @@TRANCOUNT to be 500 if you like, but if you perform one ROLLBACK, they all roll back. Personally, I keep the transaction control inside the stored procedure - I have seen very few cases where the C# app needs to get involved with that at all. — Aaron Bertrand

TomTom TomTom · Accepted Answer · 2014-10-30T13:16:31

Does "dbCommand.Transaction.Rollback();" knows which transaction I am talking about?

No. It also does not care. It rolls back the transaction that is wrapping everything on connection level. Everything inside is part of this transaction.

And if I have two transactions ate the same procedure, without label, will it be a problem for C# rollback the right transaction?

You can not have two transactions. Transactions form a hierarchy. The code above rolls back the whole top level connection level transaction. Every other transaction you open in a sstored procedure is part of this transaction.

Rollback at stored procedure vs rollback at C# code

2 Answers