SQL Server transaction execution half error automatically rolling back the entire transaction "go"

Http://www.2cto.com/database/201308/234728.html

SQL Server transaction execution half error whether the entire transaction is automatically rolled back

As you know, SQL Server transactions are a single unit of work. If a transaction succeeds, all data modifications made in the transaction are committed and become a permanent part of the database. If the transaction encounters an error and must be canceled or rolled back, all data modifications are cleared.

So is it that the business is going wrong and will roll back the whole thing? Let's look at a few examples:

--createtableCreate TableTestrollback (Idintprimarykey, Namevarchar (Ten)) Setxact_abortoff--Default Settingsbegin TranInsert  intoTestrollbackvalues (1,'Kevin')Insert  intoTestrollbackvalues (2,'Kevin')Insert  intoTestrollbackvalues (1,'Kevin')Insert  intoTestrollbackvalues (3,'Kevin')Commit Tran

1 --Use setxact_abort on2 SETXact_aborton;3 begin Tran4 Insert  intoTestrollbackvalues (1,'Kevin')5 Insert  intoTestrollbackvalues (2,'Kevin')6 Insert  intoTestrollbackvalues (1,'Kevin')7 Insert  intoTestrollbackvalues (3,'Kevin')8 Commit Tran9  Ten Select * fromTestrollback One all rollback no data inserted A   - ---use Trycatch to catch error and rollback whole transcation - begin Tran the     beginTry -        Insert  intoTestrollbackValues(1,'Kevin') -        Insert  intoTestrollbackValues(2,'Kevin') -        Insert  intoTestrollbackValues(1,'Kevin') +        Insert  intoTestrollbackValues(3,'Kevin') -     Commit Tran +     EndTry A beginCatch at    rollback - EndCatch

All rollback no data inserted

For the above test you can see that SQL Server is simply rollback the error statement instead of the whole thing by default. So if you want to rollback the whole thing, you can use the SET XACT_ABORT option settings or catch errors such as Try catch to rollback.
For network problems, it's a bit different from the results above. In the process of executing a transaction, if the client disconnects, SQL Server automatically rollback the whole thing.
Executes the first statement in the SSMS client, removing the Committran

SETXact_abortoff--Default Settingsbegin TranInsert  intoTestrollbackvalues (1,'Kevin')Insert  intoTestrollbackvalues (2,'Kevin')Insert  intoTestrollbackvalues (1,'Kevin')Insert  intoTestrollbackvalues (3,'Kevin')

Then disconnect and view the open transaction with DBCC OPENTRAN on the server:

Interval of time to perform the discovery DBCC OPENTRAN is gone, and the query table data does not, indicating that the whole thing is rolled back. So when the client disconnects and the transaction does not complete, the whole thing is rolled back.

SQL Server transaction execution half error automatically rolling back the entire transaction "go"