SQL Server内存中OLTP事务的快照隔离。

Question

我将尝试解释SQL Server内存优化表（内存OLTP）中事务隔离级别的工作原理。

如果执行以下查询：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks

显示错误消息：

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

现在，如果我通过添加表提示来修改查询，它就可以工作：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks WITH(SNAPSHOT)

但是如果我通过 SET TRANSACTION ISOLATION LEVEL SNAPSHOT 更改事务隔离级别并删除表提示：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks

出现错误信息：it does not work again displaying error message:

当会话的 TRANSACTION ISOLATION LEVEL 设置为 SNAPSHOT 时，无法访问或创建内存优化表和本地编译模块。

为什么使用表提示和设置事务隔离级别后它可以正常工作？

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

有问题吗？

更新: 尝试将MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT设置为ON，但是仍然从上一个查询中得到相同的错误：

当会话事务隔离级别设置为SNAPSHOT时，无法访问或创建内存优化表和本地编译模块。

- gio

---

除了不显式使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT外，您还需要使用MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT。换句话说，与此选项一起使用SET TRANSACTION ISOLATION LEVEL READ COMMITTED（如果您希望磁盘上的对象也是如此，则为READ_COMMITTED_SNAPSHOT）。这有点令人困惑，但当您具有同时涉及磁盘和内存对象的事务时，就能更容易理解。 - Jeroen Mostert

据我所知，READ_COMMITTED_SNAPSHOT（悲观写入）与SNAPSHOT（乐观写入）不同。因此，如果我需要涉及磁盘和内存优化表的事务，是否不可能使用SNAPSHOT隔离级别？ - gio

READ_COMMITTED_SNAPSHOT 的意思就是 READ COMMITTED 被视为 SNAPSHOT。在乐观锁和悲观锁方面没有区别；快照隔离总是乐观的，如果数据自事务开始以来已被修改，则写操作将失败。如果您想在生效的快照隔离中使用基于锁的 READ COMMITTED 语义（“悲观”），则可以使用 READCOMMITTEDLOCK 表提示。组合内存和磁盘表的显式事务有点棘手，但它们不应该很常见。 - Jeroen Mostert

Answer 1

如果您真的想了解在引用传统表和内存优化表时支持的内存中 OLTP 的隔离级别，那么您还必须了解事务启动模式。所有这些都在我的文章中详细说明:

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/ http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/

如果您对这个常常被误解的话题有进一步的问题，请让我知道。

- NedOtter

Answer 2

若要在没有提示的情况下使用显式 SNAPSHOT 事务，则必须启用 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 数据库选项。以下是来自文档摘录（重点是我的）：

显式 - 您的 Transact-SQL 包含代码 BEGIN TRANSACTION，以及最终的 COMMIT TRANSACTION。两个或多个语句可以并入同一个事务中。在显式模式下，您必须使用数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 或者在 FROM 子句中为内存优化表编写有关事务隔离级别的表提示。

以下是显示如何启用此数据库选项的示例:

ALTER DATABASE YourDatabase
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

- Dan Guzman