CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15817

First commit; commits two transactions

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

Upvotes: 3

Views: 177

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32230

SQL Server is going to parse that like this:

begin transaction 
    insert into #Test values (1)
    begin transaction 
        insert into #Test values (2)
    commit
    select * from #test
commit
select * from #test

The first select * is within the first transaction. That means it has full access to things already executed previously in that transaction. Statements executed later in the same transaction have access to uncommitted data as though it were already committed.

Upvotes: 3

Related Questions