sql-server - SQL Server 内存中 oltp 事务快照隔离

Question

试图了解事务隔离级别如何在 SQL Server 内存优化表(内存中 oltp)上工作。

如果我执行以下查询:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

SELECT *
FROM tm.Tasks

显示错误消息:

Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as 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

它不再工作，显示错误消息:

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

为什么它与表提示一起工作，并通过设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

才不是？

更新 : 尝试设置 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT至 ON ，仍然从上次查询中得到相同的错误:

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

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/

如果您对这个经常被误解的话题还有其他疑问，请告诉我。