sql 2000 nested transaction | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sql 2000 nested transaction

hi all
what is the differnce
in these two SP
and what ‘ll be the result?? thankx ****************************************
CREATE PROCEDURE [testproc] ( @error1 numeric=0 output, @error2 numeric =0 output, @returnvalue numeric) AS
begin transaction
insert into person ([name])values(‘outerking’)
begin transaction
select @returnvalue = 100
insert into person ([name])values(‘king’)
select [email protected]@error
select * from person
Select [email protected]@error
select @@error
select @returnvalue = 0
select ‘trancount 1: ‘+ cast(@@trancount as varchar)
rollback
select ‘trancount 2: ‘+ cast(@@trancount as varchar)
commit
select [name] as [after] from person
return @returnvalue
GO
**************************************** ****************************************
CREATE PROCEDURE [testproc1] ( @error1 numeric=0 output, @error2 numeric =0 output, @returnvalue numeric) AS
begin transaction
insert into person ([name])values(‘outerking’)
begin transaction
select @returnvalue = 100
insert into person ([name])values(‘king’)
select [email protected]@error
select * from person
Select [email protected]@error
select @@error
select @returnvalue = 0
select ‘trancount 1: ‘+ cast(@@trancount as varchar)
commit
select ‘trancount 2: ‘+ cast(@@trancount as varchar)
rollback
select [name] as [after] from person
return @returnvalue
GO
****************************************
In the first proc, the second insert is rolled back and the first one is committed, whereas in the second one, both statements will be rolled back. I like to indent any nested statements to make it easier to see what goes with what. eg.
CREATE PROCEDURE [testproc] ( @error1 numeric=0 output, @error2 numeric =0 output, @returnvalue numeric) AS
begin transaction
insert into person ([name])values(‘outerking’)
begin transaction
select @returnvalue = 100
insert into person ([name])values(‘king’)
select [email protected]@error
select * from person
Select [email protected]@error
select @@error
select @returnvalue = 0
select ‘trancount 1: ‘+ cast(@@trancount as varchar)
rollback
select ‘trancount 2: ‘+ cast(@@trancount as varchar)
commit
select [name] as [after] from person
return @returnvalue
GO
First SP will <br />a&gt;begin a transaction setting @@trancount=1<br />b&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />erform action query inserting a record in person table.<br />c&gt;begin a transaction setting @@trancount=2<br />d&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />erform action query inserting a record in person table.<br />e&gt;select @@error as o as no error reported.<br />f&gt;select 2 records from person table which were inserted while begining.<br />g&gt;select @@trancount=2 as two transaction are active.<br />h&gt;rolling back all transactions setting @@trancount=0 i.e. not committing two inserted records.<br />i&gt;selecting @@trancount=0<br />j&gt;commit throws exception setting @@error=err.number set no active @@transaction.<br />k&gt;finally selecting from person all the records except the two inserted from our query.<br /><br /><br />Second SP will <br />a&gt;begin a transaction setting @@trancount=1<br />b&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />erform action query inserting a record in person table.<br />c&gt;begin a transaction setting @@trancount=2<br />d&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />erform action query inserting a record in person table.<br />e&gt;select @@error as 0 as no error reported.<br />f&gt;select 2 records from person table which were inserted while begining.<br />g&gt;select @@trancount=2 as two transaction are active.<br />h&gt;Commiting rthe last active transactions setting @@trancount=1<br />i&gt;selecting @@trancount=1<br />j&gt;Finally rollback active outer transaction forcing inner nested transaction to be rolled back.setting @@transaction=0.<br />k&gt;finally selecting from person all the records except the two inserted from our query.<br /><br />it concludes that in nested transactions if outer fails or rolls back it even rolls back inner transaction where as if inner transaction rolls back or fails it rolls even the outer and not commiting anyone.<br /><br /><br />this is a really good example tripathirohit. it refreshed my logic of nested transaction too.<br /><br /><br /><br /><br /><br />
<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />its clear now<br />thankx<br /><br />
]]>