主頁(yè) > 知識(shí)庫(kù) > SQL Transcation的一些總結(jié)分享

SQL Transcation的一些總結(jié)分享

熱門(mén)標(biāo)簽:重慶防封電銷(xiāo)機(jī)器人供應(yīng)商 南京電銷(xiāo)外呼系統(tǒng)運(yùn)營(yíng)商 威海智能語(yǔ)音外呼系統(tǒng) 西安青牛防封電銷(xiāo)卡 400電話申請(qǐng)需要開(kāi)戶(hù)費(fèi)嗎 山西語(yǔ)音外呼系統(tǒng)價(jià)格 北京辦理400電話多少 智能語(yǔ)音外呼系統(tǒng)哪個(gè)牌子好 溫州語(yǔ)音外呼系統(tǒng)代理
1.1.1 摘要
相信大家對(duì)于SQL Transcation再熟悉不過(guò),它確保了數(shù)據(jù)庫(kù)的數(shù)據(jù)一致性和安全性,尤其在對(duì)數(shù)據(jù)執(zhí)行增刪時(shí),如果發(fā)生異常和錯(cuò)誤它就會(huì)觸發(fā)事務(wù)回滾,從而確保了我們數(shù)據(jù)的一致性和安全性,下面我們將通過(guò)分四部分介紹事件(Transcation)。

1.1.2 正文
首先讓我們通過(guò)一個(gè)具體的例子介紹Transcation的使用,假如我們的數(shù)據(jù)庫(kù)中有一個(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ǔ)過(guò)程把數(shù)據(jù)插入到該表中,存儲(chǔ)過(guò)程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ǔ)過(guò)程,接著讓我們執(zhí)行該存儲(chǔ)過(guò)程。

圖2執(zhí)行存儲(chǔ)過(guò)程的消息


通過(guò)上圖我們知道在執(zhí)行存儲(chǔ)過(guò)程中發(fā)生了異常,而且是由于值“20111111”數(shù)據(jù)超出了tinyint的范圍產(chǎn)生的,現(xiàn)在讓我們看一下數(shù)據(jù)插入的情況。

圖3 UserInfo表中數(shù)據(jù)

我們發(fā)現(xiàn)只插入了兩行數(shù)據(jù),而第三行數(shù)據(jù)沒(méi)有成功插入,但為了確保數(shù)據(jù)完整性,我們要把數(shù)據(jù)全部插入或全部不插入,這時(shí)我們可以考慮使用Transcation來(lái)確保數(shù)據(jù)完整性和安全性。
接著讓我們修改一下存儲(chǔ)過(guò)程SPAddDataToUserInfo,在存儲(chǔ)過(guò)程中添加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)在我們?cè)賵?zhí)行一次存儲(chǔ)過(guò)程看一下是否全部不插入到表中。

圖4 UserInfo表中數(shù)據(jù)

我們發(fā)現(xiàn)結(jié)果和沒(méi)有添加Transcation處理一樣,數(shù)據(jù)依然插入到表中。這究竟是什么原因呢?也許細(xì)心的你已經(jīng)發(fā)現(xiàn)了,我們沒(méi)有添加事務(wù)回滾——ROLLBACK。
但我們究竟要在哪里添加事務(wù)回滾(ROLLBACK)呢?或更具體地說(shuō):“究竟什么時(shí)候我們要觸發(fā)事務(wù)回滾(ROLLBACK)呢”?
由于我們數(shù)據(jù)插入失敗是因?yàn)椴迦脒^(guò)程發(fā)生了異常情況,那么我們就要捕獲異常和處理異常,那就是TRY/CATCH的設(shè)計(jì)了,好讓我們繼續(xù)完善我們的存儲(chǔ)過(guò)程吧。
復(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ǔ)過(guò)程添加了異常處理機(jī)制TRY/CATCH(注意:SQLSERVER 2005或之后的版本才支持TRY/CATCH),接下來(lái)讓我們?cè)賵?zhí)行一次存儲(chǔ)過(guò)程。
圖5 UserInfo表中數(shù)據(jù)

看上去我們已經(jīng)把問(wèn)題的解決了,我們知道存儲(chǔ)過(guò)程可以?xún)?nèi)嵌存儲(chǔ)過(guò)程或函數(shù),所以我們把上面的存儲(chǔ)過(guò)程SPAddDataToUserInfo內(nèi)嵌到存儲(chǔ)過(guò)程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ǔ)過(guò)程SPAddDataToUserInfo會(huì)發(fā)生異常,它會(huì)回滾事務(wù)(ROLLBACK),但SPMultiDataToUserInfo是發(fā)生回滾還是繼續(xù)插入數(shù)據(jù)呢?

圖6 執(zhí)行存儲(chǔ)過(guò)程消息

