question:

if the nested transaction encountered an exception, then rollbacked. How about the outer transaction?

Lets demo this using below code.

create procedure sp1BEGIN BEGIN TRANSACTION...exec sp2COMMIT END

Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception? 

Answer:

The outer transaction will be rollbacked as well.

There are no autonomous transactions in SQL Server. You may see @@TRANCOUNT increase beyond 1, but a rollback affects the whole thing.

 

Outer proc:

CREATE PROCEDURE dbo.sp1    @trip BITASBEGIN    SET NOCOUNT ON;    BEGIN TRANSACTION;    PRINT @@TRANCOUNT;    BEGIN TRY        EXEC dbo.sp2 @trip = @trip;    END TRY    BEGIN CATCH        PRINT ERROR_MESSAGE();    END CATCH    PRINT @@TRANCOUNT;    IF @@TRANCOUNT > 0        COMMIT TRANSACTION;    PRINT @@TRANCOUNT;ENDGO

 

Inner proc:

CREATE PROCEDURE dbo.sp2    @trip BITASBEGIN    SET NOCOUNT ON;    BEGIN TRANSACTION;    PRINT @@TRANCOUNT;    IF @trip = 1    BEGIN        IF @@TRANCOUNT > 0            ROLLBACK TRANSACTION;    END    ELSE    BEGIN           IF @@TRANCOUNT > 0            COMMIT TRANSACTION;    END    PRINT @@TRANCOUNT;ENDGO

 

So now let's call it and let everything commit:

EXEC dbo.sp1 @trip = 0;

Results:

1

2

1

1

0

Now let's call it and roll back the inner procedure:

EXEC dbo.sp1 @trip = 1;

Results:

1

2

0 <-- notice that a rollback here rolled back both

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

0

0

 

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

The following example shows the intended use of nested transactions. The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

 

 

SET QUOTED_IDENTIFIER OFF;GOSET NOCOUNT OFF;GOUSE AdventureWorks2008R2;GOCREATE TABLE TestTrans(Cola INT PRIMARY KEY,               Colb CHAR(3) NOT NULL);GOCREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) ASBEGIN TRANSACTION InProcINSERT INTO TestTrans VALUES (@PriKey, @CharCol)INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)COMMIT TRANSACTION InProc;GO/* Start a transaction and execute TransProc. */BEGIN TRANSACTION OutOfProc;GOEXEC TransProc 1, 'aaa';GO/* Roll back the outer transaction, this will   roll back TransProc's nested transaction. */ROLLBACK TRANSACTION OutOfProc;GOEXECUTE TransProc 3,'bbb';GO/* The following SELECT statement shows only rows 3 and 4 are    still in the table. This indicates that the commit   of the inner transaction from the first EXECUTE statement of   TransProc was overridden by the subsequent rollback. */SELECT * FROM TestTrans;GO

 

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_namestatement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0, you are not in a transaction.

Reference

 

整理自

http://technet.microsoft.com/en-us/library/ms189336%28v=sql.105%29.aspx

http://stackoverflow.com/questions/9692734/sql-server-nested-transactions-in-a-stored-procedure/9692880#9692880