Integration Services and Isolation Level

Question

We have a data-warehousing package that our clients run during the day against their live transactional system. On most clients this seems to work fine but on busy clients we get deadlocking errors.

By default SSIS runs with an isolation level of Serializable which is the highest isolation level on SQL 2005.

The SSIS package is only reading data but the deadlocks are created because of the size of the queries and the locks being escalated to page locks while other processes are updating/inserting.

Clients are generally running the Standard Edition of SQL 2005 so that is the only functionality we can use.

Does anyone know if changing the IsolationLevel of the package to ReadCommitted (or other Isolation level) will resolve these locking issues. And if it will solve them are there any side-effects (phantom reads, non-repeatable reads etc)

Thanks,

Answer 1

There is a great discussion of read-committed and repeatable-read isolation levels, and potential issues they can produce:

http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx http://blogs.msdn.com/craigfr/archive/2007/05/09/repeatable-read-isolation-level.aspx

Whether it affects you, depends on the table schema and kind of updates that are running on your system.

If you are only reading data, your best option might be a snapshot isolation. SSIS does not support it, but you can avoid SSIS transactions and issue BEGIN TRANSACTION in the SQL code.