MS SQL Server中CATCH块内XACT_STATE为1的错误类型及示例合理性

Great question—your confusion is totally understandable, but the example is not wrong. Let's clear up the key misconception first: not all errors that trigger a CATCH block will set XACT_STATE to -1.

First, a Quick Recap of XACT_STATE Values #

• XACT_STATE() = 1: The transaction is committable. Even though an error occurred and we entered the CATCH block, the transaction itself is still intact and can be committed (if your business logic allows it).
• XACT_STATE() = -1: The transaction is uncommittable (doomed). The error was severe enough to corrupt the transaction's integrity, so the only valid operation is a rollback.
• XACT_STATE() = 0: No active transaction exists.

Which Errors Leave XACT_STATE = 1 in the CATCH Block? #

There are plenty of common errors that trigger CATCH but don't doom the transaction. Here are the most frequent scenarios:

• Non-fatal constraint violations: For example, inserting a duplicate primary key (severity level 14) or violating a CHECK constraint. These errors fail the individual statement, but the rest of the transaction remains valid.
• Data conversion errors: Like trying to cast a non-numeric string to an INT (severity level 16). If XACT_ABORT is OFF, this only fails the problematic statement, not the entire transaction. Even with XACT_ABORT = ON, some of these errors still leave the transaction committable.
• General runtime errors that don't break transaction integrity: Think of errors like "divide by zero" (severity level 16) or "invalid column name" (severity level 16)—these break the current statement but don't render the entire transaction uncommittable.

Why the Example Includes the XACT_STATE = 1 Check #

The example is following best practices: it accounts for all possible transaction states after an error. There are cases where you might want to commit the transaction even after an error—for instance, if the error was isolated to a single statement that you've handled in the CATCH block, and the rest of the transaction's work is still valid.

For example, suppose your TRY block does three things:

1. Updates a customer's email
2. Tries to insert a duplicate order (which fails)
3. Updates the customer's last activity date

If the duplicate order error triggers CATCH, but the email and last activity updates are still valid, you might choose to commit those changes instead of rolling back everything. That's when XACT_STATE() = 1 comes into play.

A Note on XACT_ABORT #

Your initial thought about XACT_ABORT = ON is partially correct, but not absolute. When XACT_ABORT = ON, some errors will terminate the transaction immediately (leading to XACT_STATE = -1), but many others only terminate the problematic statement and leave the transaction committable. The key factor is whether the error is severe enough to corrupt the transaction's consistency.

内容的提问来源于stack exchange，提问作者Mohammad Afrashteh