事务隔离级别Transaction Isolation Levels

访问内存优化表的事务支持以下隔离级别。The following isolation levels are supported for transactions that access memory-optimized tables.

  • SNAPSHOTSNAPSHOT

  • REPEATABLE READREPEATABLE READ

  • SERIALIZABLESERIALIZABLE

  • READ COMMITTEDREAD COMMITTED

可以在本机编译存储过程的原子块中指定事务隔离级别。The transaction isolation level can be specified as part of the atomic block of a natively compiled stored procedure. 有关详细信息,请参阅 CREATE PROCEDURE (Transact-SQL)For more information, see CREATE PROCEDURE (Transact-SQL). 在从解释型 Transact-SQLTransact-SQL 访问内存优化表时,可以使用表级提示指定隔离级别。When accessing memory-optimized tables from interpreted Transact-SQLTransact-SQL, the isolation level can be specified using table-level hints.

定义本机编译存储过程时必须指定事务隔离级别。You must specify the transaction isolation level when you define a natively compiled stored procedure. 在从解释型 Transact-SQLTransact-SQL 中的用户事务访问内存优化表时,必须使用表级提示指定隔离级别。You must specify the isolation level in table hints when accessing memory-optimized tables from user transactions in interpreted Transact-SQLTransact-SQL. 有关详细信息,请参阅具有内存优化表的事务隔离级别准则For more information, see Guidelines for Transaction Isolation Levels with Memory-Optimized Tables.

自动提交事务对于内存优化表支持隔离级别 READ COMMITTED。The isolation level READ COMMITTED is supported for memory-optimized tables with autocommit transactions. READ COMMITTED 在用户事务或原子块中无效。READ COMMITTED is not valid in user transactions or in an atomic block. 显式或隐式用户事务不支持 READ COMMITTED。READ COMMITTED is not supported with explicit or implicit user transactions. 具有自动提交事务的内存优化表支持隔离级别 READ_COMMITTED_SNAPSHOT,并且仅在查询未访问任何基于磁盘的表时才支持。Isolation level READ_COMMITTED_SNAPSHOT is supported for memory-optimized tables with autocommit transactions and only if the query does not access any disk-based tables. 此外,在 SNAPSHOT 隔离级别下通过解释型 Transact-SQLTransact-SQL 启动的事务不能访问内存优化表。In addition, transactions that are started using interpreted Transact-SQLTransact-SQL with SNAPSHOT isolation cannot access memory-optimized tables. 在 REPEATABLE READ 或 SERIALIZABLE 隔离级别下使用解释型 Transact-SQLTransact-SQL 的事务必须使用 SNAPSHOT 隔离级别访问内存优化表。Transactions that are use interpreted Transact-SQLTransact-SQL with either REPEATABLE READ or SERIALIZABLE isolation must access memory-optimized tables using SNAPSHOT isolation. 有关此方案的详细信息,请参阅交叉容器事务For more information about this scenario, see Cross-Container Transactions.

SQL ServerSQL Server 中的默认隔离级别为 READ COMMITTED。READ COMMITTED is the default isolation level in SQL ServerSQL Server. 在会话的隔离级别为 READ COMMITED(或更低级别)时,可以执行以下操作之一:When the isolation level of the session is READ COMMITED (or lower), you can do one of the following:

  • 显式使用更高的隔离级别来访问内存优化表(例如,WITH (SNAPSHOT))。Explicitly use a higher isolation level hint for accessing the memory-optimized table (for example, WITH (SNAPSHOT)).

  • 指定 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 设置选项,它将内存优化表的隔离级别设置为 SNAPSHOT(如同向每个内存优化表加入 WITH(SNAPSHOT) 提示)。Specify the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT set option, which will set the isolation level for memory-optimized tables to SNAPSHOT (as if you included WITH(SNAPSHOT) hints to every memory-optimized table). 有关详细信息MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT,请参阅ALTER DATABASE SET 选项(TRANSACT-SQL)For more information about MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, see ALTER DATABASE SET Options (Transact-SQL).

或者,如果会话的隔离级别为 READ COMMITTED,则可以使用自动提交事务。Alternatively, if the isolation level of the session is READ COMMITTED, you can use autocommit transactions.

以解释型 Transact-SQLTransact-SQL 开头的 SNAPSHOT 事务无法访问内存优化表。SNAPSHOT transactions started in interpreted Transact-SQLTransact-SQL cannot access memory-optimized tables.

内存优化表所支持的事务隔离级别提供与基于磁盘的表相同的逻辑保证。The transaction isolation levels supported for memory-optimized tables provide the same logical guarantees as disk-based tables. 但用于提供隔离级别保证的机制有所不同。The mechanism used for providing isolation level guarantees is different.

