November 10, 2009 at 12:46 am
Can you run transactions inside a transactions..you should be right.. nested transaction?
I have to kick off a stored procedure that has a transaction in it, so i just want to make sure if I commit a transaction within a transaction it will still rollback that transaction and rollback all it.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
November 10, 2009 at 2:23 am
For some reason nested transactions is one of the most confusing subjects. There are few rules that you need to understand and then the whole subject will be clear. When you start a session SQL Server keeps track on your transactions count with a system variable that is called @@trancount. Under the default settings, it is set to zero which means that you are not inside of a transaction. When you issue a begin transaction statement, SQL Server adds 1 to the variable @@trancount. Each begin transaction statement adds 1 to @@trancount (so if you ran it twice the value of @@trancount will be two). When you run the statement commit transaction, SQL Server subtracts 1 from @@trancount. When you run the statements rollback transaction, SQL server resets the variable @@trancount to zero (unless you are working with save points which are a different story). SQL Server determines if to do data modifications only when the value of @@trancount is zero. This means that if in my code I had 2 begin transaction statements, and then one commit transaction, the inner transaction didn’t get commited, because the value of @@trancount is now set to 1 (First begin tran set it to one. Second begin tran set it to two. First commit tran set it to one again, but since we are still in the middle of transaction the data is not getting commited). Just to clarify take a look at this code:
Begin tran
Update mytable set mycol = mycol + 1
Begin tran
Update MyTbl2 set MyCol2 = MyCol1
Commit tran
Rollback tran
In this code the first commit has no effect on the data because after it was executed the value of @@trancount is one. After the rollback tran is executed the value of @@trancount is zero, so the whole transaction (including the inner part) is rolled back. I could change the last statement to commit transaction and this also would change the value of @@trancount to 0 and would commit the whole transaction.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2009 at 2:29 am
Excellent reply Ten Centuries. That makes it very clear for me. Thanks!
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply