S Q L數據庫中事務的實現方法

文/蔡義忠 熊發涯

本文主要探討事務的定義，事務的基本特征，以及在S Q L S e r v e r數據庫的存儲過程中的具體實現方法。還將探討事務在執行過程中失敗了，在存儲過程中是怎樣判斷及怎樣具體處理的方法。

筆者在開發一個《說課比賽網絡評分系統》時，遇到需要對多個數據庫表同時操作的情況，如果用普通的做法，順序對各個數據庫表進行操作，就可能會在極端情況下，出現操作一部分數據庫表時操作被中斷，導致數據庫中的信息出現不一致，甚至出現“臟數據”，從而影響軟件系統的穩定運行。如果使用事務來處理對多個數據庫表的操作就可以避免在極端情況下的錯誤產生。

事務是對數據庫執行的一個操作單位，它是以邏輯順序完成的工作單元或工作序列，無論是用戶手工操作，還是由程序進行的自動操作。

一個事務可以是一個或多個DML語句。在管理事務時，任何指定的事務都必須作為一個整體來完成，否則其中任何一條語句都不會完成。下面是事務的基本特征：

1．所有的事務都有開始和結束。例如，在SQL Server中事務用BEGIN TRANSACTION開始。

2.事務可以被保存或撤消。例如，在SQLS e r v e r 中撤消事務的方法用R O L L B A C KT R A N S A C T I O N 。提交事務用COMMIT TRANSACTION。通常撤消或保存事務是放在一個條件判斷語句中來執行的。

3．如果事務在中途失敗，事務中的任何部分都不會被記錄到數據庫中，這樣可以很好的保證數據的完整性。

注意：事務控制命令只與DML 命令INSERT、UPDATE 和DELETE配合使用，如我們不會創建表之后使用COMMIT 語句，因為當表被創建之后，它會自動被提交給數據庫。類似地，不能使用ROLLBACK 語句來恢復被撤消的表。

當事務完成后，事務信息被保存在數據庫里的指定區域或臨時回退區域。所有的修改都被保存到這個臨時回退區域，直到事務控制命令出現。當事務控制命令出現時，所做的修改要么被保存到數據庫中，要么被放棄，然后臨時回退區域被清空。

以具體的應用實例來進一步解釋事務的使用方法。在我最近開發的《說課比賽網絡評分系統》中，要求一個院校最多只能報四門課程參加湖北省高職高專院校說課比賽。那么，在數據庫系統中，增加一個院校表信息的同時，就得在用戶表和課程表中同時增加四條記錄信息，并且一個新增的參賽教師信息對應一個新增的參賽課程信息。這個功能對應的界面如圖1所示。

這個功能需求，用存儲過程及事務來實現，具體代碼如下：

注意：SQL Server的注釋是用“--”開頭，讀者可以通過看注釋來理解事務的實現方法。

圖1 通過事務增加院校的界面

--Proc_VS_SchoolAddTran

功能：添加一個學校的記錄的同時，添加四個教師記錄和四門課程記錄，每個教師對應著一門課程，其中第一個教師為默認的該校聯系負責人。

Create Procedure [dbo].[Proc_VS_SchoolAddTran] --創建存儲過程

(

@SchoolName [varchar](50), --學校名稱

@SchoolAddr [varchar](250) --學校地址

)

As

BEGIN --存儲過程的實現開始

BEGIN TRANSACTION--啟動事務

DECLARE @ERROR INT --定義整型變量存儲失敗錯誤編號

DECLARE @SCHOOLID INT --定義學校編號

DECLARE @TEACHERID INT --定義教師編號

SET @ERROR=0 --初始化記錄錯誤編號的變量值為0--根據存儲過程的參數@SchoolName,@SchoolAddr來插入一條學校信息記錄。

Insert Into [dbo].[tb_VS_School](

[SchoolName],

[SchoolAddr]

)

Values

(

@SchoolName,

@SchoolAddr

)

SET @ERROR=@ERROR+@@ERROR --保存當前操作返回的錯誤信息編號，注意沒錯誤時@@ERROR返回為0，有錯誤時，@@ERROR返回為非零的正整數

SET @SCHOOLID= @@identity --保存當前插入記錄的自動生成的院校ID號

--插入第一個教師的初始信息Insert Into [dbo].[tb_VS_User](

[UserTypeID], --教師類型

[Password], --參賽教師的登陸密碼，默認為123456

[Status], --參賽教師的申報信息狀態，默認為0，即未審核[SchoolID]--參賽教師所在學校的編號，即上面生成新院校的ID號)

Values(

1,

'123456',

0,

@S C HO O LI D

)

SET @ERROR=@ERROR+@@ERROR --保存當前操作返回的錯誤信息編號

SET @TEACHERID=@@identity --保存當前插入記錄的自動生成的教師ID號

Update [dbo].[tb_VS_School]

Set

[ConnectManID]=@TEACHERID

Where SchoolID=@SCHOOLID

SET @ERROR=@ERROR+@@ERROR

Insert [dbo].[tb_VS_Course] --插入一門新課程信息

(

[CourseName],--課程名

[FromSchoolID], --所在院校編號，即上面的新增院校ID編號

[FromTeacherID], --所屬教師編號，即上面的新增教師ID編號

[Status] --狀態信息，初始為0，表示未審核

)

Values

(

'某課程',

@SCHOOLID,@TEACHERID,

0

)

SET @ERROR=@ERROR+@@ERROR

--增加第二名第三名第四名教師及課程的操作代碼同上，在此處略過。

--由于只要有一個Insert語句執行出現失敗，@@ERROR

都會產生一個正整數，所以每次操作結束都用

@ERROR=@ERROR+@@ERROR記錄下@ERROR的值是否發生變化，如果@ERROR始終為零，則一切正常，可以提交事務，完成整個操作，反之，則回滾事務，放棄所做的修改。

IF(@ERROR=0)

BEGIN

COMMIT TRANSACTION

Return @SCHOOLID

END

ELSE

BEGIN

ROLLBACK TRANSACTION Return 0

END

END

go

--最后用exec來執行測試一下Proc_VS_SchoolAddTran的功能

--exec Proc_VS_SchoolAddTran '黃岡技工學院','黃岡市黃州區'

事務處理在數據庫應用中極為重要，是保證數據庫應用系統安全穩定運行的重要手段。本文中的小案例中的判斷事務執行的成功或失敗是通過記錄每個操作語句執行后返回的系統錯誤號@@ERROR來判斷，只要有一次出現@@ERROR返回值不是0，則最終的@ERROR變量就不能為0，也就可以判斷事務是否失敗。注意區別，@ERROR是我定義的變量，@@ERROR是系統返回的錯誤編號，執行正常時@@ERROR返回為0,執行不正常@@ERROR返回為非0。