CODEWITHSUNDEEP
ssislocking
Akash
Akash

Reputation: 1726

OLE DB Command in SSIS holds Exclusive lock on table indefinitely

I'm implementing an upsert operation using SSIS

The general package structure is 

<Begin transaction>

<read config values>

<Data flow task>

<commit transaction>

Data flow task is comprised of

<oledb Source>

<Lookup>

<matched output to Update using oledbcommand>

<No Match output to Insert using oledbcommand>

The package runs fine the 1st time, when there are only inserts

on the second run, when a set of updates have happened, and then an insert has happened, the Update statement ends up holding an exclusive lock on the destination table, goes to sleep with AWAITING COMMAND, while the Insert gets suspended and continues waiting for a lock.

To check the statements in the last para, I used the following commands:

select * from master.sys.sysprocesses where blocked<>0 or spid in (select blocked from master.sys.sysprocesses where blocked <>0)

Is there a way to fix this locking situation?

Upvotes: 1

Views: 6617

Answers (2)

billinkc
billinkc

Reputation: 61231

Since you are explicitly controlling your transactions, have you set the RetainSameConnection property to true? Otherwise, you will have two+ database connections to the same resource and inserting and updating the same table but on different transactions and that would result in the behaviour you are experiencing.

Upvotes: 4

Samuel Vanga
Samuel Vanga

Reputation: 501

OLEDB Command is known to be problematic. It is executed on one row at a time similar to a cursor. So it is slow, specially if you've large number of records to process. I can't exactly say why there is locking but I guess it's because of other processes trying to access the table at the same time.

It's better to change your data flow task to something like this:

oledb source

lookup

No match output to OLEDB Destination, Add a destination directly, you don't need OLEDB command here to do inserts.

Divert matched records to a staging table in destination database

In the control flow, use an execute sql task and update statements to perform updates: this now performs a set based updated. Faster than the original configuration.

This article on SQL Server Central walks through the implementation. Be advised it's using 2005, you need to make minor changes to the lookup transform to suite 2008.

Upvotes: 3

Related Questions