SQL Server 事务执行一半出错是否自动回滚整个事务
大家都知道英语名子SQL Server事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。
所以是不是说事务出错一定会回滚整个事物呢？ 先看几个个例子：
--create table
createtabletestrollback(id intprimarykey, name varchar(10))
SETXACT_ABORTOFF--Default Settings
begintran
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (2,'kevin')usatoday
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (3,'kevin')
committran
三条成功插入只有第三条语句出错回滚
1.png
2.png
线上线下作文--u SET XACT_ABORT ON
SETXACT_ABORTON;
begintran
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (2,'kevin')
inrtinto testrollback values (1,'kevin')
rewardedinrtinto testrollback values (3,'kevin')
committran
lect*from testrollback
全部回滚没有数据插入
---u try catch to catch error and rollback whole transcation
begintran
begintry
inrtinto testrollback values (1,'kevin')
adsf inrtinto testrollback values (2,'kevin')
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (3,'kevin')
committran
endtry
begincatch
rollback
endcatch
全部回滚没有数据插入
翻译官张京个人介绍 对于上面的测试可以看到默认情况下SQL Server只是Rollback出错的语句而不是整个事物。所以如果想Rollback整个事物的话可以通过SET XACT_ABORT选项设置或者使用信息系统管理工程师Try Catch之类的捕获错误进行Rollback.
对于出现网络问题会跟上面的结果有点不一样。 在执行事务的过程中，如果Client断开，那么SQL Server会自动Rollback整个事物。
在SSMS客户端执行第一条语句，去掉commit Tran
SETXACT_ABORTOFF--Default Settings
begintran
控件是什么
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (2,'kevin')
inrtinto testrollback values (1,'kevin')
inrtinto testrollback values (3,'kevin')
之后断开连接，在服务器上用DBCC OPENTRAN查看open的事务：
3.png
间隔一段时间再执行发现DBCC OPENTRAN已经没有了，而且查询表数据也没有，说明整个事物回滚了。所以在客户端断开且事务没有完成的情况下整个事物回滚。
对于上面的测试微软有详细的解释：
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 Databa 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 Databa 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 Databa 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 caus an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, e SET XACT_ABORT (Transact-SQL)
新东方 骗子 更多信息参考：澳门新濠天地官网
