集成服务和隔离级别
我们有一个数据仓库包，我们的客户可以在白天针对他们的实时交易系统运行。 在大多数客户端上，这似乎工作正常，但在繁忙的客户端上，我们会遇到死锁错误。
默认情况下，SSIS 以可序列化的隔离级别运行，这是 SQL 2005 上的最高隔离级别。SSIS
包仅读取数据，但由于查询的大小和其他进程时的锁升级为页锁，因此会产生死锁正在更新/插入。
客户端通常运行 SQL 2005 标准版，因此这是我们唯一可以使用的功能。
有谁知道将包的 IsolationLevel 更改为 ReadCommited （或其他隔离级别）是否可以解决这些锁定问题。 如果它能解决这些问题，是否有任何副作用（幻读、不可重复读取等）
谢谢，
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,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
关于读提交和可重复读隔离级别以及它们可能产生的潜在问题进行了深入的讨论：
http://blogs.msdn.com/craigfr/archive/2007/04/25/read-commissed-isolation-level.aspx
http://blogs.msdn。 com/craigfr/archive/2007/05/09/repeatable-read-isolation-level.aspx
它是否影响您，取决于表架构和系统上运行的更新类型。
如果您只读取数据，那么最好的选择可能是快照隔离。 SSIS 不支持它，但您可以避免 SSIS 事务并在 SQL 代码中发出 BEGIN TRANSACTION。
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.