默认 SQL Server 隔离级别更改
我们有一位客户在我们的数据库应用程序中遇到了一些阻塞问题。我们要求他们运行阻塞进程报告跟踪，他们给我们的跟踪显示了 SELECT 和 UPDATE 操作之间发生的阻塞。跟踪文件显示以下内容：
- 同一 SELECT 查询正在不同的隔离级别上执行。一条跟踪显示 Serialized IsolationLevel，而后面的跟踪显示 RepeatableRead IsolationLevel。我们在执行查询时不使用显式事务。
- UPDATE 查询正在以 RepeatableRead 隔离级别执行，但被 SELECT 查询阻止。这是预期的，因为我们的更新被包装在具有 RepeatableRead IsolationLevel 的显式事务中。
所以基本上我们不知道为什么 SELECT 查询的隔离级别不是默认的 ReadCommited IsolationLevel，但更令人困惑的是，为什么查询的隔离级别会随着时间的推移而改变？只有一个客户看到此行为，因此我们怀疑这可能是数据库配置问题。
有什么想法吗？
提前致谢，
格雷厄姆
we have a customer that's been experiencing some blocking issues with our database application. We asked them to run a Blocked Process Report trace and the trace they gave us shows blocking occurring between a SELECT and UPDATE operation. The trace files show the following:
- The same SELECT query is being executed at different isolation levels. One trace shows a Serializable IsolationLevel while a later trace shows a RepeatableRead IsolationLevel. We do not use an explicit transaction while executing the query.
- The UPDATE query is being executed with a RepeatableRead isolation level but is being blocked by the SELECT query. This is expected as our updates are wrapped in an explicit transaction with IsolationLevel of RepeatableRead.
So basically we're at a loss as to why the Isolation Level of the SELECT query would not be the default ReadCommitted IsolationLevel but, even more confusingly, why the IsolationLevel of the query would change over time? It is only one customer that is seeing this behaviour so we suspect it may be a database configuration issue.
Any ideas?
Thanks in advance,
Graham
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在您的场景中，我建议显式地将隔离级别设置为快照 - 这将通过防止锁定来防止读取妨碍写入（插入和更新），但这些读取仍然是“良好”读取（即不是脏数据 -它与 NOLOCK 不同）
一般来说，我发现当我的查询出现锁定问题时，我会手动控制所应用的锁。例如，我会使用行级锁进行更新以避免页/表级锁定，并将读取设置为 readpast （接受我可能会丢失一些数据，在某些情况下可能没问题）
link|edit|delete|flag
编辑--将所有注释合并到答案中
作为优化过程的一部分，sql server 避免在它知道尚未更改的页面上进行提交读取，并自动回退到较小的锁定策略。在您的情况下，sql server 从可序列化读取变为可重复读取。
问：感谢您提供有关降低隔离级别的有用信息。鉴于我们没有对 SELECT 使用显式事务（我们的理解是隐式事务将使用 ReadCommited），您能想到它首先使用 Serialized IsolationLevel 的任何原因吗？
答：默认情况下，如果这是您的默认隔离级别，SQL Server 将使用已提交读，但是如果您没有在查询中另外指定锁定策略，那么您基本上是在对 sql server 说“做您认为最好的事情，但我的偏好已提交读”。由于SQL Server可以自由选择，所以它这样做是为了优化查询。 （sql server中的优化算法非常复杂，我自己也没有完全理解）。据我所知，不在事务中显式执行不会影响 sql server 使用的隔离级别。
问：最后一件事，SQL Server 增加隔离级别（大概还有所需的锁数量）来优化查询是否合理？我还想知道如果池连接继承了上次使用的隔离级别，重用是否会影响这一点？
答：Sql 服务器将作为“锁升级”过程的一部分来执行此操作。从http://support.microsoft.com/kb/323630，我引用：“Microsoft SQL服务器动态确定何时执行锁升级 在做出此决定时，SQL Server 会考虑特定扫描中持有的锁数量、整个事务持有的锁数量以及正在使用的内存。对于整个系统中的锁，SQL Server 的默认行为只会在需要提高性能或必须将过多的系统锁内存减少到更合理的水平时才会发生锁升级。设计可能会在不需要的时候触发锁升级，并且升级的表锁可能会阻塞其他用户”。
尽管锁升级与更改查询运行的隔离级别并不完全相同，但这让我感到惊讶，因为我不希望 sql server 获取比默认隔离级别允许的更多的锁。
In your scenario, I would recommend explicitly setting isolation level to snapshot - that will prevent read from getting in the way of writes (inserts and updates) by preventing locks, yet those read would still be "good" reads (i.e. not dirty data - it is not the same as a NOLOCK)
Generally i find that where i have locking issues with my queries, i manually control the lock applied. e.g. i would do updates with row-level locks to avoid page/table level locking, and set my reads to readpast (accepting that i may miss some data, in some scenarios that might be ok)
link|edit|delete|flag
EDIT-- Combining all the comments into the answer
As part of the optimisation process, sql server avoids getting commited reads on a page that it know hasn't changed, and automatically falls back to a lesser locking strategy. In your case, sql server drops from a serializable read to a repeatable read.
Q: Thanks for that useful info regarding dropping Isolation Levels. Can you think of any reason that it would use Serializable IsolationLevel in the first place, given that we don't use an explicit transaction for the SELECT - it was our understanding that the implicit transaction would use ReadCommitted?
A: By default, SQL Server will use Read Commmited if that is your default isolation level BUT if you do not additionally specify a locking strategy in your query, you are basically saying to sql server "do what you think is best, but my preference is Read Commited". Since SQL Server is free to choose, so it does in order to optimise the query. (The optimisation algorithm in sql server is very complex and i do not fully understand it myself). Not explicitly executing within a transaction does not, afaik, affect the isolation level that sql server uses.
Q: One last thing, does it seem reasonable that SQL Server would increase the Isolation Level (and presumably the number of locks required) to optimise the query? I'm also wondering whether the reuse of a pooled connection would affect this if it inherited the last used Isolation Level?
A: Sql server will do that as part of a process called "Lock Escalation". From http://support.microsoft.com/kb/323630, i quote: "Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole. Typically, SQL Server's default behavior results in lock escalation occurring only at those points where it would improve performance or when you must reduce excessive system lock memory to a more reasonable level. However, some application or query designs may trigger lock escalation at a time when it is not desirable, and the escalated table lock may block other users".
Although lock escalation is not exactly the same thing as changing the isolation level a query runs under, this surprises me because i would not have expected sql server to take more locks than what the default isolation level permits.
有关为什么 SQL 将通过升级获取更多锁的更多信息：这是不正确的，升级会减少（而不是增加）所需的锁数量。表锁是单个锁，而不是从较低级别执行相同操作所需的所有页锁或行锁。锁升级总是出于一个原因进行：获取较高级别的锁比锁定所有较低级别的对象更有效。
例如，可能没有可用于有效锁定的索引。即，如果您对某个字段中年份为 2010 年的所有记录使用 UPDLOCK 进行计数，并且该日期字段上没有索引，则需要对 2010 年的每条记录进行行锁定，如果有许多记录，则效率不高。命中，页锁也无济于事，因为它们可能是随机分布在页上的，因此 SQL 需要一个表锁。此外，在持有 UPDLOCK 的同时，SQL 还必须锁定其他记录，防止其更改为 2010 年，并且在该字段上没有索引来执行范围锁，SQL 没有选择，只能采取表锁来防止这种情况发生。后一点是优化新手经常忽略的一点：SQL 还必须“保护”事务中已经执行的查询的完整性。
More info regarding why SQL would take more locks by escalating: this is incorrect, escalating reduces (not increases) the number of locks required. A table lock is a single lock vs. all the page or row locks required to do the same from a lower level. Lock escalation is always done for one reason: it's more efficient to take a higher level lock than to lock all the lower-level objects
For example, perhaps there is no index available to lock efficiently against. I.e. if you take a count with UPDLOCK on all records with a year of 2010 in a field, and there is no index on that date field, this will require a row lock on each record in 2010, which is not efficient if many records are hit, and a page lock will not help either since they are presumably distributed randomly across pages, therefore SQL takes a table lock. Moreover, SQL MUST also lock other records from changing to being in the year 2010 while the UPDLOCK is held, and with no index on this field to do a range lock, SQL has NO CHOICE but to take a table lock to prevent this from happening. This latter point is one often missed by those new to optimization: the realization that SQL must also "protect" the integrity of the queries already executed in the transaction.