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
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
TRYblock whileSET XACT_ABORT ONis 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
CATCHblock that tries to write an error log row or commit before rolling back.
How to fix it
-
In every
CATCHblock, checkXACT_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 -
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. -
Find and fix the original error, which
ERROR_NUMBER()andERROR_MESSAGE()report in theCATCHblock.
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
266Transaction count mismatch after EXECUTE3902COMMIT has no corresponding BEGIN3903ROLLBACK has no corresponding BEGIN1205Deadlocked and chosen as the victim25P02PostgreSQL: current transaction is aborted
More transactions and locking errors
1222Lock request time out period exceeded3609Transaction ended in the trigger3960Snapshot isolation update conflict3616Error raised during trigger execution6401No transaction or savepoint found3998Uncommittable transaction rolled back3621The statement has been terminated
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.
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.