// SQL Server error reference

SQL Server error 3930: Transaction cannot be committed

An error made the transaction uncommittable (XACT_STATE() = -1). It can only be rolled back; anything that writes to the log fails until you do.

Error number
3930
Severity
16
Category
Transactions and locking
Error message
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

What it means

Some errors leave a transaction doomed: it still exists, but SQL Server will not commit it. Inside a CATCH block, XACT_STATE() returns -1 in this state. Any statement that would write to the transaction log, including COMMIT and inserts into a logging table, fails with 3930 until the transaction is rolled back.

Common causes

  • An error inside a TRY block while SET XACT_ABORT ON is in effect: every runtime error then dooms the transaction.
  • Errors that doom a transaction even without XACT_ABORT, such as a deadlock or some conversion and constraint errors in triggers.
  • A CATCH block that tries to write an error log row or commit before rolling back.

How to fix it

  1. In every CATCH block, check XACT_STATE() and roll back first, then log or rethrow.

    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        INSERT INTO dbo.ErrorLog (Number, Message) VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
        THROW;
    END CATCH
  2. If you need to keep part of the work, you can only commit when XACT_STATE() is 1; with -1 the only option is a full rollback.

  3. Find and fix the original error, which ERROR_NUMBER() and ERROR_MESSAGE() report in the CATCH block.

Reproduce it safely

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT XACT_STATE();  -- -1
    COMMIT;               -- Msg 3930
END CATCH;
IF @@TRANCOUNT > 0 ROLLBACK;

Related errors

More transactions and locking errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.