圖7 UserInfo表中數(shù)據(jù)

在插入的過(guò)程發(fā)生了異常,檢查UserInfo表數(shù)據(jù)并沒(méi)有插入表中,這符合我們?cè)O(shè)計(jì)的意圖,但我們發(fā)現(xiàn)異常不僅僅是產(chǎn)生于插入的數(shù)據(jù)超出了數(shù)值的范圍,還包含事務(wù)計(jì)數(shù)異常。

由于定義了兩個(gè)存儲(chǔ)過(guò)程,而且SPAddDataToUserInfo內(nèi)嵌在SPMultiDataToUserInfo中,在執(zhí)行這兩個(gè)存儲(chǔ)過(guò)程中,它們都發(fā)生了異常并且進(jìn)行事務(wù)回滾(因?yàn)閁ser為空)。

由于插入的數(shù)據(jù)超出了數(shù)值的范圍的異常是我們特意引起的,而事務(wù)計(jì)數(shù)異常這是預(yù)期之外的異常。

接下來(lái)讓我們看一下究竟是什么原因引起了該異常,這里我們通過(guò)輸出Transactions計(jì)數(shù)來(lái)查看問(wèn)題所在。
復(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

上面我們通過(guò)輸出事務(wù)的計(jì)數(shù)(@@TRANCOUNT)來(lái)查看在事務(wù)回滾時(shí),事務(wù)計(jì)數(shù)器的變化。
圖8 存儲(chǔ)過(guò)程執(zhí)行消息

通過(guò)上圖我們發(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ù)異常的原因,所以我們?cè)谶M(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)在我們?cè)黾恿耸聞?wù)計(jì)數(shù)器的判斷,當(dāng)計(jì)數(shù)器為0時(shí),不進(jìn)行事務(wù)回滾,這樣就沒(méi)有了之前事務(wù)計(jì)數(shù)器異常了。

圖9 存儲(chǔ)過(guò)程執(zhí)行消息

現(xiàn)在對(duì)于事務(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語(yǔ)句也可以是多條SQL語(yǔ)句。
事務(wù)具有四個(gè)特性
原子性:不可分隔、成則具成、敗則具敗。
一致性:事務(wù)在完成時(shí),必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)
隔離性:獨(dú)立的執(zhí)行互不干擾。由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。
持久性:務(wù)完成之后,它對(duì)于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。
應(yīng)用程序主要通過(guò)指定事務(wù)啟動(dòng)和結(jié)束的時(shí)間來(lái)控制事務(wù)。
啟動(dòng)事務(wù):使用 API 函數(shù)和 Transact-SQL 語(yǔ)句,可以按顯式、自動(dòng)提交或隱式的方式來(lái)啟動(dòng)事務(wù)。
結(jié)束事務(wù):您可以使用 COMMIT(成功) 或 ROLLBACK(失敗) 語(yǔ)句,或者通過(guò) API 函數(shù)來(lái)結(jié)束事務(wù)。
事務(wù)模式分為:顯示事務(wù)模式、隱式事務(wù)模式、自動(dòng)事務(wù)模式。在SQL常用的是顯示模式。
創(chuàng)建事務(wù)的原則:
盡可能使事務(wù)保持簡(jiǎn)短很重要,當(dāng)事務(wù)啟動(dòng)后,數(shù)據(jù)庫(kù)管理系統(tǒng) (DBMS) 必須在事務(wù)結(jié)束之前保留很多資源、以保證事務(wù)的正確安全執(zhí)行。
特別是在大量并發(fā)的系統(tǒng)中, 保持事務(wù)簡(jiǎn)短以減少并發(fā) 資源鎖定爭(zhēng)奪,將先得更為重要。
1、事務(wù)處理,禁止與用戶(hù)交互,在事務(wù)開(kāi)始前完成用戶(hù)輸入。
2、在瀏覽數(shù)據(jù)時(shí),盡量不要打開(kāi)事務(wù)
3、盡可能使事務(wù)保持簡(jiǎn)短。
4、考慮為只讀查詢(xún)使用快照隔離,以減少阻塞。
5、靈活地使用更低的事務(wù)隔離級(jí)別。
6、靈活地使用更低的游標(biāo)并發(fā)選項(xiàng),例如開(kāi)放式并發(fā)選項(xiàng)。
7、在事務(wù)中盡量使訪問(wèn)的數(shù)據(jù)量最小。

標(biāo)簽:金昌 濟(jì)寧 宜春 中衛(wèi) 河源 貸款群呼 黃山 新余

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL Transcation的一些總結(jié)分享》,本文關(guān)鍵詞  SQL,Transcation,的,一些,總結(jié),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL Transcation的一些總結(jié)分享》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于SQL Transcation的一些總結(jié)分享的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章