对于基于磁盘的表,将使用锁定实现大多数隔离级别保证,从而防止因阻塞发生冲突。For disk-based tables, most isolation level guarantees are implemented using locking, which prevent conflicts through blocking. 对于内存优化表,将使用一种冲突检测机制来提供保证,从而无需使用锁。For memory-optimized tables, the guarantees are enforced using a conflict detection mechanism, which avoids the need to take locks. 基于磁盘的表的 SNAPSHOT 隔离属于例外情况。The exception is SNAPSHOT isolation on disk-based tables. 该隔离与内存优化表的 SNAPSHOT 隔离类似,也通过冲突检测机制实现。This is implemented similarly to SNAPSHOT isolation on memory-optimized tables using a conflict detection mechanism.

SNAPSHOTSNAPSHOT
此隔离级别指定:事务中任何语句所读取的数据都将是在该事务开始时便存在的数据的事务性一致版本。This isolation level specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. 事务只能识别在其开始之前提交的数据修改。The transaction can only recognize data modifications that were committed before the start of the transaction. 在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. 事务中的语句所获取的已提交数据快照对应于该数据在事务开始时的状态。The statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

写操作(更新、插入和删除)始终与其他事务完全隔离。Write operations (updates, inserts and deletes) are always fully isolated from other transactions. 因此,SNAPSHOT 事务中的写操作可能与其他事务的写操作发生冲突。Therefore, the write operations in a SNAPSHOT transaction can conflict with write operations by other transactions. 如果当前事务尝试更新或删除的行已由当前事务启动后提交的其他事务更新或删除,则当前事务会终止并显示以下错误消息。When the current transaction attempts to update or delete a row that has been updated or deleted by another transaction that committed after the current transaction started, the transaction terminates with the following error message.

错误 41302。Error 41302. 当前事务尝试更新的 X 表中的记录自此事务启动后已更新。The current transaction attempted to update a record in table X that has been updated since this transaction started. 该事务已中止。The transaction was aborted.

如果当前事务尝试插入的行所包含的主键值与在当前事务之前提交的其他事务所插入行的主键值相同,将会发生提交失败并显示以下错误消息。When the current transaction attempts to insert a row with the same primary key value as a row that was inserted by another transaction that committed before the current transaction, there will be a failure to commit with the following error message.

显示错误 41325。Error 41325. 因某个序列化读取验证失败,当前事务无法提交。The current transaction failed to commit due to a serializable validation failure.

如果一个事务向该事务提交之前已删除的表写入数据,则该事务会终止并显示以下错误消息:If a transaction writes to a table that is dropped before the transaction commits, the transaction terminates with the following error message:

显示错误 41305。Error 41305. 因某个可重复读取验证失败,当前事务无法提交。The current transaction failed to commit due to a repeatable read validation failure.

REPEATABLE READREPEATABLE READ
此隔离级别包含由 SNAPSHOT 隔离级别提供的保证。This isolation level includes the guarantees given by SNAPSHOT isolation level. 此外,REPEATABLE READ 还保证,对于由该事务读取的任何行,在该事务提交时,该行都不会由任何其他事务更改。In addition, REPEATABLE READ guarantees that for any row that is read by the transaction, at the time the transaction commits the row has not been changed by any other transaction. 在事务结束之前,该事务中的每个读取操作都是可重复的。Every read operation in the transaction is repeatable up to the end of the transaction.

如果当前事务读取的任何行已由在当前事务之前提交的其他事务更新,则提交会失败并显示以下错误消息。If the current transaction has read any row that has then been updated by another transaction that has committed before the current transaction, the commit fails with the following error message.

显示错误 41305。Error 41305. 因某个可重复读取验证失败,当前事务无法提交。The current transaction failed to commit due to a repeatable read validation failure.

SERIALIZABLESERIALIZABLE
此隔离级别包含由 REPEATABLE READ 提供的保证。This isolation level includes the guarantees given by REPEATABLE READ. 在获取快照与事务结束之间不会出现任何虚拟行。No phantom rows have appeared between the snapshot and the end of the transaction. 虚拟行与选择、更新或删除的筛选条件匹配。Phantom rows match the filter condition of a select, update, or delete.

事务执行时就好像不存在并发事务。The transaction is executed as if there are no concurrent transactions. 所有操作几乎都发生在单个序列化点 (提交时间)。All actions virtually occur at a single serialization point (commit time).

如果违反了其中任何一个保证,则该事务无法提交并显示以下错误消息:If any of these guarantees is violated, the transaction fails to commit with the following error message:

显示错误 41325。Error 41325. 因某个序列化读取验证失败,当前事务无法提交。The current transaction failed to commit due to a serializable validation failure.

请参阅See Also

了解内存优化表上的事务 Understanding Transactions on Memory-Optimized Tables
具有内存优化表的事务隔离级别的准则 Guidelines for Transaction Isolation Levels with Memory-Optimized Tables
内存优化表事务重试逻辑准则Guidelines for Retry Logic for Transactions on Memory-Optimized Tables