使用 IsolationLevel 讀取未送出和支持 TransactionOption 的 SSIS 阻塞自身

我的項目組件有IsolationLevel = ReadUncommitted和TransactionOption = supported。

我的查詢是對錶 Y 的選擇，驗證表 X 中是否不存在，並將結果寫入表 X。

SSIS 在 SQL Server 中打開兩個 id，一個 select 和一個 bulk。批量被選擇阻止。

我嘗試使用NOLOCK，SET ISOLATION LEVEL READ UNCOMMITTED但它們不起作用。

我能夠通過檢查“OLEDB Destination”上的“Check Constraint”來解決這個問題，但我不知道為什麼。

檢查您打開了多少筆交易

select @@opentran 

如果您只想不受阻塞的影響-並且您知道可能的副作用-臟讀-

您可以 在選擇之前添加此行：

set transaction isolation level read uncommitted

或者在您的選擇 中，您可以使用 WITH (NOLOCK) 提示，例如：

select a,b,c, from mytable with (nolock)

而不是 sp_who 請執行以下程式碼，並在此處發布結果：

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
   CASE es.transaction_isolation_level
       WHEN 0 THEN 'Unspecified'
       WHEN 1 THEN 'Read Uncommitted'
       WHEN 2 THEN 'Read Committed'
       WHEN 3 THEN 'Repeatable'
       WHEN 4 THEN 'Serializable'
       WHEN 5 THEN 'Snapshot'
   END
,COALESCE(es.cpu_time,0) 
   + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
   + COALESCE(es.writes,0) 
   + COALESCE(er.reads,0) 
   + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
   LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
   LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
   LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
   LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
   LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
   CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

where es.is_user_process = 1 
 and es.session_id <> @@spid

UNION 

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
   CASE es.transaction_isolation_level
       WHEN 0 THEN 'Unspecified'
       WHEN 1 THEN 'Read Uncommitted'
       WHEN 2 THEN 'Read Committed'
       WHEN 3 THEN 'Repeatable'
       WHEN 4 THEN 'Serializable'
       WHEN 5 THEN 'Snapshot'
   END
,COALESCE(es.cpu_time,0) 
   + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
   + COALESCE(es.writes,0) 
   + COALESCE(er.reads,0) 
   + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
   INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
   LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
   LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
   LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
   LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
   LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1 
 and es.session_id <> @@spid

這將向您展示實際導致阻塞的原因。這段程式碼來自這裡：

如何找到阻塞其他程序的睡眠會話的 T-SQL？

引用自：https://dba.stackexchange.com/questions/115676