SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

Question

My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported.

My query is a select on table Y verifying if not exists in table X and the result is written to table X.

SSIS opens two ids in SQL Server, a select and a bulk. The bulk is being blocked by the select.

I tried to use NOLOCK andSET ISOLATION LEVEL READ UNCOMMITTED but they do not work.

I was able to resolve this checking the "Check Constraint" on "OLEDB Destination" and I don't know why.

I think you may be confused. The Isolation level refers to accessing the data and works the same as nolock. Documentation can be found here however the transaction option refers to the ability to use a transaction with your sql query so that you can rollback if you wish. BEGIN TRANSACTION COMMIT TRANSACTION In order to know why you are being blocked we need more info on the package. Are the insert and the select going to the same table? What does the query look like? That sort of information will be vital to my ability to help you. — Zane, Sep 21 '15 at 18:26

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

to check how many transactions you have open

select @@opentran

if all you want to do is not to be affected by blocking - and you are aware of the possible side effects - dirty reads -

you can either before the select add this line:

set transaction isolation level read uncommitted

or within your select you can use the hint WITH (NOLOCK), example:

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

instead of sp_who please run the following code, and post the results here:

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

this will show you what is actually causing the blocking. this code comes from here:

how to find the T-SQL of a sleeping session that is blocking some other process?

Is the package opening 2 concurrent connections to the same SQL instance/database, and reading from one connection and writing on the other, with the same table as source & target ? — p w, Sep 22 '15 at 06:31
@marcello miorelli i have alredy tried this but is the select blocking the insert(BULK) — Omoreira, Sep 23 '15 at 00:49
@pw the package open 2 connections one for insert and other to select i put an image from sp_who showing the block. this two ids is opened by SSIS. the insert execute an trigger, i executed the project once with disabled trigger and nothing change. — Omoreira, Sep 23 '15 at 00:53
@marcellomiorelli i was able to find what is the problem. When i execute the package with "check constraint" disable the select bring the first batch and start to insert but when he try to bring other batchs, the insert transaction lock the table and he cant get the lock to disable the constraints to execute the select. Lock "LCK_M_SCH_S" — Omoreira, Sep 23 '15 at 15:42
@Omoreira I knew that with more visibility you would figure the bloking issue out — Marcello Miorelli, Sep 23 '15 at 15:59

SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

1 Answers1