SQL Server Always On可用性组中的隔离级别行为

In my previous article, Data synchronization in SQL Server Always On Availability Groups, we explored the internal data synchronization for both Asynchronous and Synchronous data commit mode. In the following image, on the secondary replica, we can see two important processes.

在上一篇文章“ SQL Server Always On可用性组中的数据同步”中 ，我们探讨了异步和同步数据提交模式的内部数据同步。 在下图中，在辅助副本上，我们可以看到两个重要过程。

• Log Receive: It receives the log records sent by the primary replica and writes to Log cache on each secondary replica
  日志接收：它接收主副本发送的日志记录，并写入每个辅助副本上的日志缓存
• Redo Thread: Redo thread on secondary replica writes all log records changes to the data page and index page
  重做线程：辅助副本上的重做线程将所有日志记录更改写入数据页和索引页

SQL Server Always Always中的可读辅助副本对可用性组的影响 (Impact of Readable secondary replica in SQL Server Always On Availability Groups)

Suppose, you have a readable secondary replica to distribute read workload to the secondary replica. The overall objective is to keep the minimum load on the primary replica.

假设您有一个可读的辅助副本，可以将读取的工作负载分配给该辅助副本。 总体目标是保持主副本上的负载最小。

We might have a few questions at this point.

在这一点上，我们可能有几个问题。

• Does read workload on secondary replica make any performance impact on a primary replica?
  辅助副本上的读取工作负载是否会对主副本产生任何性能影响？
• Does primary replica get impacted due to locking on a secondary replica?
  主副本是否由于锁定在辅助副本上而受到影响？
• What is the impact of isolation levels on the secondary read workload?
  隔离级别对辅助读取工作负载有什么影响？

Let’s evaluate the impact of a readable secondary replica over primary replica with the following points

让我们通过以下几点来评估可读的辅助副本对主副本的影响

• In the Asynchronous data commit mode, the primary replica does not wait for an acknowledgement from the secondary replica. It does not make any impact on the transaction time
  在异步数据提交模式下，主副本不等待来自辅助副本的确认。 不会对交易时间造成任何影响
• In Synchronous data commit mode, primary replica waits for an acknowledgement from the primary replica. Once the log record is hardened on the secondary, it sends an acknowledgement to the primary replica
  在同步数据提交模式下，主副本等待来自主副本的确认。 一旦日志记录在辅助副本上得到了加强，它就会向主副本发送一个确认

Readable secondary replica in SQL Server Always On Availability Groups does not impact the acknowledgement receipt from secondary to the primary replica. SQL Server always gives priority to internal threads. In this case, Redo thread always gets a priority on secondary replica than the user threads (due to read workload). If there is a high IO intensive workload, it might cause IO bottleneck. We should always follow the best practice of segregating data and log file into different data store or physical disks.

SQL Server Always On可用性组中的可读辅助副本不会影响从辅助副本到主副本的确认接收。 SQL Server始终优先考虑内部线程。 在这种情况下，重做线程始终在辅助副本上比用户线程具有优先级（由于读取工作负载）。 如果存在大量的IO密集型工作负载，则可能会导致IO瓶颈。 我们应始终遵循将数据和日志文件分离到不同的数据存储或物理磁盘的最佳实践。

We might see an issue in which REDO threads get slow due to the read workload. We might see blocking for REDO thread. In these cases, you might impact the Recovery Point Objective and Recovery Time Objective for database services.

我们可能会看到一个问题，其中REDO线程由于读取工作负载而变慢。 我们可能会看到REDO线程被阻塞。 在这些情况下，您可能会影响数据库服务的恢复点目标和恢复时间目标。

快照隔离级别概述 (Overview of Snapshot Isolation level)

SQL Server default isolation level is READ COMMITTED in SQL Server. An isolation level changes the behavior of locking in SQL Server. In the Snapshot Isolation Level, SQL Server maintains a row version for each transaction in TempDB. SQL Server maintains a sequence number of the transaction. It does not acquire locks on the data pages for a snapshot transaction. It allows other transactions to execute without any blocking scenario.

SQL Server的默认隔离级别在SQL Server中为READ COMMITTED。 隔离级别更改了SQL Server中的锁定行为。 在快照隔离级别中，SQL Server为TempDB中的每个事务维护行版本。 SQL Server维护事务的序列号。 它不会为快照事务获取数据页上的锁。 它允许其他事务在没有任何阻塞情况下执行。

In the following screenshot, we can see that one user-initiated a transaction to update a record while other user wants to read that data. In the Read Committed Snapshot Isolation level, SQL Server provides a copy of the page to read the data.

在下面的屏幕截图中，我们可以看到一个用户发起了一个事务来更新记录，而另一个用户想要读取该数据。 在“读取提交的快照隔离”级别中，SQL Server提供页面的副本以读取数据。

