mysql+nest+嵌套事务,SQL Server-存储过程中的嵌套事务

Lets say this is the situation:

[Stored Proc 1]

BEGIN

BEGIN TRANSACTION

...

exec sp 2

COMMIT

END

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

Thanks.

解决方案

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

EDIT asked to point to documentation. Don't know of the topic that documents this explicitly, but I can show it to you in action.

USE tempdb;

GO

Inner proc:

CREATE PROCEDURE dbo.sp2

@trip BIT

AS

BEGIN

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;

END

GO

Outer proc:

CREATE PROCEDURE dbo.sp1

@trip BIT

AS

BEGIN

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;

END

GO

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

Transaction count after EXECUTE indicates a mismatching number

of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

0

0