默认的SQL Server隔离级别更改

Question

我们有一位客户在使用我们的数据库应用程序时遇到了阻塞问题。我们要求他们运行一个Blocked Process Report跟踪，而他们提供的跟踪结果显示在SELECT和UPDATE操作之间发生了阻塞。跟踪文件显示以下信息：

- 同一个SELECT查询在不同的隔离级别下执行。一个跟踪显示Serializable IsolationLevel，而后来的一个跟踪显示RepeatableRead IsolationLevel。我们没有在执行查询时使用显式事务。 - UPDATE查询使用RepeatableRead隔离级别执行，但被SELECT查询阻塞。这是预期的，因为我们的更新被包装在隔离级别为RepeatableRead的显式事务中。

因此，我们完全不知道为什么选择查询的隔离级别不是默认的ReadCommitted隔离级别，更加令人困惑的是，查询的隔离级别会随时间改变？只有一个客户看到了这种行为，所以我们怀疑这可能是数据库配置问题。

有什么想法吗？

谢谢， Graham

- Graham

Answer 1

在您的情况下，我建议明确设置隔离级别为快照-这将通过防止锁定来防止读取妨碍写入（插入和更新），但这些读取仍然是“好的”读取（即不是脏数据-它与NOLOCK不同）。

通常，我发现如果我的查询存在锁定问题，我会手动控制所应用的锁。例如，我会使用行级锁进行更新，以避免页面/表级锁定，并将我的读取设置为readpast（接受我可能会错过一些数据，在某些情况下可能没关系）

EDIT-- 将所有评论合并为答案

作为优化过程的一部分，SQL Server避免在已知未更改的页面上获取提交读取，并自动回退到较低的锁定策略。在您的情况下，SQL Server从可串行化读取降级为可重复读取。

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

A: 默认情况下，如果您的默认隔离级别是Read Committed，则SQL Server将使用Read Commmited。但是，如果您在查询中没有另外指定锁定策略，那么您基本上是在告诉SQL Server“做出你认为最好的选择，但我的首选是Read Commited”。由于SQL Server可以自由选择，因此它会优化查询。（SQL Server中的优化算法非常复杂，我自己也不完全理解）。据我所知，不在事务内显式执行不会影响SQL Server使用的隔离级别。

Q: 最后一个问题，SQL Server增加隔离级别（并可能增加所需的锁数）以优化查询是否合理？如果连接继承了上次使用的隔离级别，是否会受到影响？

A: Sql server将在称为“锁定升级”的过程中执行此操作。从http://support.microsoft.com/kb/323630引用：“Microsoft SQL Server动态确定何时执行锁定升级。在做出此决定时，SQL Server考虑了在特定扫描上持有的锁的数量，整个事务持有的锁的数量以及系统中用于锁的内存。通常，SQL Server的默认行为只会在能够提高性能或必须将过多的系统锁内存减少到更合理的水平时发生锁定升级。但是，某些应用程序或查询设计可能会在不希望的时间触发锁定升级，并且升级的表锁可能会阻止其他用户。”

尽管锁定升级与更改查询运行的隔离级别并不完全相同，但这让我感到惊讶，因为我没有预料到Sql server会使用比默认隔离级别允许的锁定更多的锁定。

- Ash M

---

没问题，Ash。感谢你提供有关降低隔离级别的有用信息。你能想到为什么在第一次使用Serializable隔离级别时会这样做吗？因为我们没有为SELECT使用显式事务——我们的理解是隐式事务将使用ReadCommitted。 - Graham

谢谢Ash。如果你把你的评论合并成一个答案，我会将其标记为答案并给你投票。 - Graham

最后一个问题，SQL Server 增加隔离级别（并且可能增加所需的锁数量）以优化查询是否合理？我还想知道，如果重用池化连接并继承了上次使用的隔离级别，这是否会受到影响？ - Graham

Answer 2

关于为什么SQL会通过升级而需要更多锁的更多信息：这是不正确的，升级会减少（而不是增加）所需的锁数。表锁是单个锁，而不是从较低级别执行相同操作所需的所有页面或行锁。锁升级总是出于一个原因：取高级别锁比锁定所有低级对象更有效率。

例如，可能没有索引可用于有效地锁定。也就是说，如果你在具有2010年字段的所有记录上使用UPDLOCK计算，并且该日期字段上没有索引，则如果许多记录被命中，则需要对每个2010年记录进行一次行锁定，这是不有效率的。页面锁也无济于事，因为它们可能随机分布在各个页面上，因此SQL会获取表锁。此外，SQL还必须锁定其他记录以防止其变为2010年，同时保持UPDLOCK，并且由于没有索引来执行范围锁定，SQL别无选择，只能获取表锁以防止发生这种情况。这后一点经常被那些新手忽略了：认识到SQL还必须“保护”已在事务中执行的查询的完整性。

- otto-null