在介紹了SQL Server中的事務(wù)的概念之后，現(xiàn)在我們可以繼續(xù)介紹這次的點(diǎn)滴了。

我們可以把SSIS中的整個(gè)package包含在一個(gè)事務(wù)中，但是如果在package的執(zhí)行過(guò)程中有一個(gè)表需要鎖定應(yīng)該怎么處理呢？SSIS內(nèi)建的事務(wù)處理可以解決這個(gè)問(wèn)題。

SSIS中的包，容器（例如Loop，F(xiàn)oreach Loop，Sequence）或者一個(gè)單獨(dú)的任務(wù)中都可以設(shè)置事務(wù)處理選項(xiàng)。事務(wù)處理選項(xiàng)有下面一些值       　　　　　　　　

1. Required-如有事務(wù)則添加，否則新添加一個(gè)
2. Supported-如有有事務(wù)添加一個(gè)，沒(méi)有則不添加，這是默認(rèn)選項(xiàng)+
3. NotSupported-不添加事務(wù)處理

內(nèi)建的事務(wù)處理要使用Distributed Transaction Coordinator(MSDTC)服務(wù)，這個(gè)服務(wù)必須開(kāi)啟。MSDTC允許使用分布式事務(wù)處理，例如在一個(gè)事務(wù)中同時(shí)處理SQL Server數(shù)據(jù)庫(kù)和Oracle數(shù)據(jù)庫(kù)。如果沒(méi)有開(kāi)啟這個(gè)服務(wù)會(huì)得到下面的錯(cuò)誤提示.

Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.".  
The DTC transaction failed to start. This could occur because the MSDTC Service is not running.  

注意SSIS中包中的元素的事務(wù)隔離級(jí)別是Serializable，這種級(jí)別會(huì)影響鎖的持續(xù)時(shí)間。下面我們來(lái)用一個(gè)例子說(shuō)明在如何package中鎖定一個(gè)表

1.新建一個(gè)SequenceContainer，命名為T(mén)est Initialization。

2.這個(gè)SequenceContainer主要用來(lái)創(chuàng)建測(cè)試的環(huán)境，創(chuàng)建連個(gè)表TranQueue，TranQueueHistory，向***個(gè)表中添加一條記錄，這樣模擬一個(gè)事物處理過(guò)程。我們只是使用這個(gè)SequenceContainer來(lái)創(chuàng)建測(cè)試環(huán)境，所以設(shè)置它的TransactionOption選項(xiàng)為NotSupported在這個(gè)SequenceContainer中依次添加三個(gè)Execute SQL，依次他們的設(shè)置如下 

/*命名*/                   
Create TranQueue Table       
/*SQLstatement設(shè)置*/       
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =    
   
OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )   
BEGIN   
execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')   
END 
/*命名*/  
Populate TranQueue          
/*SQLstatement設(shè)置*/  
INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT 
 
(NVARCHAR(23), GETDATE(), 121))  
/*命名*/  
Create TranQueueHistory table      
/*SQLstatement設(shè)置*/  
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =   
 
OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )  
BEGIN 
execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')  
END 

3.創(chuàng)建第二個(gè)SequenceContainer，命名為Process，TransactionOption屬性設(shè)置為Supported，這樣就會(huì)添加事務(wù)處理。  

4.在這個(gè)SequenceContainer中添加一個(gè)Execute SQL，命名為ProcessTranQueue，它的SQLStatement設(shè)置為下面的語(yǔ)句。這個(gè)語(yǔ)句的作用，模擬事務(wù)處理，刪除TranQueue表中前10條數(shù)據(jù)；OUTPUT字句將刪除的數(shù)據(jù)插入到TranQueueHistory表中，模擬處理結(jié)束，更新歷史記錄

DELETE TOP(10) dbo.TranQueue  
OUTPUT DELETED.*  
INTO dbo.TranQueueHistory  
FROM dbo.TranQueue WITH (TABLOCKX)  

5.添加一個(gè)Execute SQL，命名為Placeholder for Breakpoint。這個(gè)任務(wù)不進(jìn)行任何操作，只是為了在這設(shè)置一個(gè)斷點(diǎn)然后在這里停下來(lái)讓我們有時(shí)間驗(yàn)證是否會(huì)鎖定表。
 
6.右擊Control Flow界面添加一個(gè)變量v_SimulateFailure，類(lèi)型為Int32，值為1。

7.添加一個(gè)Execute SQL命名為Simulate Failure。用它來(lái)模擬錯(cuò)誤，設(shè)置SQLStatement為select 1/0，當(dāng)pacakage執(zhí)行到這里的時(shí)候會(huì)造成錯(cuò)誤進(jìn)而回滾。

8.右擊Placeholder for Breakpoint和Simulate Failure之間的連線，點(diǎn)擊Edit，設(shè)置Evaluation operation為Expression and Constraint，設(shè)置Expression為@[User::v_SimulateFailure] == 1，其他保持默認(rèn)。這樣之后這個(gè)自定義變量的值為1的時(shí)候才會(huì)繼續(xù)往下執(zhí)行。
 
9.執(zhí)行package，會(huì)得到如圖1的結(jié)果，package在斷點(diǎn)處終止。 

  圖1              

10.打開(kāi)SQL Server Management Studion,選擇對(duì)應(yīng)的數(shù)據(jù)庫(kù)，新建一個(gè)Query，執(zhí)行下面的語(yǔ)句，NOLOCK選項(xiàng)忽略鎖，這個(gè)語(yǔ)句查詢(xún)得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒(méi)有提交

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  

11.執(zhí)行下面的語(yǔ)句

SELECT * FROM dbo.TranQueue  

語(yǔ)句將阻塞在這里，語(yǔ)句一直停留在執(zhí)行狀態(tài)，不會(huì)結(jié)束。因?yàn)樵赑rocess TranQueue任務(wù)中我們使用TABLOCKX，在這里將等待任務(wù)回滾或者提交。或者可以寫(xiě)成這樣，它任然會(huì)阻塞

DELETE TOP(10) dbo.TranQueue  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))  

12. 點(diǎn)擊Continue按鈕或者Debuge按鈕，會(huì)看到package執(zhí)行失敗，執(zhí)行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因?yàn)閳?zhí)行了回滾，不會(huì)得到任何結(jié)果。SELECT * FROM dbo.TranQueue，任然有一條記錄。

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  

NOLOCK提示忽略鎖，這個(gè)語(yǔ)句查詢(xún)得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒(méi)有提交

13.  執(zhí)行下面的語(yǔ)句，

SELECT * FROM dbo.TranQueue  

sql語(yǔ)句將阻塞在這里，語(yǔ)句一直執(zhí)行。因?yàn)樵赑rocess TranQueue任務(wù)中我們使用TABLOCKX，在這里將等待任務(wù)回滾或者提交?；蛘呖梢詫?xiě)成這樣

DELETE TOP(10) dbo.TranQueue;  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))， 

它任然會(huì)阻塞     

14.  點(diǎn)擊Continue按鈕或者Debuge按鈕，會(huì)看到package執(zhí)行失敗，執(zhí)行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因?yàn)閳?zhí)行了回滾，不會(huì)得到任何結(jié)果。執(zhí)行SELECT * FROM dbo.TranQueue，任然有一條記錄。

如果設(shè)置變量User::v_SimulateFailure的值為0，不會(huì)執(zhí)行Simulate Failure任務(wù)，就不會(huì)回滾，TranQueue中的記錄會(huì)被寫(xiě)入到TranQueueHistory中。這里有一個(gè)很有意思的語(yǔ)句：

DELETE TOP(10) dbo.TranQueue  
OUTPUT DELETED.*  
INTO dbo.TranQueueHistory  
FROM dbo.TranQueue WITH (TABLOCKX)  

如果兩個(gè)表的結(jié)構(gòu)有一部分是是一樣的，現(xiàn)在想把一個(gè)表的數(shù)據(jù)導(dǎo)入到另外一個(gè)表中，可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable，這樣***個(gè)表中的數(shù)據(jù)會(huì)被“剪切”到第二個(gè)表中。

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/04/10/2011806.html

【編輯推薦】

1. SQL點(diǎn)滴之使用attach功能出現(xiàn)錯(cuò)誤及解決方法
2. SQL點(diǎn)滴之一個(gè)簡(jiǎn)單的字符串分割函數(shù)
3. SQL點(diǎn)滴之重置win7登錄密碼對(duì)SQL登錄的影響
4. MySQL數(shù)據(jù)庫(kù)的優(yōu)化（上）單機(jī)MySQL數(shù)據(jù)庫(kù)的優(yōu)化
5. MySQL數(shù)據(jù)庫(kù)的優(yōu)化（下）MySQL數(shù)據(jù)庫(kù)的高可用架構(gòu)方案