Whether to automatically roll back the entire transaction if half of SQL Server transaction execution fails

As we all know, SQLServer transactions are a single unit of work. If a transaction succeeds, all data modifications made in the transaction will be committed and become a permanent component of the database. If the transaction encounters an error and must

As we all know, SQLServer transactions are a single unit of work. If a transaction succeeds, all data modifications made in the transaction will be committed and become a permanent component of the database. If the transaction encounters an error and must

Name

Testrollback

2

1

3

Testrollback

2

1

3

Testrollback

1

2

1

3

Testrollback

2

1

3

Open

For the above test, Microsoft has a detailed explanation:

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. if the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. if the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. if the client logs off the application, any outstanding transactions are rolled back.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. you can change this behavior using the SET XACT_ABORT statement. after SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. compile errors, such as syntax errors, are not affected by SET XACT_ABORT. for more information, see SET XACT_ABORT (Transact-SQL)

For more information, see Controlling Transactions.

This article is from the "follow SQL Server Technology" blog. Be sure to keep this source

, Hong Kong server, U.S. server, and Hong Kong Virtual Host