在 Sql Server 2005 中使用 UPDATE 语句避免死锁的最佳隔离级别 [英] Best Isolation Level to avoid deadlocks using an UPDATE sentence in Sql Server 2005

问题描述

我需要对 sql server 表执行更新语句.该表同时被另一个进程使用.因为有时会发生死锁，您建议使用哪种隔离级别来避免或最小化这些死锁?

I need execute an update statement over an sql server table. This table is used by another process at the same time. Because that sometimes deadlocks occurs, which Isolation Level do you recommend to avoid or minimize these deadlocks?

推荐答案

READ UNCOMMITTED

但是这允许进程在事务提交之前读取数据，这就是所谓的脏读.进一步阅读

But that allows the process to read the data before a transaction has committed, what is known as a dirty read. Further Reading

您可能更喜欢打开行版本控制，更新会创建该行的新版本，并且任何其他 select 语句都使用旧版本，直到该行已提交.为此，请打开 READ_COMMITTED_SNAPSHOT 模式.有更多信息这里.维护行的版本会产生开销，但它消除了 UPDATE/SELECT 死锁.

You may prefer to turn on row versioning, the update creates a new version of the row and any other select statements use the old version until this one has committed. To do this turn on READ_COMMITTED_SNAPSHOT mode. There is more info here. There is an overhead involved maintaining the versions of the rows but it removes UPDATE/SELECT deadlocks.

这篇关于在 Sql Server 2005 中使用 UPDATE 语句避免死锁的最佳隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！