1

SQL Server 2008 R2 supports Nested transaction but I am using SQL Server 2008 Standard Edition. Please let me know whether it supports nested transaction?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Phoenix
  • 43
  • 5

1 Answers1

4

No version of SQL Server supports nested transactions. It appears to, but it doesn't

Why?

  • ROLLBACK can rollback all transactions
  • Some errors "doom" the transaction stack
  • SET XACT_ABORT ON does an implicit rollback

So even with savepoints and named transactions, you still can't truly nest. For example, the links below invalidate this pattern with savepoints: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/ . I'd hate to rely on this because you really need SET XACT_ABORT ON in SQL Server

Hence my answer here to enable stored proc nesting: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

This links onto the authoritative Paul S. Randal: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676