SQL點(diǎn)滴之SSIS中的事務(wù)處理
在介紹了SQL Server中的事務(wù)的概念之后，現(xiàn)在我們可以繼續(xù)介紹這次的點(diǎn)滴了。
我們可以把SSIS中的整個package包含在一個事務(wù)中，但是如果在package的執(zhí)行過程中有一個表需要鎖定應(yīng)該怎么處理呢？SSIS內(nèi)建的事務(wù)處理可以解決這個問題。
SSIS中的包，容器（例如Loop，F(xiàn)oreach Loop，Sequence）或者一個單獨(dú)的任務(wù)中都可以設(shè)置事務(wù)處理選項。事務(wù)處理選項有下面一些值
- Required-如有事務(wù)則添加，否則新添加一個
- Supported-如有有事務(wù)添加一個，沒有則不添加，這是默認(rèn)選項+
- NotSupported-不添加事務(wù)處理
內(nèi)建的事務(wù)處理要使用Distributed Transaction Coordinator(MSDTC)服務(wù)，這個服務(wù)必須開啟。MSDTC允許使用分布式事務(wù)處理，例如在一個事務(wù)中同時處理SQL Server數(shù)據(jù)庫和Oracle數(shù)據(jù)庫。如果沒有開啟這個服務(wù)會得到下面的錯誤提示.
- 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ù)隔離級別是Serializable，這種級別會影響鎖的持續(xù)時間。下面我們來用一個例子說明在如何package中鎖定一個表
1.新建一個SequenceContainer，命名為Test Initialization。
2.這個SequenceContainer主要用來創(chuàng)建測試的環(huán)境，創(chuàng)建連個表TranQueue，TranQueueHistory，向***個表中添加一條記錄，這樣模擬一個事物處理過程。我們只是使用這個SequenceContainer來創(chuàng)建測試環(huán)境，所以設(shè)置它的TransactionOption選項為NotSupported在這個SequenceContainer中依次添加三個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)建第二個SequenceContainer，命名為Process，TransactionOption屬性設(shè)置為Supported，這樣就會添加事務(wù)處理。
4.在這個SequenceContainer中添加一個Execute SQL，命名為ProcessTranQueue，它的SQLStatement設(shè)置為下面的語句。這個語句的作用，模擬事務(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.添加一個Execute SQL，命名為Placeholder for Breakpoint。這個任務(wù)不進(jìn)行任何操作，只是為了在這設(shè)置一個斷點(diǎn)然后在這里停下來讓我們有時間驗(yàn)證是否會鎖定表。
6.右擊Control Flow界面添加一個變量v_SimulateFailure，類型為Int32，值為1。
7.添加一個Execute SQL命名為Simulate Failure。用它來模擬錯誤，設(shè)置SQLStatement為select 1/0，當(dāng)pacakage執(zhí)行到這里的時候會造成錯誤進(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)。這樣之后這個自定義變量的值為1的時候才會繼續(xù)往下執(zhí)行。
9.執(zhí)行package，會得到如圖1的結(jié)果，package在斷點(diǎn)處終止。
圖1
10.打開SQL Server Management Studion,選擇對應(yīng)的數(shù)據(jù)庫，新建一個Query，執(zhí)行下面的語句，NOLOCK選項忽略鎖，這個語句查詢得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒有提交
- SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
11.執(zhí)行下面的語句
- SELECT * FROM dbo.TranQueue
語句將阻塞在這里，語句一直停留在執(zhí)行狀態(tài)，不會結(jié)束。因?yàn)樵赑rocess TranQueue任務(wù)中我們使用TABLOCKX，在這里將等待任務(wù)回滾或者提交。或者可以寫成這樣，它任然會阻塞
- DELETE TOP(10) dbo.TranQueue
- INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))
12. 點(diǎn)擊Continue按鈕或者Debuge按鈕，會看到package執(zhí)行失敗，執(zhí)行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因?yàn)閳?zhí)行了回滾，不會得到任何結(jié)果。SELECT * FROM dbo.TranQueue，任然有一條記錄。
- SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
NOLOCK提示忽略鎖，這個語句查詢得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒有提交
13. 執(zhí)行下面的語句，
- SELECT * FROM dbo.TranQueue
sql語句將阻塞在這里，語句一直執(zhí)行。因?yàn)樵赑rocess TranQueue任務(wù)中我們使用TABLOCKX，在這里將等待任務(wù)回滾或者提交。或者可以寫成這樣
- DELETE TOP(10) dbo.TranQueue;
- INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))，
它任然會阻塞
14. 點(diǎn)擊Continue按鈕或者Debuge按鈕，會看到package執(zhí)行失敗，執(zhí)行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因?yàn)閳?zhí)行了回滾，不會得到任何結(jié)果。執(zhí)行SELECT * FROM dbo.TranQueue，任然有一條記錄。
如果設(shè)置變量User::v_SimulateFailure的值為0，不會執(zhí)行Simulate Failure任務(wù)，就不會回滾，TranQueue中的記錄會被寫入到TranQueueHistory中。這里有一個很有意思的語句：
- DELETE TOP(10) dbo.TranQueue
- OUTPUT DELETED.*
- INTO dbo.TranQueueHistory
- FROM dbo.TranQueue WITH (TABLOCKX)
如果兩個表的結(jié)構(gòu)有一部分是是一樣的，現(xiàn)在想把一個表的數(shù)據(jù)導(dǎo)入到另外一個表中，可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable，這樣***個表中的數(shù)據(jù)會被“剪切”到第二個表中。
原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/04/10/2011806.html
【編輯推薦】
