SQL Server AlwaysOn中的脏读 [英] Dirty Reads in SQL Server AlwaysOn

问题描述

我有一对SQL Server 2014数据库设置为同步AlwaysOn可用性组。

I have a pair of SQL Server 2014 databases set up as a synchronous AlwaysOn availability group.

两个服务器都设置为 Synchronous commit 可用性模式，会话超时为50秒。次要对象被设置为只读的目的可读次要对象。

Both servers are set to the Synchronous commit availability mode, with a session timeout of 50 seconds. The secondary is set to be a Read-intent only readable secondary.

如果我写了主要对象，然后立即从辅助读取（通过 ApplicationIntent = ReadOnly ），我始终读取脏数据（即写入之前的状态）。如果我在写作和阅读之间等待大约一秒钟，就会得到正确的数据。

If I write to the primary and then immediately read from the secondary (via ApplicationIntent=ReadOnly), I consistently read dirty data (i.e. the state before the write). If I wait for around a second between writing and reading, I get the correct data.

这是预期的行为吗？如果是这样，我可以做些什么来确保从次级读取的内容是最新的？

Is this expected behaviour? If so, is there something I can do to ensure that reads from the secondary are up-to-date?

我想将次级用作读取的内容，

I'd like to use the secondary as a read-only version of the primary (as well as a fail-over), to reduce the load on the primary.

推荐答案

有主要版本的唯一版本（以及故障转移），以减少主要版本上的负载。除非您使用无锁提示，否则您将无法获得脏读。.

There is no way you can get dirty reads unless you are using no-lock hint..

在AlwaysOn中启用只读次要对象时。内部SQL使用行版本化来存储以前的版本

When you enable Read Only secondaries in AlwaysOn..Internally SQL uses rowversioning to store Previous version of the row..

进一步使用同步提交模式，这可以确保日志记录首先在辅助数据库上提交，然后在主要数据库上提交。

further you are using Synchronous commit mode,this ensures log records are committed first on secondary,then on primary..

您看到的是数据延迟。

此 whitePaper 处理这种情况。.下面是相关的部分，有助于您进一步了解它。.

This whitePaper deals with this scenario..Below is relevant part which helps in understanding more about it..

在辅助副本上运行的报告工作负载a会导致一些数据延迟，通常要几秒钟到几分钟，这取决于主要工作负载和网络延迟。

The reporting workload running on the secondary replica will incur some data latency, typically a few seconds to minutes depending upon the primary workload and the network latency.

即使将辅助副本配置为同步模式，数据延迟仍然存在。确实，同步副本通过在将ACK发送到主数据库之前对已提交事务的事务日志记录进行加固来帮助确保在理想条件下（即RPO = 0）没有数据丢失，但这并不能保证REDO线程在辅助副本上，确实已将关联的日志记录应用于数据库页面。

The data latency exists even if you have configured the secondary replica to synchronous mode. While it is true that a synchronous replica helps guarantee no data loss in ideal conditions (that is, RPO = 0) by hardening the transaction log records of a committed transaction before sending an ACK to the primary, it does not guarantee that the REDO thread on secondary replica has indeed applied the associated log records to database pages.

因此存在一些数据延迟。您可能想知道在异步模式下配置辅助副本时，这种数据延迟是否更可能发生。这是一个较难回答的问题。如果主副本和辅助副本之间的网络无法跟上事务日志流量（即，如果没有足够的带宽），则异步副本可能会进一步落后，从而导致更高的数据延迟。

So there is some data latency. You may wonder if this data latency is more likely when you have configured the secondary replica in asynchronous mode. This is a more difficult question to answer. If the network between the primary replica and the secondary replica is not able to keep up with the transaction log traffic (that is, if there is not enough bandwidth), the asynchronous replica can fall further behind, leading to higher data latency.

对于同步副本，不足的网络带宽不会导致辅助节点上的数据延迟增加，但会降低事务响应时间和主要工作负载的吞吐量

In the case of synchronous replica, the insufficient network bandwidth does not cause higher data latency on the secondary but it can slow down the transaction response time and throughput for the primary workload

这篇关于SQL Server AlwaysOn中的脏读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！