Does SQL Server Roll Back Entire Transactions on Error in Batched Commands?

SQL Server Transaction Processing: Error Rollback Behavior

Be sure to consider the transactional behavior of your database when executing multiple SQL statements as a single batch command. Specifically, if one or more statements encounter an error, should the entire transaction be rolled back?

In SQL Server, the default behavior is to roll back the entire transaction on any failure. However, in some cases (such as the example provided in the question):

<code class="language-sql">BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;</code>

Where the SQL statement is sent as a single string command, the rollback behavior may not be as expected.

To ensure that failed statements automatically trigger a rollback, you can use the SET XACT_ABORT ON statement before starting the transaction. This command modifies the transaction behavior of the session so that any error encountered immediately causes the transaction to be rolled back. In this case, the insert operation will be reverted and the transaction will not complete successfully.

By setting XACT_ABORT ON explicitly, the database is guaranteed to behave consistently when errors occur, ensuring data integrity and preventing accidental changes. It's worth noting that this setting only applies to the current session and will not affect subsequent transactions.

The above is the detailed content of Does SQL Server Roll Back Entire Transactions on Error in Batched Commands?. For more information, please follow other related articles on the PHP Chinese website!