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

As we all know, SQL Server 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 be canceled or rolled back, all data modifications are cleared.

 

So is it true that a transaction error will always roll back the entire transaction? Let's take a look at several examples:

-- Createtable

Create Table testrollback (idintprimarykey,
Namevarchar (10 ))

 

Setxact_abortoff
-- Default settings

Begin tran

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (2, 'kevin ')

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (3, 'kevin ')

Commit tran

 

Three successful inserts, only the third statement, error rollback

 

 

-- Use setxact_abort on

Set xact_aborton;

Begin tran

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (2, 'kevin ')

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (3, 'kevin ')

Commit tran

 

Select * From testrollback

All rollback without data insertion

 

--- Use trycatch to catch error and rollback whole transcation

Begin tran

Begin
Try

Insert
Into testrollback values (1, 'kevin ')

Insert
Into testrollback values (2, 'kevin ')

Insert
Into testrollback values (1, 'kevin ')

Insert
Into testrollback values (3, 'kevin ')

Commit
Tran

End
Try

Begin catch

Rollback

End catch

 

All rollback without data insertion

 

In the above test, we can see that by default, SQL Server is only a rollback error statement, not the whole thing. So if you want to rollback the entire thing, you can use the set
Set the xact_abort option or use catch errors such as try catch for rollback.

Network problems may be slightly different from the above results. If the client is disconnected during transaction execution, SQL Server automatically rolls back the entire transaction.

Run the first statement on the SSMs client to remove committran.

Set xact_abortoff -- default settings

Begin tran

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (2, 'kevin ')

Insert into testrollbackvalues (1, 'kevin ')

Insert into testrollbackvalues (3, 'kevin ')

 

Then disconnect and use DBCC opentran on the server to view open transactions:

After a period of execution, it is found that DBCC opentran is no longer available and no table data is queried. This means that the entire transaction is rolled back. Therefore, the entire transaction is rolled back when the client is disconnected and the transaction is not completed.

 

For the above test, Microsoft has a detailed explanation:

If an error prevents the successful completion of a transaction, sqlserver automatautomatautomatrollrolls back the transaction and frees all resources held bythe transaction. if the client's network connection to an instance of thedatabase engine is broken,
Any outstanding transactions for the connection arerolled back when the network notifies the instance of the break. if the clientapplication fails or if the client computer goes down or is restarted, thisalso breaks the connection, and the instance of
Database Engine rolls backany outstanding connections when the network notifies it of the break. If theclient logs off the application, any outstanding transactions are rolled back.

If a Run-Time statement error (such as a constraint violation) occurs in abatch, the default behavior in the database engine is to roll back only thestatement that generated the error. you can change this behavior using the setxact_abort statement.
After set xact_abort on is executed, any run-timestatement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by set xact_abort. formore information, seeset
Xact_abort (TRANSACT-SQL)

For more information, see controllingtransactions.