SQL Server在預存程序中編寫交易處理代碼的三種方法

SQL Server中資料庫交易處理是相當有用的，鑒於很多SQL初學者編寫的交易處理代碼存往往存在漏洞，本文我們介紹了三種不同的方法，舉例說明了如何在預存程序交易處理中編寫正確的代碼。希望能夠對您有所協助。

在編寫SQL Server 事務相關的預存程序代碼時，經常看到下面這樣的寫法：

begin tran   
   update statement 1 ...   
   update statement 2 ...   
   delete statement 3 ...   
commit tran 

這樣編寫的SQL存在很大隱患。請看下面的例子：

create table demo(id int not null)   
go   
begin tran   
   insert into demo values (null)  
   insert into demo values (2)   
commit tran   
go 

執行時會出現一個違反not null 約束的錯誤資訊，但隨後又提示(1 row(s) affected)。 我們執行select * from demo
後發現insert into demo values(2) 卻執行成功了。 這是什麼原因呢? 原來 SQL Server在發生runtime
錯誤時，預設會rollback引起錯誤的語句，而繼續執行後續語句。

如何避免這樣的問題呢？

 

有三種方法：

1. 在事務語句最前面加上set xact_abort on

set xact_abort on   
begin tran   
   update statement 1 ...   
   update statement 2 ...   
   delete statement 3 ...   
commit tran   
go 

當xact_abort 選項為on 時，SQL Server在遇到錯誤時會終止執行並rollback 整個事務。

2. 在每個單獨的DML語句執行後，立即判斷執行狀態，並做相應處理。

begin tran   
   update statement 1 ...  
   if @@error <> 0   
      begin rollback tran   
      goto labend   
   end   
   delete statement 2 ...   
   if @@error <> 0  
      begin rollback tran   
      goto labend   
   end   
   commit tran   
   labend:   
go 

3. 在SQL Server 2005中，可利用 try...catch 異常處理機制。

begin tran   
begin try   
   update statement 1 ...   
   delete statement 2 ...   
end try   
begin catch  
   if @@trancount > 0   
      rollback tran   
end catch  
   if @@trancount > 0   
      commit tran  
go 

關於SQL Server資料庫中在預存程序中編寫正確的交易處理代碼的方法就介紹到這裡了，希望本次的介紹能夠對您有所協助。

原文出處：http://www.sqlstudy.com/sql_article.php?id=2008060701。