Menu
Imagine the following scenario:
I am using SQL Server 2005. I have a transaction that is calling, among other SQL statements, a stored procedure that also has a transaction inside. The outer transaction sometimes fails and it is rolled back after the stored procedure is called and committed successfully.
My question is, does the stored procedure's transaction rollback too?
818
A nested transaction is used to provide a transactional guarantee for a subset of operations performed within the scope of a larger transaction. Doing this allows you to commit and abort the subset of operations independently of the larger transaction.
With a nested transaction, a commit does not write any changes to disk, except for the top level transaction. A rollback, however works regardless of the level of the transaction, so yes, it will roll the inner transaction back.
Advantages of Nested Transactions 1. Nested transactions allow for a simple composition of subtransactions improving modularity of the overall structure. 2. The concurrent execution of subtransactions that follow the prescribed rules allows for enhanced concurrency while preserving consistency.
If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
With a nested transaction, a commit does not write any changes to disk, except for the top level transaction. A rollback, however works regardless of the level of the transaction, so yes, it will roll the inner transaction back.
169
Absolutely yes, the top level transaction will own all the data changes until it is committed or rolled back.
However I would encourage you to think carefully about the transaction model. The more such scenarios exist in your system the greater your exposure to locking issues. Also the computational expense of the procedure increases.
It's remarkable how often, when rationalising SQL, I find transactions have been implemented where they just aren't required. I encourage you (and anyone working with transactions) to think carefully about why you are using them in each context and what would happen were the transaction not implemented. Just my 2c worth!
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
