使用内存优化表的事务

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

本文详细介绍了内存优化表和本机编译存储过程特有的事务相关方面。

SQL Server 中的事务隔离级别以不同的方式应用到内存优化表与基于磁盘的表，并且基础机制不同。 了解差异可帮助程序员设计高吞吐量系统。 在所有情况下，事务完整性的目标是相同的。

有关特定于内存优化表事务的错误条件，请参阅 冲突检测和重试逻辑 部分。

有关一般信息，请参阅 SET TRANSACTION ISOLATION LEVEL。

悲观与乐观

内存优化表与基于磁盘的表之间的功能差异来自对事务完整性的悲观与乐观方法。 内存优化表采用乐观方法：

• 悲观方法采用锁机制，在潜在冲突发生之前将其阻止。 锁在执行语句时获取，并在事务提交时释放。

• 乐观策略会在冲突发生时立即检测到冲突，并在提交时执行验证检查。

  • 内存优化表不会发生错误 1205（死锁）。

乐观方法的开销较小，通常效率更高，部分原因是大多数应用程序中事务冲突并不常见。 悲观方法和乐观方法之间的主要功能差异在于，如果发生冲突，使用悲观方法时，你需要等待，而使用乐观方法时，其中一个事务将失败并需要客户端重试。 当隔离级别REPEATABLE READ生效时，功能差异会更大，而在SERIALIZABLE级别时差异最大。

事务启动模式

SQL Server 使用以下模式进行事务启动：

• 自动提交。 简单的查询或 DML 语句在开始时隐式打开事务，语句的末尾隐式提交事务。 “自动提交”为默认设置。

  在自动提交模式下，通常无需在 FROM 子句中为内存优化表编写有关事务隔离级别的表提示。

• 显式。 Transact-SQL 包含代码 BEGIN TRANSACTION，以及最终的代码 COMMIT TRANSACTION。 您可以将两个或多个语句纳入同一个事务中。

  在显式模式下，您必须使用数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT，或者在 FROM 子句中为内存优化表编写有关事务隔离级别的表提示。

• 隐式。 当SET IMPLICIT_TRANSACTION ON生效时启动。 如果 @@TRANCOUNT 为 0，则此选项会在每个 UPDATE 语句之前隐式执行等同于显式 BEGIN TRANSACTION 的操作。 因此，最终是否发出显式 COMMIT TRANSACTION 取决于您的 T-SQL 代码。

• ATOMIC 块。 ATOMIC 块中的所有语句始终作为单个事务的一部分运行。 成功时，原子块的全部操作将被提交；发生故障时，所有操作将被回滚。 每个本机编译的存储过程都需要一个 ATOMIC 块。

具有显式模式的代码示例

下面的解释型 Transact-SQL 脚本使用了：

• 显式事务。
• 内存优化表，名为 dbo.Order_mo.
• READ COMMITTED事务隔离级别上下文。

因此，您需要在内存优化表上添加表提示符。 提示必须为 SNAPSHOT 或更高隔离级别。 对于代码示例，提示为 WITH (SNAPSHOT). 如果删除此提示，脚本遇到错误 41368，因此自动重试不适用：

错误 41368

仅在自动提交事务中支持使用 READ COMMITTED 隔离级别访问内存优化表。 不支持在显式或隐式事务中使用。 使用表提示，例如 WITH (SNAPSHOT)，为内存优化表提供支持的隔离级别。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION; -- Explicit transaction.

-- Order_mo is a memory-optimized table.
SELECT *
FROM dbo.Order_mo AS o WITH (SNAPSHOT) -- Table hint.
     INNER JOIN dbo.Customer AS c
         ON c.CustomerId = o.CustomerId;

COMMIT TRANSACTION;

您可以通过使用数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 来避免使用 WITH (SNAPSHOT) 提示。 将此选项设置为 ON时，在较低隔离级别下访问内存优化表时会自动提升为 SNAPSHOT 隔离。

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

行版本控制

内存优化表使用高度复杂的行版本化系统，使乐观方法在最严格的隔离级别SERIALIZABLE下也能高效运行。 有关详细信息，请参阅内存优化表简介。

当 READ_COMMITTED_SNAPSHOT 或 SNAPSHOT 隔离级别生效时，基于磁盘的表间接地具有行版本控制系统。 此系统基于 tempdb，而内存优化的数据结构带有内置的行版本控制，以实现最大效率。

隔离级别

下表按照从低到高的顺序列出了可能的事务隔离级别。 有关可能发生的冲突以及用于处理这些冲突的重试逻辑的详细信息，请参阅 冲突检测和重试逻辑。

事务阶段和生命周期

涉及内存优化表时，事务的生存期将经历下图所示的阶段：

下面是阶段的描述。

第 1 阶段（共 3 阶段：常规处理）

• 此阶段执行查询中的所有查询和 DML 语句。

• 在此阶段，语句所看到的内存优化表版本为事务逻辑开始时间时的版本。

第 2 阶段（共 3 阶段：验证）

