具有 Memory-Optimized 表的事务隔离级别指南

2017-03-06

在许多情况下，必须指定事务隔离级别。内存优化表的事务隔离不同于基于磁盘的表。

指定事务隔离级别的要求：

TRANSACTION ISOLATION LEVEL 是 ATOMIC 块中必需的选项，该选项包含本地编译存储过程的内容。
由于容器间事务中的隔离级别使用限制，因此在解释的 Transact-SQL 中使用内存优化表时，通常必须附带表提示，以指定用于访问表的隔离级别。有关隔离级别提示和跨容器事务的详细信息，请参阅事务隔离级别。
必须显式声明所需的事务隔离级别。无法使用锁定提示（如 XLOCK）来保证事务中某些行或表的隔离。
访问数据库的应用程序应实现重试逻辑，以处理由事务冲突、验证失败和提交依赖项失败导致的错误。请注意，即使使用只读事务，也可能发生提交依赖项失败。
应避免使用内存优化表长时间运行的事务。此类事务会增加冲突和后续事务终止的可能性。长时间运行的事务也会延迟垃圾回收。事务运行的时间越长，OLTP 越长 In-Memory 会保留最近删除的行版本，这可能会降低新事务的查找性能。

基于磁盘的表通常依赖于锁定和阻塞来实现事务隔离性。内存优化表依赖于多版本控制和冲突检测来保证隔离。有关详细信息，请参阅 Memory-Optimized 表中事务中的冲突检测、验证和提交依赖项检查部分。

基于磁盘的表允许使用隔离级别 SNAPSHOT 和 READ_COMMITTED_SNAPSHOT 进行多版本控制。对于内存优化表，所有的隔离级别都采用了多版本机制，其中包括可重复读隔离级别（REPEATABLE READ）和可序列化隔离级别（SERIALIZABLE）。

事务类型

SQL Server 中的每个查询都在事务的上下文中运行。

SQL Server 中有三种类型的事务：

自动提交事务。如果没有活动事务上下文，并且未在会话中将隐式事务设置为 ON，则每个查询都有自己的事务上下文。该事务在语句开始执行时启动，并在语句完成时完成。
显式事务。用户通过显式 BEGIN TRAN 或 BEGIN ATOMIC 启动事务。相应的 COMMIT 和 ROLLBACK 或 END 完成后，事务才会完成（在原子块的情况下）。
隐式事务。当选项IMPLICIT_TRANSACTIONS设置为 ON 时，每当用户执行语句且没有活动事务上下文时，将隐式启动事务。事务通过显式 COMMIT 和 ROLLBACK 完成。

基线“READ COMMITTED”隔离

READ COMMITTED 是 SQL Server 中的默认隔离级别。

隔离级别 READ COMMITTED 保证事务在当前事务之外看不到任何未提交的数据。换句话说，事务只读取已提交到数据库的数据，或被当前事务更改的数据。

内存优化表支持的所有隔离级别都提供读取提交的保证。因此，如果事务不需要更强大的保证，则可以使用内存优化表支持的任何隔离级别。与其他隔离级别相比，SNAPSHOT 使用最少的系统资源。

SNAPSHOT 隔离级别是内存优化表支持的最低隔离级别，其提供的保证包括 READ COMMITTED 的保证。事务中的每个语句都读取数据库中版本一致的数据。不仅由事务提交到数据库的所有行被读取，而且所有读取操作都可以看到同一组事务所做的更改集。

指南：如果只需要 READ COMMITTED 隔离保证，请将 SNAPSHOT 隔离与本机编译的存储过程一起使用，并通过解释的 Transact-SQL 访问内存优化表。

对于自动提交事务，隔离级别 READ COMMITTED 在内存优化表中会隐式地转换为 SNAPSHOT。因此，如果将 TRANSACTION ISOLATION LEVEL 会话设置设置为 READ COMMITTED，则无需在访问内存优化表时通过表提示指定隔离级别。

以下自动提交事务示例显示了内存优化表 Customers 和常规表 [订单历史记录]之间的联接，作为临时批处理的一部分：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;

以下显式或隐式事务示例显示了相同的联接，但这次是在显式用户事务中。内存优化的表 Customers 在快照隔离下被访问，如表提示 WITH (SNAPSHOT) 所示，而常规表 [Order History] 在读取提交的隔离下被访问。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT

操作差异

除了读取已提交的保证外，使用基于磁盘表的应用程序还可能依赖于两个关键实现细节。将使用 READ COMMITTED 隔离访问的基于磁盘的表转换为使用 SNAPSHOT 隔离访问的内存优化表时，请注意以下事项：

假设 READ_COMMITTED_SNAPSHOT 为 OFF，基于磁盘存储的表的 READ COMMITTED 隔离级别实现使用锁来防止读取与写入之间的冲突。当写入器开始更新行时，它会获取锁，在提交事务之前不会释放锁。任何读取操作会被阻止，且需要等待写入事务提交。

某些应用程序可能假设读者始终等待编写器提交，尤其是在应用程序层中的两个事务之间有任何同步时。

指引： 应用程序不能依赖于阻止行为。如果应用程序需要并发事务之间的同步，可以通过 sp_getapplock（Transact-SQL）在应用程序层或数据库层中实现此类逻辑。
在使用 READ COMMITTED 隔离的事务中，每个语句都会看到数据库中的行的最新版本。因此，后续语句会观察到数据库状态的变化。

使用 WHILE 循环轮询表，直到找到新行的过程，是基于此假设的应用模式的一个示例。每次循环迭代后，查询将看到数据库中的最新更新。

指引： 如果应用程序需要轮询内存优化的表以获取写入表的最新行，请将轮询循环移到事务的作用范围之外。

下面是使用此假设的示例应用程序模式。使用 WHILE 循环轮询表，直到找到新行。在每个循环迭代中，查询将访问数据库中的最新更新。

以下示例脚本会持续轮询表 t1，直到其中有一行。然后，它会从表中删除一行，以便进一步处理。

请注意，轮询逻辑需要超出事务的范围，因为它使用快照隔离来访问表 t1。在事务过程中使用轮询逻辑会创建长时间运行的事务，这是一种不良做法。

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT

锁定表提示

可以使用锁定提示（例如表提示（Transact-SQL）中的 HOLDLOCK 和 XLOCK）来基于磁盘的表，以便 SQL Server 获得比指定隔离级别所需更多的锁。

内存优化表不使用锁。更高的隔离级别（例如 REPEATABLE READ 和 SERIALIZABLE）可用于声明所需的保证。

不支持锁定提示。而是通过事务隔离级别声明出所需的保证。支持 NOLOCK 是因为 SQL Server 不会对内存优化表加锁。请注意，与基于磁盘的表不同，对于内存优化表，NOLOCK 并不意味着表现为 READ UNCOMMITTED。

另请参阅

了解 Memory-Optimized 表上的事务
 Memory-Optimized 表上的事务重试逻辑指南
 事务隔离级别

通过