sql-server-2005 - 默认 SQL Server IsolationLevel 更改

Question

我们有一位客户在使用我们的数据库应用程序时遇到了一些阻塞问题。我们要求他们运行 Blocked Process Report 跟踪，他们给我们的跟踪显示在 SELECT 和 UPDATE 操作之间发生了阻塞。跟踪文件显示以下内容:

在不同的隔离级别执行相同的 SELECT 查询。一条跟踪显示了 Serializable IsolationLevel，而后面的跟踪显示了可重复读取 IsolationLevel。我们在执行查询时不使用显式事务。

UPDATE 查询正在以可重复读取隔离级别执行，但被 SELECT 查询阻止。这是意料之中的，因为我们的更新包含在 IsolationLevel 为 RepeatableRead 的显式事务中。

所以基本上我们不知道为什么 SELECT 查询的隔离级别不会是默认的 ReadCommitted IsolationLevel 但更令人困惑的是，为什么查询的 IsolationLevel 会随着时间而改变？只有一位客户看到此行为，因此我们怀疑这可能是数据库配置问题。

有任何想法吗？

提前致谢，

格雷厄姆

Answer 1

在您的场景中，我建议将隔离级别显式设置为快照 - 这将通过防止锁定来防止读取妨碍写入(插入和更新)，但这些读取仍然是“好”读取(即不是脏数据)它与 NOLOCK 不同)

一般来说，我发现我的查询有锁定问题，我手动控制应用的锁定。例如我会使用行级锁进行更新以避免页/表级锁定，并将我的读取设置为 readpast(接受我可能会错过一些数据，在某些情况下可能没问题)
链接|编辑|删除|标志

编辑 - 将所有评论合并到答案中

作为优化过程的一部分，sql server 避免在它知道没有更改的页面上获得提交的读取，并自动回退到较小的锁定策略。在您的情况下，sql server 从可序列化读取下降到可重复读取。

问:感谢提供有关降低隔离级别的有用信息。考虑到我们没有为 SELECT 使用显式事务，您能想到它首先使用 Serializable IsolationLevel 的任何原因 - 我们的理解是隐式事务将使用 ReadCommitted 吗？

A:默认情况下，如果这是您的默认隔离级别，SQL Server 将使用 Read Commmited 但是如果您没有在查询中额外指定锁定策略，您基本上是在对 sql server 说“做你认为最好的事情，但我的偏好是读提交”。由于 SQL Server 是可以自由选择的，所以它这样做是为了优化查询。 (sql server中的优化算法很复杂，我自己也不是很懂)。不在事务中显式执行不会影响 sql server 使用的隔离级别。

问:最后一件事，SQL Server 增加隔离级别(以及可能需要的锁数量)来优化查询是否合理？我还想知道如果池连接的重用继承了上次使用的隔离级别，它是否会影响这一点？

A: Sql server 会在一个叫做“Lock Escalation”的过程中做到这一点。来自 http://support.microsoft.com/kb/323630 ，我引用:“Microsoft SQL Server 动态确定何时执行锁升级。在做出此决定时，SQL Server 会考虑特定扫描中持有的锁数量、整个事务持有的锁数量，以及整个系统中用于锁定的内存。通常，SQL Server 的默认行为导致锁定升级仅发生在可以提高性能的那些点，或者当您必须将过多的系统锁定内存减少到更合理的水平时但是，某些应用程序或查询设计可能会在不希望的时候触发锁升级，而升级的表锁可能会阻塞其他用户”。

虽然锁升级与更改查询运行的隔离级别并不完全相同，但这让我感到惊讶，因为我不希望 sql server 使用比默认隔离级别允许的更多的锁。