May 6, 2008 at 4:24 am
Hi,
When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.
Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest
Please let me know the reason for this.
Thanks in advance
Regards,
B. Prakash
To Get Succeeded in Life don't get Changed according to the environment,
Be Honest, Smart & Bold enough to create your own fine environment.
Regards,
B.Prakash.
May 6, 2008 at 5:17 am
Bug in the code =) Transactions for ID's between 1 and 5 never get rolled back.
Need to add the following lines
else
rollback trans
This would make the complete one read:
Create Table #BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into #BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
select 'true', @Id
commit tran
end
else
begin
-- select 'false' , @Id
rollback tran
end
end
else -- new line
rollback tran -- new line
Set @Id = @Id + 1
End
Select * from #BPTest
May 6, 2008 at 5:28 am
I too noticed...
But my question is why that affects the other transactions?
To Get Succeeded in Life don't get Changed according to the environment,
Be Honest, Smart & Bold enough to create your own fine environment.
Regards,
B.Prakash.
May 6, 2008 at 6:34 am
Trace out the flow of begin transactions and commits/rollbacks, or take away the while loop and write it all out as one long series.
May 6, 2008 at 11:26 pm
When the loop is up to @id = 6, you will have 7 pending transactions.
The commit tran will only commit the inner most of these, still leaving 6 'Begin tran' pending.
When the @id = 7, and executes the Rollback, all 7 pending transactions are rolled back.
(Another 'Begin tran' having been added at the top of the loop), including the record of value 6 you were expecting.
As a Commit or Rollback is then performed for every value after 6, each value - 7 , 8, 9 - will either be committed or rolled back. As only 8 modulo 2 is 0, this will be the only value left in the table.
use the @@TRANCOUNT system variable to see how this count is increasing during the loop.
A rollback will roll back ALL transactions.
------
Robert
May 7, 2008 at 12:31 am
Hi rchavil,
Thats a great finding... Thank you very much...
I thought of the transactions which is commited/rollbacked after a proper transaction alone will work... i.e when there is a pending transactions, the one which is commited/rollbacked wont work.
I tested the same with @id < 5. Then when I rollback transaction I found the table gets created again and result has been produced. You would have found I have dropped table at the end. Seems there is a reference in pending transactions which again creats the table to produce result.
Is there any reference for your finding?
Thanks once again.
Regards,
B. Prakash
To Get Succeeded in Life don't get Changed according to the environment,
Be Honest, Smart & Bold enough to create your own fine environment.
Regards,
B.Prakash.
May 7, 2008 at 1:12 am
The roll back for all transactions is documented in the BOL.
Quote
"ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
"
------
Robert
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply