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

事務(wù)

SQL Server中的事務(wù)是單個(gè)的工作單元。如果某一事務(wù)成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù)修改均會(huì)提交，成為數(shù)據(jù)庫(kù)中永久的組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所有的數(shù)據(jù)修改均被清除。

在SQL Server中使用事務(wù)有可能會(huì)造成一些預(yù)想不到的結(jié)果，具體來(lái)說(shuō)有臟讀，不可重復(fù)讀和幻讀三種結(jié)果。

• 臟讀：臟讀是指當(dāng)一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)庫(kù)，并且對(duì)數(shù)據(jù)進(jìn)行修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，另外一個(gè)事務(wù)也在訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。
• 不可重復(fù)讀：在一個(gè)事物內(nèi)多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒(méi)有結(jié)束時(shí)，另外一個(gè)事物也在訪問(wèn)該同一數(shù)據(jù)，那么在第一個(gè)事務(wù)兩次讀取之間，由于第二個(gè)事務(wù)的修改，第一個(gè)事務(wù)兩次讀取到的數(shù)據(jù)可能不一樣。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀取到的數(shù)據(jù)不一樣，因此稱為不可重復(fù)讀。
• 幻讀：幻讀是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行修改，這種修改設(shè)計(jì)到表中的全部數(shù)據(jù)行。同時(shí)第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這個(gè)修改時(shí)向同一個(gè)表中插入一行新數(shù)據(jù)，這樣第一個(gè)事務(wù)的用戶發(fā)現(xiàn)還有一條數(shù)據(jù)沒(méi)有修改，像發(fā)生了幻覺(jué)一樣，因此稱為幻讀

在SQL Server中給事務(wù)指定一個(gè)隔離級(jí)別，這個(gè)隔離級(jí)別定義該事務(wù)與其他事務(wù)進(jìn)行資源或數(shù)據(jù)更改相隔離的級(jí)別。事務(wù)隔離級(jí)別決定了是否鎖定SQL Server對(duì)象，下面是SQL Server中的事務(wù)隔離級(jí)別。

• Rdad Uncommitted： 讀取數(shù)據(jù)不需要等待解鎖，這種方式會(huì)讀到臟數(shù)據(jù)，因?yàn)樽x取的數(shù)據(jù)有可能是還沒(méi)有更新的數(shù)據(jù)。這種隔離級(jí)別最低，會(huì)造成臟讀，不可重復(fù)讀和幻讀的結(jié)果，并發(fā)性最高。
• Read Committed： 讀取數(shù)據(jù)需要等待解鎖，這樣會(huì)讀取到最新的被更新的數(shù)據(jù)。Read Committed不會(huì)造成臟讀的問(wèn)題，但是會(huì)造成不可重復(fù)的和幻讀的問(wèn)題。Read Committed是SQL Server的默認(rèn)設(shè)置。
• Repeatable Read： 與Read Committed類似，它會(huì)鎖定所讀取的所有行，但是沒(méi)有其他的連接可以更新或插入數(shù)據(jù)，這樣如果select語(yǔ)句可能選擇到這條新跟新或插入的數(shù)據(jù)，這條數(shù)據(jù)記錄是不會(huì)出現(xiàn)在select結(jié)果中的。同時(shí)被選擇出的數(shù)據(jù)也不能被其他連接更改，直到讀取動(dòng)作執(zhí)行結(jié)束或者回滾結(jié)束。這種隔離級(jí)別不會(huì)造成臟讀和不可重復(fù)讀，但是會(huì)造成幻讀。
• Serializable：和Repeatable Read類似，不過(guò)沒(méi)有其他的連接可以插入或更新數(shù)據(jù)，同時(shí)如果在下次查詢中任然使用這種事務(wù)隔離級(jí)別，你會(huì)得到相同的查詢結(jié)果，就是說(shuō)更新或新插入的數(shù)據(jù)任然不會(huì)出現(xiàn)在查詢結(jié)果中。這種隔離級(jí)別不會(huì)造成臟讀，不可重復(fù)讀或幻讀。

還有兩種是SQL Server 2005中新添加的事務(wù)隔離級(jí)別

• 一種Read Committed級(jí)別的變異，當(dāng)你把數(shù)據(jù)庫(kù)的隔離級(jí)別設(shè)置成READ_COMMITTED_SNAPHOT，任何使用Read Committed級(jí)別的的事務(wù)不再需要鎖定數(shù)據(jù)對(duì)象。執(zhí)行語(yǔ)句時(shí)會(huì)得到select語(yǔ)句開(kāi)始執(zhí)行之時(shí)會(huì)得到所有最新的結(jié)果。
• SNAPSHOT：一種全新的級(jí)別SNAPSHOT，當(dāng)你在任何數(shù)據(jù)庫(kù)對(duì)象中設(shè)置事務(wù)的隔離級(jí)別為ALLOW_SNAPSHOT_ISOLATION時(shí)，其他事務(wù)都不會(huì)遇到共享鎖，查詢結(jié)果會(huì)得到所有更新之后的行。這種隔離級(jí)別不會(huì)造成臟讀，不可更新讀和幻讀的結(jié)果

 所有上述的事務(wù)處理都在tempdb數(shù)據(jù)庫(kù)中一個(gè)類似版本庫(kù)的數(shù)據(jù)對(duì)象中自動(dòng)進(jìn)行，當(dāng)遇到更新未被提交的情況，數(shù)據(jù)引擎會(huì)檢索這個(gè)版本庫(kù)得到合適的提交結(jié)果。維護(hù)這個(gè)版本庫(kù)的工作由SQL Server自動(dòng)進(jìn)行，不需要人為干預(yù)。

SSIS中的事務(wù)處理

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

• Required-如有事務(wù)則添加，否則新添加一個(gè)
• Supported-如有有事務(wù)添加一個(gè)，沒(méi)有則不添加，這是默認(rèn)選項(xiàng)+
• 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è)置如下 

    1/*命名*/                
    2Create TranQueue Table    
    3/*SQLstatement設(shè)置*/    
    4IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = 
    5
    6OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )
    7BEGIN
    8execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')
    9END
   10/*命名*/
   11Populate TranQueue        
   12/*SQLstatement設(shè)置*/
   13INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT
   14
   15(NVARCHAR(23), GETDATE(), 121))
   16/*命名*/
   17Create TranQueueHistory table    
   18/*SQLstatement設(shè)置*/
   19IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = 
   20
   21OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )
   22BEGIN
   23execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')
   24END

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é)束，更新歷史記錄

   1DELETE TOP(10) dbo.TranQueue
   2OUTPUT DELETED.*
   3INTO dbo.TranQueueHistory
   4FROM 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，類型為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ǔ)句查詢得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒(méi)有提交

1SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)

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

1SELECT * FROM dbo.TranQueue

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

1 DELETE TOP(10) dbo.TranQueue
2  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ǔ)句查詢得到一條記錄 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è)表中。