October 17, 2013 at 8:55 am
Hugo Kornelis (10/16/2013)
marcia.j.wilson (10/16/2013)
What would be some real-life situations in which you would use nested transactions?I have not seen any practical use cases for direct nesting of transactions in a single code block. But nested transactions can often happen when working with procedures that call each other.
...
Hugo, thanks for the explanation.
October 17, 2013 at 9:29 am
Bhuvnesh (10/17/2013)
L' Eomot Inversé (10/16/2013)
Bhuvnesh (10/16/2013)
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
The thing is that commit doesn't always commit a transaction. At any point in time, the system variable @@trancount for your sql session has a value, which is zero or a positive integer; if it's a positive integer, BEGIN TRAN does nothing but increment it, but if it's zero BEGIN TRAN starts a transaction and increments @@trancount. COMMIT raises an error if it's zero; otherwise it decrements it; if the result of decrementing it is zero it commits the transaction, othewise it does nothing (other than that decrement). This makes it possible to write stored procedures that use explicit transactions without having to worry about calling them when there's already an open transaction. ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
Thanks Tom for explanation.So it means that "Commit" is not playing any role here (above Tsql block) ?
It depends on what happened before the beginning of that block. If there is already a transaction open in the session when the "begin tran" is reached, then that begin tran does nothing but increment a count and the commit does nothing but devcrement that count. But if there isn't already an open transaction when that begin tran is reached, the begin tran starts a transaction and the commit tran commits it (because the begin tran increment takes the count from 0 to 1, which requires a transaction to be started, and the commit tran takes it from 1 to 0, which requires the transaction to be committed) and the rollbackthrows an error because there is no transaction to be rolled back.
Tom
October 31, 2013 at 3:34 am
Really, you like to think complicated ...!
This was a nice question with an essential point on nested transactions,which clarify for beginners this point
expert advices finally lead to never more understand simple and real things
Viewing 3 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply