Menu
I have never understood what a nested transaction is good for. Committing a nested transaction commits nothing - it just decreases @@TRANCOUNT. And ROLLBACK rollbacks everything.
BEGIN TRANSACTION
//do an update
BEGIN TRANSACTION
//do an insert
COMMIT TRANSACTION
COMMIT TRANSACTION
What is the difference with this:
BEGIN TRANSACTION
//do an update
//do an insert
COMMIT TRANSACTION
Please give me an example why should nested transactions be used and how they make a difference.
325
Like top-level transactions, nested transactions have the following properties: A transaction is serializable with respect to its siblings, that is, accesses to shared resources by sibling transactions have to obey the read-write and write-write synchronization rules.
Definition. Multilevel transactions are a variant of nested transactions where nodes in a transaction tree correspond to executions of operations at particular levels of abstraction in a layered system architecture.
The primary benefit of using transactions is data integrity. Many database uses require storing data to multiple tables, or multiple rows to the same table in order to maintain a consistent data set. Using transactions ensures that other connections to the same database see either all the updates or none of them.
Oracle doesn't support nested transactions. If a transaction commits, it commits. That's why you generally don't want to commit (or rollback) a transaction in a stored procedure, that makes it difficult to reuse the procedure elsewhere if your transaction semantics differ.
Nested transactions allows your code to call other code (SPs for instance) which uses transactions itself without actually committing your transaction when they commit.
That said, you can use safepoints to roll back inside of a transaction.
There's a CodeProject article dedicated to that.
68
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
