sql - 触发错误 : The current transaction cannot be committed and cannot support operations that write to the log file

Question

因此，当 sp_SomeProc 尝试执行无效的 sql 语句时，我从 SQL Server 收到以下错误消息。我收到错误:

The current transaction cannot be committed and cannot support operations that write to the log file. 

关于我做错了什么的任何想法？ (这只是我为模拟问题而创建的示例，所以请不要“你为什么这样做？”、“这有安全隐患”等。)

所以我的表看起来像:

CREATE TABLE tSOMETABLE
(  
    RecID INT NOT NULL IDENTITY(1,1)
    Val VARCHAR(20),
CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED 
(
    RecID ASC
)
)

所以在我的触发器中，我有:

CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT]     
    ON [dbo].[tSOMETABLE]   
    FOR INSERT  
AS      
SET NOCOUNT ON  
BEGIN   
         BEGIN
            SELECT * INTO #temp FROM INSERTED

            WHILE EXISTS (SELECT 1 FROM #temp)
            BEGIN
                DECLARE @RecID INT      
                SELECT @RecID = RecID
                FROM #temp t
                EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID
                DELETE #temp WHERE @RecID = RecID
            END         
        END   
END

现在 sp_SomeProc 的代码如下所示:

CREATE PROC sp_SomeProc 
(
    @EventType VARCHAR(50),
    @RecID INT,
    @Debug BIT = 0
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @ProcTable TABLE 
    (
        RecID INT NOT NULL IDENTITY(1,1),
        Cmd VARCHAR(MAX)
    )

    INSERT INTO @ProcTable(Cmd)
      SELECT 'EXEC sp_who'
      UNION
      SELECT 'EXEC sp_SomeStoredProcThatDoesntExist'


    DECLARE  @RecID INT  
    SELECT @RecID = MIN(RecID) FROM @ProcTable
    WHILE @RecID IS NOT NULL
    BEGIN  
        DECLARE @sql VARCHAR(MAX)
        SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID
        IF @Debug = 1
            PRINT @sql
        ELSE
            BEGIN
                BEGIN TRY      
                    EXEC(@sql)
                END TRY
                BEGIN CATCH
                    DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX)
                    SELECT @Msg = 'Failed While Executing: ' + @sql  
                    SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE()
                    -- DO SOME MORE STUFF HERE AND THEN ...
                    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
                END CATCH 
            END
        SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID > @RecID
    END  
END

所以为了测试我尝试:

INSERT INTO tSOMETABLE(Val)
SELECT 'Hello'

Answer 1

当您在事务中使用 try/catch 块时会发生此错误。让我们考虑一个简单的例子:

SET XACT_ABORT ON

IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)
    INSERT INTO #t (i) VALUES (3)
    INSERT INTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batch
    INSERT INTO #t (i) VALUES (4) 

COMMIT  TRAN
SELECT * FROM #t

当第四次插入导致错误时，批处理终止并且事务回滚。到目前为止没有任何惊喜。

现在让我们尝试使用 TRY/CATCH 块处理该错误:

SET XACT_ABORT ON
IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)
    BEGIN TRY
        INSERT INTO #t (i) VALUES (3)
        INSERT INTO #t (i) VALUES (1) -- dup key error
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
    END CATCH  
    INSERT INTO #t (i) VALUES (4)
    /* Error the Current Transaction cannot be committed and 
    cannot support operations that write to the log file. Roll back the transaction. */

COMMIT TRAN
SELECT * FROM #t

我们发现了重复键错误，但除此之外，我们的情况也不好。我们的批处理仍然被终止，我们的事务仍然被回滚。原因其实很简单:

TRY/CATCH 块不影响交易。

由于 XACT_ABORT ON，一旦出现重复键错误，事务就注定失败。它已经完成了。它受了致命伤。它被击穿了心脏……而错误是罪魁祸首。 TRY/CATCH 给 SQL Server 带来了一个糟糕的名字。 (对不起，忍不住)

换句话说，它将 从不 promise 和意愿 永远 被回滚。 TRY/CATCH 块所能做的就是阻止尸体的坠落。我们可以使用 XACT_STATE() 函数来查看我们的事务是否可提交。如果不是，唯一的选择是回滚事务。

SET XACT_ABORT ON -- Try with it OFF as well.
IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)

    SAVE TRANSACTION Save1
    BEGIN TRY
        INSERT INTO #t (i) VALUES (3)
        INSERT INTO #t (i) VALUES (1) -- dup key error
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
        IF XACT_STATE() = -1 -- Transaction is doomed, Rollback everything.
            ROLLBACK TRAN
        IF XACT_STATE() = 1 --Transaction is commitable, we can rollback to a save point
            ROLLBACK TRAN Save1
    END CATCH  
    INSERT INTO #t (i) VALUES (4)

IF @@TRANCOUNT > 0
    COMMIT TRAN
SELECT * FROM #t

触发器总是在事务的上下文中执行，因此如果您可以避免在其中使用 TRY/CATCH，事情就会简单得多。

为了解决您的问题，CLR 存储过程可以在单独的连接中连接回 SQL Server 以执行动态 SQL。您可以获得在新事务中执行代码的能力，并且错误处理逻辑在 C# 中既易于编写又易于理解。