Menu
Does sql server allow nested transactions? If so then whats the priority of transactions?
949
It is also possible to put one transaction within another transaction in SQL Server and when we do so, it is called a nested transaction. We are going to use the following Customer table to understand the nested transaction in SQL Server. Please use the below SQL Script to create the Customer table.
The rules to the usage of a nested transaction are as follows: While the nested (child) transaction is active, the parent transaction may not perform any operations other than to commit or abort, or to create more child transactions. Committing a nested transaction has no effect on the state of the parent transaction.
A nested transaction is a database transaction that is started by an instruction within the scope of an already started transaction. Nested transactions are implemented differently in different databases.
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.
From the MSDN documentation on SQL Server. Nesting Transactions:
Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. 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.
151
Just to qualify this and directly answer the question, yes SQL Server does allow the nesting of transactions.
The quoted reference kindly provided by ichiban, details that the outcome of outermost transaction will determine whether or not nested transactions are committed.
28
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
