Please see the code below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE TestProc
AS
BEGIN
begin try
SET NOCOUNT ON;
--SET XACT_ABORT on;
set transaction isolation level read committed
create table #Test (id int)
begin transaction
insert into #Test values (1)
begin transaction
insert into #Test values (2)
commit
select * from #test
commit
select * from #test
end try
begin catch
print 'got here 1'
end catch
END
GO
which is called by this:
begin try
exec TestProc
end try
begin catch
print @@TRANCOUNT
end catch
The output is:
table 1:
1
2
table 2:
1
2
Why does the first commit; commit both transactions? Please see this link: http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling, which says: "No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.". I would expect the first table to contain one row only:
table 1
1