[SQL SERVER]透過 IMPLICIT_TRANSACTIONS 避免Block

2015-03-11

1405
0
SQL_SERVER
2015-12-17

之前透過非同步啟動Agent Job來處理一些耗時作業(參考 [SQL SERVER] 非同步呼叫SP不使用service broker or xp_cmdshell )

由於我的Agent Job 會透過SSIS平行進行複製檔案和新增檔案紀錄，

在沒有包交易情況下一切執行正常，但今天同事呼叫該SP並包交易就會發生錯誤(Block)，

主要是因為最外層交易未commit所導致，

今天我花了大部分時間針對SSIS package的 transactionoption屬性測試，

但不管如何SSIS package還是會join父交易，這裡記錄一下我透過IMPLICIT_TRANSACTIONS 來解決，

如有更好的方法還請告知。

SSIS control flow

新增檔案資料 to DB的程式碼是很單存的ADO.net且沒有使用任何交易

SP script

alter proc usp_t2
as
set nocount on
begin try
exec dbo.USP_asyunjob 'd:\Package.dtsx'
WHILE (1 = 1)
    BEGIN
        WAITFOR DELAY '00:00:2'
        IF EXISTS(
               SELECT 1
               FROM ttt with(nolock) where c1='rico'
           )
           break;
        IF NOT EXISTS(
               SELECT 1
               FROM   ttt with(nolock) where     c1='rico'
           )
           CONTINUE;
    END
SELECT * FROM   ttt with(nolock) where    c1='rico'
end try
begin CATCH
declare @msg nvarchar(4000)
set @msg='error'+error_message()
raiserror(@msg,16,1)
end CATCH