使用内存优化表的事务

适用于：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生效时启动。 此选项隐式执行 0，相当于在每个UPDATE语句之前显式地执行BEGIN TRANSACTION，如果@@TRANCOUNT是0的话。 因此，最终需要由您的 T-SQL 代码明确调用 COMMIT TRANSACTION。

• ATOMIC 块。 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）
• 数据库引擎中基于行版本控制的隔离级别
• 控制事务持续性