CREATE PROCEDURE [inner] AS BEGIN
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
IF (1 + 1 = 2) BEGIN
ROLLBACK TRANSACTION;
END ELSE BEGIN
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRANSACTION;
END CATCH
END
和一个外部SP调用内部,
CREATE PROCEDURE [outer] AS BEGIN
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
EXEC [inner];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRANSACTION;
;THROW;
END CATCH
END
然后,我叫外SP,
EXEC [outer];
我知道这个错误
Msg 266 Level 16 State 2 Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.
Previous count = 1, current count = 0.
我不知道为什么它说“不影响后续处理”,因为这是
显然是假的
:相反,它抛出一个严重性为16的错误,这个错误可以用
BEGIN CATCH
捕获。
如果您确实希望使用这样的嵌套事务,并且只能够部分回滚它们,那么它就会变得更加复杂。
如果没有交易,您必须有条件地开始一项事务。然后,您必须
SAVE
一个保存点。然后,每个回滚都必须有条件地回滚整个事务,或者只回滚保存点。
CREATE PROCEDURE [inner] AS BEGIN
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
DECLARE @tranCount int = @@TRANCOUNT;
IF @tranCount = 0
BEGIN TRANSACTION;
SAVE TRANSACTION innerSave;
IF (1 + 1 = 2) BEGIN
IF @tranCount = 0
ROLLBACK;
ROLLBACK TRANSACTION innerSave;
END ELSE BEGIN
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
IF @tranCount = 0