September 6, 2009 at 7:49 am
I have a few questions regarding the atomicity of transactions.
1. In an agent job, is each step atomic? i.e, if step 1 and 2 succeed but step 3 fails, are the updates done by step 1 and 2 rolled back?
2. if you have a nested transaction, which commits, but then the outer transaction is rolled back, is the nested transaction also rolled back? if so, is there a limit on the number of nested transactions you can have?
Thanks for all answers.
September 6, 2009 at 9:04 am
1. In an agent job, is each step atomic? i.e, if step 1 and 2 succeed but step 3 fails, are the updates done by step 1 and 2 rolled back?
each step is atomic. you need to maintain transactions yourself - by default it works in autocommit mode.
2. if you have a nested transaction, which commits, but then the outer transaction is rolled back, is the nested transaction also rolled back? if so, is there a limit on the number of nested transactions you can have?
create table #x1(i int)
go
select * from #x1
begin tran
insert into #x1 (i) values (1)
begin tran
insert into #x1 (i) values (2)
commit
rollback
select * from #x1
September 6, 2009 at 10:18 am
I believe you can nest 32 transactions. Most other nesting options seem to have stack of 32 (http://msdn.microsoft.com/en-us/library/ms143432%28SQL.90%29.aspx)
September 6, 2009 at 11:18 am
Steve try this...
declare @i int
set @i = 0
while (@i < 10000)
begin
set @i = @i + 1
print cast(@i as varchar)
begin tran
end
select @@TRANCOUNT
rollback
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply