Can I rollback a transaction that has multiple inner transactions in SQL Server?

1
votes

I hear if there are nested transactions, the inner transactions will not be committed.

For example, I have a Parent Procedure and about a handful of children procedures each with begin tran/commit/rollback.

Parent Procedure 
- Inner Procedure1 (has being tran/commit/rollback tran)
- Inner Procedure2 (has being tran/commit/rollback tran)
- Inner Procedure3 (has being tran/commit/rollback tran)
- Inner Procedure4 (has being tran/commit/rollback tran)
- Inner Procedure5 (has being tran/commit/rollback tran)

I need to debug the parent procedure but I need to rollback the transaction at the end instead of committing everything it did. It's a huge procedure.

If I do

    Begin tran 
    - (parent procedure)
      -- Inner Procedure1
      -- Inner Procedure2
      -- Inner Procedure3
      -- Inner Procedure4
      -- Inner Procedure5
    rollback tran

Will everything that happened in all procedures be rolledback assuming no errors anywhere?

1
sqlsql-servertransactions
I usually go here when I can't remember: sqlskills.com/blogs/paul/… - influent
Yes, it will. However if any children SP rollback the tran, the whole tran is rolled back. - PeterHe
Best way to think about things is 'there are no truly 'nested' transactions in SQL Server'... rollback rolls everything back, not just to the last BEGIN TRAN. - pmbAustin

1 Answers

0
votes

Thank you for the replies. It looks like the answer is yes, everything will be rolledback.