1.1.1 摘要
相信大家對于SQL Transcation再熟悉不過，它確保了數(shù)據(jù)庫的數(shù)據(jù)一致性和安全性，尤其在對數(shù)據(jù)執(zhí)行增刪時(shí)，如果發(fā)生異常和錯(cuò)誤它就會(huì)觸發(fā)事務(wù)回滾，從而確保了我們數(shù)據(jù)的一致性和安全性，下面我們將通過分四部分介紹事件（Transcation）。
1.1.2 正文
首先讓我們通過一個(gè)具體的例子介紹Transcation的使用，假如我們的數(shù)據(jù)庫中有一個(gè)表UserInfo，它包含三個(gè)字段分別為：UserID(自增)、UserName (nvarchar)和LuckyNumber (tinyint)，如下圖所示：
圖1 UserInfo表
UserInfo表的sql代碼如下：
復(fù)制代碼 代碼如下:
-- The definition of UserInfo.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfo](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[LuckyNumber] [tinyint] NOT NULL
) ON [PRIMARY]
接著我們要把數(shù)據(jù)插入到表UserInfo中，這里使用一個(gè)存儲(chǔ)過程把數(shù)據(jù)插入到該表中，存儲(chǔ)過程SPAddDataToUserInfo的定義如下：
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
CREATE PROCEDURE SPAddDataToUserInfo
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('JKRush', 23);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
END
GO
現(xiàn)在我們已經(jīng)定義了一個(gè)存儲(chǔ)過程，接著讓我們執(zhí)行該存儲(chǔ)過程。
圖2執(zhí)行存儲(chǔ)過程的消息
通過上圖我們知道在執(zhí)行存儲(chǔ)過程中發(fā)生了異常，而且是由于值“20111111”數(shù)據(jù)超出了tinyint的范圍產(chǎn)生的，現(xiàn)在讓我們看一下數(shù)據(jù)插入的情況。
圖3 UserInfo表中數(shù)據(jù)
我們發(fā)現(xiàn)只插入了兩行數(shù)據(jù)，而第三行數(shù)據(jù)沒有成功插入，但為了確保數(shù)據(jù)完整性，我們要把數(shù)據(jù)全部插入或全部不插入，這時(shí)我們可以考慮使用Transcation來確保數(shù)據(jù)完整性和安全性。
接著讓我們修改一下存儲(chǔ)過程SPAddDataToUserInfo，在存儲(chǔ)過程中添加Transcation。
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END
GO
現(xiàn)在我們再執(zhí)行一次存儲(chǔ)過程看一下是否全部不插入到表中。
圖4 UserInfo表中數(shù)據(jù)
我們發(fā)現(xiàn)結(jié)果和沒有添加Transcation處理一樣，數(shù)據(jù)依然插入到表中。這究竟是什么原因呢？也許細(xì)心的你已經(jīng)發(fā)現(xiàn)了，我們沒有添加事務(wù)回滾——ROLLBACK。
但我們究竟要在哪里添加事務(wù)回滾（ROLLBACK）呢？或更具體地說：“究竟什么時(shí)候我們要觸發(fā)事務(wù)回滾（ROLLBACK）呢”？
由于我們數(shù)據(jù)插入失敗是因?yàn)椴迦脒^程發(fā)生了異常情況，那么我們就要捕獲異常和處理異常，那就是TRY/CATCH的設(shè)計(jì)了，好讓我們繼續(xù)完善我們的存儲(chǔ)過程吧。
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
現(xiàn)在我們給存儲(chǔ)過程添加了異常處理機(jī)制TRY/CATCH（注意：SQLSERVER 2005或之后的版本才支持TRY/CATCH），接下來讓我們再執(zhí)行一次存儲(chǔ)過程。
圖5 UserInfo表中數(shù)據(jù)
看上去我們已經(jīng)把問題的解決了，我們知道存儲(chǔ)過程可以內(nèi)嵌存儲(chǔ)過程或函數(shù)，所以我們把上面的存儲(chǔ)過程SPAddDataToUserInfo內(nèi)嵌到存儲(chǔ)過程SPMultiDataToUserInfo中，SPMultiDataToUserInfo的定義如下：
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
CREATE PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
我們知道存儲(chǔ)過程SPAddDataToUserInfo會(huì)發(fā)生異常，它會(huì)回滾事務(wù)（ROLLBACK），但SPMultiDataToUserInfo是發(fā)生回滾還是繼續(xù)插入數(shù)據(jù)呢？
圖6 執(zhí)行存儲(chǔ)過程消息
圖7 UserInfo表中數(shù)據(jù)
在插入的過程發(fā)生了異常，檢查UserInfo表數(shù)據(jù)并沒有插入表中，這符合我們設(shè)計(jì)的意圖，但我們發(fā)現(xiàn)異常不僅僅是產(chǎn)生于插入的數(shù)據(jù)超出了數(shù)值的范圍，還包含事務(wù)計(jì)數(shù)異常。
由于定義了兩個(gè)存儲(chǔ)過程，而且SPAddDataToUserInfo內(nèi)嵌在SPMultiDataToUserInfo中，在執(zhí)行這兩個(gè)存儲(chǔ)過程中，它們都發(fā)生了異常并且進(jìn)行事務(wù)回滾（因?yàn)閁ser為空）。
由于插入的數(shù)據(jù)超出了數(shù)值的范圍的異常是我們特意引起的，而事務(wù)計(jì)數(shù)異常這是預(yù)期之外的異常。
接下來讓我們看一下究竟是什么原因引起了該異常，這里我們通過輸出Transactions計(jì)數(shù)來查看問題所在。
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
上面我們通過輸出事務(wù)的計(jì)數(shù)（@@TRANCOUNT）來查看在事務(wù)回滾時(shí)，事務(wù)計(jì)數(shù)器的變化。
圖8 存儲(chǔ)過程執(zhí)行消息
通過上圖我們發(fā)現(xiàn)在執(zhí)行SPMultiDataToUserInfo和SPAddDataToUserInfo時(shí)，事務(wù)計(jì)數(shù)器分別加1，當(dāng)遇到SPAddDataToUserInfo中的異常時(shí)，事務(wù)回滾事務(wù)計(jì)算器置零。
當(dāng)執(zhí)行SPMultiDataToUserInfo中的事務(wù)時(shí)，由于事務(wù)計(jì)算器（@@TRANCOUNT）已經(jīng)置零，導(dǎo)致拋出異常，現(xiàn)在我們明白了導(dǎo)致事務(wù)計(jì)數(shù)異常的原因，所以我們在進(jìn)行事務(wù)回滾之前必須判斷事務(wù)計(jì)算器（@@TRANCOUNT）是否為0，如果為0就不回滾事務(wù)。
復(fù)制代碼 代碼如下:
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPAddDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
現(xiàn)在我們增加了事務(wù)計(jì)數(shù)器的判斷，當(dāng)計(jì)數(shù)器為0時(shí)，不進(jìn)行事務(wù)回滾，這樣就沒有了之前事務(wù)計(jì)數(shù)器異常了。
圖9 存儲(chǔ)過程執(zhí)行消息
現(xiàn)在對于事務(wù)我們有了進(jìn)一步的了解，而且把以上的事務(wù)定義成為一個(gè)日常通用的模板，如下給出了一個(gè)基本的Transaction模板。
復(fù)制代碼 代碼如下:
-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
BEGIN TRANSACTION
--
-- You code here.
--
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
-- Adds store procedure
-- Writes the error into ErrorLog table.
ROLLBACK TRANSACTION
END IF
END CATCH
1.1.3 總結(jié)
事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作?？梢允且粭lSQL語句也可以是多條SQL語句。
事務(wù)具有四個(gè)特性
原子性：不可分隔、成則具成、敗則具敗。
一致性：事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致狀態(tài)
隔離性：獨(dú)立的執(zhí)行互不干擾。由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。
持久性：務(wù)完成之后，它對于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。
應(yīng)用程序主要通過指定事務(wù)啟動(dòng)和結(jié)束的時(shí)間來控制事務(wù)。
啟動(dòng)事務(wù)：使用 API 函數(shù)和 Transact-SQL 語句，可以按顯式、自動(dòng)提交或隱式的方式來啟動(dòng)事務(wù)。
結(jié)束事務(wù)：您可以使用 COMMIT（成功） 或 ROLLBACK（失敗） 語句，或者通過 API 函數(shù)來結(jié)束事務(wù)。
事務(wù)模式分為：顯示事務(wù)模式、隱式事務(wù)模式、自動(dòng)事務(wù)模式。在SQL常用的是顯示模式。
創(chuàng)建事務(wù)的原則：
盡可能使事務(wù)保持簡短很重要，當(dāng)事務(wù)啟動(dòng)后，數(shù)據(jù)庫管理系統(tǒng) (DBMS) 必須在事務(wù)結(jié)束之前保留很多資源、以保證事務(wù)的正確安全執(zhí)行。
特別是在大量并發(fā)的系統(tǒng)中， 保持事務(wù)簡短以減少并發(fā) 資源鎖定爭奪，將先得更為重要。
1、事務(wù)處理，禁止與用戶交互，在事務(wù)開始前完成用戶輸入。
2、在瀏覽數(shù)據(jù)時(shí)，盡量不要打開事務(wù)
3、盡可能使事務(wù)保持簡短。
4、考慮為只讀查詢使用快照隔離，以減少阻塞。
5、靈活地使用更低的事務(wù)隔離級(jí)別。
6、靈活地使用更低的游標(biāo)并發(fā)選項(xiàng)，例如開放式并發(fā)選項(xiàng)。
7、在事務(wù)中盡量使訪問的數(shù)據(jù)量最小。
