10

同事詢問關於Procedure內是否會自動包成Transaction的問題，我的認知是SQL要額外下SET XACT_ABORT ON，但Oracle則預設會自動包成Transaction。記憶有些模糊，所以索性做個實驗最準。

分別在SQL, Oracle寫了Procedure，先塞入兩筆資料，第三筆故意產生PK重覆錯誤，觀察資料庫是否有前兩筆資料來判別是否有Transaction保護。實驗證明，原先的認知沒錯，二者行為不同。

SQL Server Procedure要將操作包成Transaction的話，記得要加上BEGIN TRAN / COMMIT TRAN及SET XACT_ABORT ON! [2010-04-26 更正: 原先測試環境中因開啟Implicit Transaction選項，導致測試結果失真，除加入XACT_ABORT設定外，還需加上BEGIN TRAN、COMMIT TRAN，在此感謝Nipsan指正]

/*** SQL Server Test ***/
--建立有PK的Table
create table jefftable (
    id decimal, 
    constraint pk_jefftable primary key (id)
)
--建立SP, 故意塞入重覆PK
create procedure jeffproc
as 
begin
   /* SQL SP要自成Transaction, 要加這列
    set xact_abort on
   */
   begin tran
   insert into jefftable values (1)
   insert into jefftable values (2)
   insert into jefftable values (1)
   commit tran
end
--執行SP, 會產生錯誤
exec jeffproc
--查Table會看到兩筆
select * from jefftable
--清理實驗廢棄物
drop table jefftable
drop procedure jeffproc
 
/*** ORACLE Test ***/
--建立有PK的Table
create table jefftable (
  id decimal, 
  constraint pk_jefftable primary key (id)
)
--建立SP, 故意塞入重覆PK
create procedure jeffproc
is
begin
  insert into jefftable values (1);
  insert into jefftable values (2);
  insert into jefftable values (1);
end;
--執行SP, 會產生錯誤
declare
begin
jeffproc;
end;
--查Table, 沒有資料
select * from jefftable
--清理實驗廢棄物
drop table jefftable
drop procedure jeffproc

Comments

# by 小熊

謝謝您的分享... ^^ 在學習這段語法的過程中， 因為 /* SQL SP要自成Transaction, 要加這列 set xact_abort on */ 中間的set xact_abort on沒有變成和註解一樣的綠色， 一開始還弄不太清楚， 小小建議一下也許可以考慮用"--"來標示... ^_^||

# by Jeffrey

to 小熊，CSharpFormatter在處理註解標色時不是100%精準，我想下回會格外留意，並謝謝你的指正!

# by Nipsan

/* SQL SP要自成Transaction, 要加這列 set xact_abort on */ SQL要成Transaction,必须显式加上 BEGIN TRAN,set xact_abort on 只是控制有错就整个transaction rollback. e.g.

# by Jeffrey

to Nipsan, 重複驗證後，發現原先測試疑受SSMS Implicit Transation選項影響，導致結果失真，已更正本文。感謝您的指正!

# by 丫包

黑大好~..我將sp加入return code..如下: create procedure jeffprocis begin begin tran DECLARE @ReturnMsg AS VARCHAR(10); insert into jefftable values (1,2,'aa'); if @ERROR > 0 GOTO GORollBack; insert into jefftable values (2,2,'bb'); if @ERROR > 0 GOTO GORollBack; insert into jefftable values (1,2,'cc'); --錯誤的,因為"1,2"為複合鍵PK if @ERROR > 0 GOTO GORollBack; commit tran SET @ReturnMsg = 'OK' GORollBack: ROLLBACK TRANSACTION SET @ReturnMsg = 'ERROR' end; TABLE的PK是使用二個欄位..執行下述的sp..一直會回傳錯誤訊息..不曉得黑大可以給我一些修改的方向嗎?..謝謝~!! [sp] DECLARE @Msg VARCHAR(30); EXEC jeffprocis @Msg OUTPUT; PRINT @Msg; [錯誤訊息] Violation of PRIMARY KEY constraint 'PK_XXX'. Cannot insert duplicate key in object 'dbo.XXX'.

# by 丫包

黑大好~..我將sp加入return code..如下: create procedure jeffprocis begin begin tran DECLARE @ReturnMsg AS VARCHAR(10); insert into jefftable values (1,2,'aa'); if @ERROR > 0 GOTO GORollBack; insert into jefftable values (2,2,'bb'); if @ERROR > 0 GOTO GORollBack; insert into jefftable values (1,2,'cc'); --錯誤的,因為"1,2"為複合鍵PK if @ERROR > 0 GOTO GORollBack; commit tran SET @ReturnMsg = 'OK' GORollBack: ROLLBACK TRANSACTION SET @ReturnMsg = 'ERROR' end; TABLE的PK是使用二個欄位..執行下述的sp..一直會回傳錯誤訊息..不曉得黑大可以給我一些修改的方向嗎?..謝謝~!! [sp] DECLARE @Msg VARCHAR(30); EXEC jeffprocis @Msg OUTPUT; PRINT @Msg; [錯誤訊息] Violation of PRIMARY KEY constraint 'PK_XXX'. Cannot insert duplicate key in object 'dbo.XXX'.

# by 丫包

><..黑大..sorry..我PO了二次~~..>"<..

# by 丫包

另外...我解決了啦..><..我在外層判斷了是否有此一筆資料.......誤解了tran的功能..它只是在做交易資料的回復..如果資料有錯..還是得用try..catch來整理.. 我不是故意洗版的~~..><..

# by orson

黑大 ado.net的transaction 去呼叫這筆procedure的話，BEGIN TRAN,set xact_abort on 可以拿掉嗎？

# by Jeffrey

to orson, 是，如果Transaction要由外界控制，移除SP內的交易控制指令會比較單純。

Post a comment