In the Read Commit Snapshot Isolation level, SQL Server requires an additional 14 bytes for maintaining row versions in TempDB.

在读取提交快照隔离级别中，SQL Server需要额外的14个字节来维护TempDB中的行版本。

Let’s understand using a simple example.

让我们用一个简单的例子来理解。

Execute the following query to create a table and insert few records in it.

执行以下查询以创建表并在其中插入一些记录。

CREATE TABLE Test
(
	ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Code CHAR(1000),
	)
GO
 
INSERT INTO Test VALUES (REPLICATE('A', 1000))
GO 100

Once data insertion is complete, we can check the page details using DBCC IND and DBCC PAGE commands.

数据插入完成后，我们可以使用DBCC IND和DBCC PAGE命令检查页面详细信息。

Specify DBCC IND parameters in the following format.

以以下格式指定DBCC IND参数。

DBCC IND(‘DB’,’Object’,-1)

Execute this command, and it gives you page numbers for the particular objects.

执行此命令，它会为您提供特定对象的页码。

DBCC IND('Test',Test,-1)
GO

We can check page details for any particular page using the following DBCC Page command.

我们可以使用以下DBCC页面命令来检查任何特定页面的页面详细信息。

DBCC PAGE(‘DB’,PageNo, PageType, OptionType)

For my example, Execute the following query:

对于我的示例，执行以下查询：

DBCC TRACEON(3604)
DBCC page ('Test',1,348,3)

Here Option 3 shows both page header information and data. You also need to enable trace flag 3604 to show the output of the DBCC command. We can see record size 1011 for this page.

在这里，选项3同时显示了页眉信息和数据。 您还需要启用跟踪标志3604以显示DBCC命令的输出。 我们可以看到此页面的记录大小1011。

Let’s change the Isolation level to Read Committed Snapshot using the ALTER DATABASE command and update the records in the table.

让我们使用ALTER DATABASE命令将“隔离级别”更改为“读取已提交快照”，并更新表中的记录。

ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON
GO
Update Test Set Code='B'

Rerun the DBCC Page command and view the difference in record size.

重新运行DBCC Page命令并查看记录大小的差异。

The difference in record length= Record length in Read Committed Snapshot Isolation level – Record length in Read Committed Isolation level.

记录长度的差=读取已提交快照隔离级别中的记录长度–读取已提交隔离级别中的记录长度。

The difference in record length=1025-1011=14 bytes.

记录长度的差异= 1025-1011 = 14个字节。

In the above calculations, we can see it adds 14 bytes for the record on the data page in Read Commit Snapshot isolation level.

在以上计算中，我们可以看到它为读取提交快照隔离级别的数据页上的记录添加了14个字节。

SQL Server Always On可用性组中的隔离级别行为
(Isolation level behavior in SQL Server Always On Availability Groups
)

Let’s understand the behavior of locking in SQL Server first. To simulate the issue, open two sessions in SSMS.

首先让我们了解锁定SQL Server的行为。 要模拟该问题，请在SSMS中打开两个会话。

Session 1:

第一场：

SET TRANSACTION ISOLATION LEVEL Repeatable Read
Begin tran
select * from Test where ID=10

Session 2:

第二场：

Begin tran
Update Test set Code='C' where ID=10

Session 1 places a shared lock on the qualifying row data and the second transaction wants to update the same record. The Update statement gets blocked due to this scenario.

会话1在合格行数据上设置了共享锁，第二个事务要更新同一记录。 由于这种情况，Update语句被阻止。

In the following image, you can understand that transaction 2 is blocked due to transaction 1 and cannot complete the transaction until transaction 1 is completed.

在下图中，您可以理解事务2由于事务1而被阻塞，并且在事务1完成之前无法完成事务。

This situation might occur in SQL Server Always On as well. Suppose this time secondary database runs a query on session 1 (changes isolation level to repeatable read). In the session on the primary replica database, another session wants to update the record.

SQL Server Always On中也可能会发生这种情况。 假设这次辅助数据库在会话1上运行查询（将隔离级别更改为可重复读取）。 在主副本数据库上的会话中，另一个会话要更新记录。

How will the transaction behave in this situation?

在这种情况下交易将如何表现？

All transaction isolation levels on the secondary replicas in SQL Server Always On Availability Groups are mapped to Snapshot Isolation to avoid any blocking. As you know, snapshot isolation uses row versioning.

SQL Server Always On可用性组中辅助副本上的所有事务隔离级别都映射到快照隔离，以避免任何阻塞。 如您所知，快照隔离使用行版本控制。

Isolation level on Secondary Replica	Isolation level mapping
Read Uncommitted (RU)	Snapshot Isolation ( SI)
Read Committed (RC)
Repeatable Read (RR)
Snapshot Isolation (SI)
Serializable (SR)

