S Q L數(shù)據(jù)庫中事務(wù)的實現(xiàn)方法

文/蔡義忠 熊發(fā)涯

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

筆者在開發(fā)一個《說課比賽網(wǎng)絡(luò)評分系統(tǒng)》時，遇到需要對多個數(shù)據(jù)庫表同時操作的情況，如果用普通的做法，順序?qū)Ω鱾€數(shù)據(jù)庫表進(jìn)行操作，就可能會在極端情況下，出現(xiàn)操作一部分?jǐn)?shù)據(jù)庫表時操作被中斷，導(dǎo)致數(shù)據(jù)庫中的信息出現(xiàn)不一致，甚至出現(xiàn)“臟數(shù)據(jù)”，從而影響軟件系統(tǒng)的穩(wěn)定運行。如果使用事務(wù)來處理對多個數(shù)據(jù)庫表的操作就可以避免在極端情況下的錯誤產(chǎn)生。

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

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

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

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

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

注意：事務(wù)控制命令只與DML 命令I(lǐng)NSERT、UPDATE 和DELETE配合使用，如我們不會創(chuàng)建表之后使用COMMIT 語句，因為當(dāng)表被創(chuàng)建之后，它會自動被提交給數(shù)據(jù)庫。類似地，不能使用ROLLBACK 語句來恢復(fù)被撤消的表。

當(dāng)事務(wù)完成后，事務(wù)信息被保存在數(shù)據(jù)庫里的指定區(qū)域或臨時回退區(qū)域。所有的修改都被保存到這個臨時回退區(qū)域，直到事務(wù)控制命令出現(xiàn)。當(dāng)事務(wù)控制命令出現(xiàn)時，所做的修改要么被保存到數(shù)據(jù)庫中，要么被放棄，然后臨時回退區(qū)域被清空。

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

這個功能需求，用存儲過程及事務(wù)來實現(xiàn)，具體代碼如下：

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

圖1 通過事務(wù)增加院校的界面

--Proc_VS_SchoolAddTran

功能：添加一個學(xué)校的記錄的同時，添加四個教師記錄和四門課程記錄，每個教師對應(yīng)著一門課程，其中第一個教師為默認(rèn)的該校聯(lián)系負(fù)責(zé)人。

Create Procedure [dbo].[Proc_VS_SchoolAddTran] --創(chuàng)建存儲過程

(

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

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

)

As

BEGIN --存儲過程的實現(xiàn)開始

BEGIN TRANSACTION--啟動事務(wù)

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

DECLARE @SCHOOLID INT --定義學(xué)校編號

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

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

Insert Into [dbo].[tb_VS_School](

[SchoolName],

[SchoolAddr]

)

Values

(

@SchoolName,

@SchoolAddr

)

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

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

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

[UserTypeID], --教師類型

[Password], --參賽教師的登陸密碼，默認(rèn)為123456

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

Values(

1,

'123456',

0,

@S C HO O LI D

)

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

SET @TEACHERID=@@identity --保存當(dāng)前插入記錄的自動生成的教師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] --狀態(tài)信息，初始為0，表示未審核

)

Values

(

'某課程',

@SCHOOLID,@TEACHERID,

0

)

SET @ERROR=@ERROR+@@ERROR

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

--由于只要有一個Insert語句執(zhí)行出現(xiàn)失敗，@@ERROR

都會產(chǎn)生一個正整數(shù)，所以每次操作結(jié)束都用

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

IF(@ERROR=0)

BEGIN

COMMIT TRANSACTION

Return @SCHOOLID

END

ELSE

BEGIN

ROLLBACK TRANSACTION Return 0

END

END

go

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

--exec Proc_VS_SchoolAddTran '黃岡技工學(xué)院','黃岡市黃州區(qū)'

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