Errors Raised with Severity/Level 16 May Cause Transactions into Doomed State
Consider a scenario wherein you are doing some operation which is generating an error with Level\Severity 16 and is causing the entire transaction to fail with the following message:
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Alternatively, you may see errors like the following which will rollback entire transaction:
Msg 8169, Level 16, State 2, Line 5
Conversion failed when converting from a character string to uniqueidentifier.
Note: There are several more errors that could fail with Level\Severity 16.
You may want to argue as to why the Severity 16 error message terminates the entire transaction and not continue with the batch. And I would say it depends!
Typically, the function which decides whether the transaction would get into doomed state or not is called as XACT_STATE(). This function is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. As per Books Online, this functions returns following three values: 1, 0 or -1. XACT_STATE() = -1 means that the current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. When a batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction.
As per our intensive research we asserted that XACT_STATE changes is not governed by a single rule and depends on the transaction state (user or system initiated and if an active transaction or not) and the type of error being raised. For example, transactions having CONVERT\CAST errors and DDL operations (ALTER TABLE\DATABASE\INDEX etc.) on failure will make the state of the transaction as -1 and will terminate the transaction.
I would like to explain this issue with the help of couple of example codes.
Following example code will get the transaction into doomed state and hence will cause the entire transaction to rollback:
Use Databasename
-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
DROP TABLE my_books;
GO
CREATE TABLE my_books
(
Isbn int PRIMARY KEY,
Title NVARCHAR(100)
);
GO
BEGIN TRY
BEGIN TRANSACTION;
ALTER TABLE my_books DROP COLUMN author;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should be rolled back.
-- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
--Print Error Information
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' + ' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
The output of the above code:
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- ------------------ ----------- ---------------------------
4924 16 1 NULL 4 ALTER TABLE DROP COLUMN failed because column 'author' does not
exist in table 'my_books'.
(1 row(s) affected)
The transaction is in an uncommittable state. Rolling back transaction.
Note: Here the entire transaction have to be rolled back since the state of the transaction have become -1, XACT_STATE()= –1
Let’s look at another code wherein, the Severity of the error is 16 yet the transaction will not rollback but will continue execution:
Use Databasename
-- Verify that the table does not exist.
IF OBJECT_ID (N'Account', N'U') IS NOT NULL
DROP TABLE Account;
GO
CREATE TABLE Account
(
ID int PRIMARY KEY,
Title NVARCHAR(100)
);
GO
INSERT INTO Account (ID,Title) VALUES (1,'ABC')
INSERT INTO Account (ID,Title) VALUES (2,'PQR')
BEGIN TRY
BEGIN TRANSACTION;
SELECT * FROM my_books WHERE Isbn/0 = 2;
SELECT * FROM Account;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should be rolled back.
-- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
--Print Error Information
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' + ' Committing transaction.'
SELECT * FROM Account;
COMMIT TRANSACTION;
END;
END CATCH;
GO
Output of the above code:
Isbn Title
----------- ------------------------------------------
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- ---------------- ----------- -----------------------------------------
8134 16 1 NULL 20 Divide by zero error encountered.
(1 row(s) affected)
The transaction is committable. Committing transaction.
ID Title
----------- ------------------------------------------
1 ABC
2 PQR
(2 row(s) affected)
Note: Here the transaction is continuing execution since the state of the transaction have become 1, XACT_STATE()= –1
So, in the above two examples you have seen how the type of error being raised and the rules are governing the state of a transaction and causing the transaction to either rollback or continue execution with remaining statements of the transaction. One more example which could get your transaction into doomed state is when you attempt to convert a value that is incompatible with target data type. Here is the code that you could try (thanks to one of my clients):
DECLARE @GUID UNIQUEIDENTIFIER
SELECT @GUID = CONVERT(UNIQUEIDENTIFIER, 'ABC')
In summary, when your transaction is hitting Level 16 error the transaction may go into doomed state (XACT_STATE() =-1), though not always as explained above!! If the transaction is doomed, this will cause the entire transaction to get into uncommittable state and hence the entire transaction will roll-back. This is expected behavior as per documentation (https://msdn.microsoft.com/en-us/library/ms189797.aspx); and you need to roll-back the transaction to proceed further using TRY-CATCH block. A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.
Remember, generally severity 16 message indicates general errors that can be corrected by the user. This means that users have an option of correcting their code and should make sure the XACT_STATE never becomes -1, else their transaction will rollback.
I have written this blog as it is not possible to write every example and scenario in the official documentation. I have tested the above example in-house and reached to this conclusion. There could be lot more errors that may get the transactions into doomed state! The user could refer to this blog as a reference to identify whether the error raised with severity\level 16 would roll back the transaction or not. If you wish you can also test errors with other severities or levels.
Regards,
Sumit Sarabhai
Technical Lead
SQL Server Support Team