二级副本上的隔离级别	隔离级别映射
读未提交（RU）	快照隔离（SI）
已提交读（RC）
可重复读（RR）
快照隔离（SI）
可序列化（SR）

SQL Server databases in Secondary replica also ignore all locking hits. However, you should test the workload to avoid any issues in the production environment.

辅助副本中SQL Server数据库也将忽略所有锁定命中。 但是，您应该测试工作负载以避免生产环境中的任何问题。

As you know that snapshot isolation level uses row versioning. If any row is modified, SQL Server stores its version in the TempDB, and it adds a 14 bytes pointer to track the row version. Let’s explore the following scenarios for the impact of the snapshot isolation level on the Secondary replica database.

如您所知，快照隔离级别使用行版本控制。 如果修改了任何行，SQL Server会将其版本存储在TempDB中，并添加一个14字节的指针来跟踪行版本。 让我们探索以下方案，了解快照隔离级别对辅助副本数据库的影响。

方案1：未为SQL Server Always On可用性组中的读取工作负载启用辅助副本 (Scenario 1: Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups)

In this case, we cannot connect to the secondary replica database for executing read workloads. In this case, there is no additional overhead on the primary replica.

在这种情况下，我们无法连接到辅助副本数据库来执行读取工作负载。 在这种情况下，主副本上没有额外的开销。

In the following image, you can see no impact on the primary and secondary replica. We cannot use the secondary replica read workload in this case.

在下图中，您不会看到对主副本和辅助副本的影响。 在这种情况下，我们无法使用辅助副本读取工作负载。

方案2：为SQL Server Always On可用性组中的读取工作负载启用辅助副本 (Scenario 2: Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups)

In this case, users can execute the read workload on the secondary database. SQL Server adds a 14 byte overhead for each row on the primary as well as on Secondary replica. Once the secondary replica, gets transaction log records for DML operation (Update, Delete), REDO thread generates a row version in the TempDB.

在这种情况下，用户可以在辅助数据库上执行读取的工作负载。 SQL Server在主副本和辅助副本上的每一行增加了14个字节的开销。 一旦辅助副本获取了DML操作的事务日志记录（更新，删除），REDO线程就会在TempDB中生成行版本。

In this case, the following process is followed:

在这种情况下，请执行以下过程：

• Secondary replica databases use the snapshot isolation level; therefore, it generates a row version in the tempdb of the secondary database. The primary replica does not maintain the row version in this case. It does not make any impact on the tempdb on the primary replica
  辅助副本数据库使用快照隔离级别。 因此，它将在辅助数据库的tempdb中生成行版本。 在这种情况下，主副本不维护行版本。 它不会对主副本上的tempdb产生任何影响
• The secondary replica also adds 14 byte overhead for each updated row. As you know, both the primary and secondary replica must be identical therefore, The Primary replica database also adds 14 byte overhead for the new or modified rows
  辅助副本还为每个更新的行增加了14个字节的开销。 如您所知，主副本和辅助副本必须相同，因此，主副本数据库还为新行或修改后的行增加了14个字节的开销

In the following image, you can see secondary replica adds 14 bytes overhead along with versioning in the TempDB. The primary replica also gets its overhead of 14 bytes.

在下图中，您可以看到辅助副本在TempDB中增加了14个字节的开销以及版本控制。 主副本的开销也为14个字节。

方案3：具有快照隔离的主副本，但未为SQL Server Always On可用性组中的读取工作负载启用辅助副本 (Scenario 3: Primary replica with Snapshot isolation but Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups)

In this case, we have a primary replica database with a snapshot isolation level. Primary replica maintains a row version along with the 14 bytes overhead. The secondary replica also gets this 14 byte overhead, but it does not maintain the row versions in the TempDB.

在这种情况下，我们有一个具有快照隔离级别的主副本数据库。 主副本维护行版本以及14个字节的开销。 辅助副本也获得了这14个字节的开销，但是它不维护TempDB中的行版本。

方案4：在SQL Server Always On可用性组中为快照工作负载启用了快照隔离的主副本和辅助副本 (Scenario 4: Primary replica with Snapshot isolation and Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups)

• Primary replica maintains the row versioning in the TempDB along with the 14 bytes overhead
  主副本在TempDB中维护行版本控制以及14个字节的开销
• The secondary database also get the 14 bytes overhead
  辅助数据库也获得了14个字节的开销
• The secondary database is also available for read access therefore, it also maintains the row versions
  辅助数据库也可用于读取访问，因此，它还维护行版本

结论 (Conclusion)

This article explores the impact of readable secondary replica in SQL Server Always On Availability Groups along with Isolation level behavior on a primary and secondary replica.

本文探讨了SQL Server Always On可用性组中可读辅助副本的影响以及主副本和辅助副本上的隔离级别行为。

翻译自: https://www.sqlshack.com/isolation-levels-behavior-in-sql-server-always-on-availability-groups/