SQL Server 事務執行一半出錯是否自動復原整個事務

大家都知道SQL Server事務是單個的工作單元。如果某一事務成功，則在該事務中進行的所有資料修改均會提交，成為資料庫中的永久組成部分。如果事務遇到錯誤且必須取消或復原，則所有資料修改均被清除。

所以是不是說事務出錯一定會復原整個事物呢？　先看幾個個例子：　

--create table

createtabletestrollback(id intprimarykey, name varchar(10))

 

SETXACT_ABORTOFF--Default Settings 

begintran

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

committran

三條成功插入只有第三條語句出錯復原

650) this.width=650;" title="1.png" style="height:288px;float:none;width:572px" src="http://img1.51cto.com/attachment/201308/165431791.png" width="882" height="304" />

650) this.width=650;" title="2.png" style="float:none" src="http://www.bkjia.com/uploads/allimg/131228/22035US4-1.png" />

--use SET XACT_ABORT ON

SETXACT_ABORTON;

begintran

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

committran

 

select*from testrollback 

全部復原沒有資料插入

 

---use try catch to catch error and rollback whole transcation 

begintran

begintry

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto 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

insertinto testrollback values (1,'kevin')

insertinto testrollback values (2,'kevin')

insertinto testrollback values (1,'kevin')

insertinto testrollback values (3,'kevin')

之後中斷連線，在伺服器上用DBCC OPENTRAN查看open的事務：

650) this.width=650;" title="3.png" style="float:none" src="http://www.bkjia.com/uploads/allimg/131228/22035V154-2.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 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)

更多資訊參考　Controlling Transactions

 

本文出自 “關注SQL Server技術” 部落格，請務必保留此出處http://lzf328.blog.51cto.com/1196996/1266888