• 验证阶段开始时分配结束时间，该时间将事务标记为逻辑上完成。 此完成使事务的所有更改对依赖于此事务的其他事务可见。 在该事务成功提交之前，依赖事务无法提交。 此外，保存此类依赖项的事务无法将结果集返回给客户端，以确保客户端只看到已成功提交到数据库的数据。

• 此阶段包括可重复读验证和可序列化验证。 对于可重复读取校验，它会检查事务是否更新了读取的任何行。 为了进行可序列化的验证，它会检查事务是否将行插入到扫描的任何数据范围内。 按隔离级别和冲突中的表所述，使用快照隔离时，可能会同时发生可重复读验证和可序列化验证，以验证唯一约束和外键约束的一致性。

第 3 阶段（共 3 阶段）：提交处理

• 在提交阶段，进程将对持久化表所做的更改写入日志，并将日志写入磁盘。 然后，进程将控制权返回到客户端。

• 提交处理完成后，进程会通知所有依赖事务，告知它们可以提交。

一如既往，请根据您的数据需求，将事务工作单元保持在尽可能小且简短的范围内。

冲突检测和重试逻辑

有两种与事务相关的错误情况会导致事务失败并回滚。 在大多数情况下，此类失败后您需要重试该事务，这与发生死锁时的情况类似。

• 并发事务之间的冲突。 这些冲突（包括更新冲突和验证失败）可能会因为事务隔离级别冲突或约束冲突而发生。

• 依赖项失败。 这些失败是由于你依赖的事务无法提交，或者依赖项数量增长过大。

以下错误条件可能会导致事务在访问内存优化表时失败。

重试逻辑

如果某个事务因上述任何条件而失败，请重试该事务。

可以在客户端或服务器端实现重试逻辑。 在客户端上实现重试逻辑以提高效率。 此方法还有助于在发生故障之前处理事务返回的结果集。

重试 T-SQL 代码示例

对于不向客户端返回结果集的事务，仅对 T-SQL 使用服务器端重试逻辑。 否则，重试可能会将额外的结果集返回给客户端。

以下的 T-SQL 脚本示例展示了如何编写用于解决与内存优化表相关事务冲突错误的重试逻辑。

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry AS INT = 10;

    WHILE (@retry > 0)
    BEGIN

        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
            SET OrderDate = GETUTCDATE()
            WHERE CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
            SET OrderDate = GETUTCDATE()
            WHERE CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0; -- Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry - = 1;

            IF (@retry > 0
                AND ERROR_NUMBER() IN (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205))
                BEGIN
                    IF XACT_STATE() = -1
                        ROLLBACK TRANSACTION;

                    WAITFOR DELAY '00:00:00.001';
                END
            ELSE
                BEGIN
                    PRINT 'Suffered an error for which Retry is inappropriate.';
                    THROW;
                END
        END CATCH

    END -- While loop
END
GO

-- EXECUTE usp_update_salesorder_dates;

跨容器事务

如果一个事务是跨容器事务，则满足以下条件：

• 从解释型 Transact-SQL 访问内存优化表。
• 当事务已打开时执行本机过程（XACT_STATE() = 1）。

术语“跨容器”来自事务跨两个事务管理容器运行的事实。 一个容器管理基于磁盘的表，另一个容器管理内存优化表。

在单个跨容器事务中，您可以使用不同的隔离级别来访问基于磁盘的表和内存优化表。 通过显式表提示（如 WITH (SERIALIZABLE) 或数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT）来表达这种差异。 此选项将内存优化表的隔离级别隐式提升为快照（如果 TRANSACTION ISOLATION LEVEL 配置为 READ COMMITTED 或 READ UNCOMMITTED）。

在以下 Transact-SQL 代码示例中：

• 基于磁盘的表 Table_D1 通过 READ COMMITTED 隔离级别进行访问。
• 使用Table_MO7隔离级别访问内存优化表SERIALIZABLE。 Table_MO6 没有特定的关联隔离级别，因为插入操作始终是一致的，并且本质上是在可串行化隔离级别下执行的。

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.
SELECT *
FROM Table_D1;

-- Table_MO6 and Table_MO7 are memory-optimized tables.
-- Table_MO7 is accessed using SERIALIZABLE isolation,
-- while Table_MO6 does not have a specific isolation level.

INSERT INTO Table_MO6
SELECT *
FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;

限制

• 内存优化表不支持跨数据库事务。 如果事务访问内存优化表，则事务无法访问任何其他数据库，但以下除外：

  • tempdb 数据库。
  • 从 master 数据库进行只读访问。

• 不支持分布式事务：使用 BEGIN DISTRIBUTED TRANSACTION时，事务无法访问内存优化表。

原生编译的存储过程

• 在原生存储过程中，ATOMIC 代码块必须为整个代码块声明事务隔离级别，例如：

  ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• 不能在本机存储过程的正文中包含显式事务控制语句。 类似 BEGIN TRANSACTION 和 ROLLBACK TRANSACTION 不允许的语句。

• 有关使用 ATOMIC 块的事务控制的详细信息，请参阅 Native Procedures 中的原子块。

相关内容

• SET IMPLICIT_TRANSACTIONS （Transact-SQL）
• sp_getapplock（Transact-SQL）
• 数据库引擎中基于行版本控制的隔离级别
• 控制事务持续性