# In-Memory：内存优化表的事务处理

## 一，MOT使用乐观并发事务控制

1，并发控制策略

1.1，悲观策略（Pessimistic Approach）

1.2，乐观策略（Optimistic Approach）

2，MOT使用乐观并发控制（Optimistic Concurrency Control，简称OCC）

## 二，MOT支持的事务隔离级别（Transaction Isolation Level）

1，MOT的SNAPSHOT隔离级别

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.

SNAPSHOT隔离级别只会影响读操作，而写操作不受隔离级别的影响，和其他事务完全隔离，因此，在Snapshot隔离级别下，当并发事务尝试去更新同一行数据时，并发事务产生更新冲突，抛出错误 41302，41325，或41305，SQL Server选择一个开始时间晚的事务作为失败者，并回滚其操作，产生的Error是：

• Error 41302. 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.
• 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:
• Error 41305. The current transaction failed to commit due to a repeatable read validation failure.

2，提升事务的隔离级别

• ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
• 为MOT使用Table Hint：with(snapshot)

• 设置数据库选项：MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 为ON

3，事务初始化模式（Transaction Initiation Modes）

SQL Server 支持四种事务初始化模式：
• Autocommit：自动提交模式（默认模式），将单个语句作为一个事务，在语句开始时，隐式开始一个事务；在语句结束时，隐式提交该事务；
• 在autocommit模式下，访问MOT不需要使用Table Hint指定事务隔离级别；SQL Server自动为MOT应用SNAPSHOT隔离。
• Explicit：显式模式，使用begin tran 显式开始一个事务，使用commit tran 提交事务，或使用rollback tran 回滚事务。在显式事务中，将事务中的一个，或多个查询语句作为单个事务进行处理；
• 在显式模式下，访问MOT必须使用SNAPSHOT隔离级别，通过使用Table Hint 指定SNAPSHOT 隔离级别，
• 或设置数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 为ON来实现；
• Implicit：隐式模式，查询语句隐式开始一个事务，必须显式使用commit tran 提交事务，或使用rollback tran回滚事务。使用该模式，必须设置选项：
SET IMPLICIT_TRANSACTION ON
• Atomic block：原子块模式，只能用于Natively Compiled SP中。在Atomic block中的所有查询语句都作为单个事务提交或回滚。
• 在Atomic block中，支持的事务隔离级别是：TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
• 在Natively Compiled SP中，使用BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) 定义Atomic block事务：
create procedure schema_name.sp_name
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level=snapshot, language=N'us_english')
statement1;
statement2;
....
end 
View Code

## 三，访问MOT的事务隔离级别

The transaction isolation level 'READ UNCOMMITTED' is not supported with memory optimized tables.

• 在Autocommit （单语句事务）模式下，能够访问MOT；
• 在显式和隐式模式下，不能访问MOT；

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).

• 使用Table Hint：with(snapshot)，该hint只能用于MOT；WITH(REPEATABLEREAD) 和 WITH(SERIALIZABLE) ；
• 设置数据库选项：MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 为ON；
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

3，如果设置Session的隔离级别为Snapshot，无法访问MOT

alter database current set allow_snapshot_isolation on
set transaction isolation level snapshot

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

The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

## 四，行版本（Row Version）

The table has three rows: r1, r2, and r3. r1 has three versions, r2 has two versions, and r3 has four versions. Note that different versions of the same row do not necessarily occupy consecutive memory locations. The different row versions can be dispersed throughout the table data structure.

1，MOT的多版本（Multi-Versioning）

MOT的同一行数据可以有不同的版本，因此，并发执行事务可能访问同一行数据的不同版本，由于在同一时刻，任何数据行都有可能拥有不同行版本，并且都是有效的；如果根据数据行的不同版本执行数据更新操作，有可能产生逻辑错误。MOT维护的多行版本（Row-Version）不是存储在tempdb中，而是直接存储在MOT中，作为MOT数据结构的一部分存储在内存中。

2，使用行版本实现Snapshot事务隔离

• 在事务开始时，其他事务已经提交更新的数据版本，能够被当前事务识别；如果其他事务没有提交更新，那么当前事务读取不到更新之后的数据，只能读取到已经存在，事务已经提交更新的数据；
• 在事务开始之后，其他事务所执行的数据更新不会被当前事务识别；例如：
• 其他事务插入的新数据不会被当前事务读取到；
• 其他食物删除的旧数据，当前事务仍然能够读取到；

## 五，MOT的事务处理

1，交叉事务（cross-container transaction）

2，访问MOT的事务生命周期

Phase1：常规处理阶段，事务所有的查询和更新操作都在这个阶段执行：

• 在该阶段，有时会产生更新冲突（Update Conflict），如果当前事务更新的数据行，被其他事务更新，但未提交，那么会产生更新冲突；
• If any query tries to update a row that has already been updated by an active transaction, an ‘update conflict’ error is generated.
• 在该阶段，有时会产提交依赖（Commit Dependence），这是因为事务读取到被其他事务更新，但是尚未提交（处于验证或提交阶段）；
• 依赖失败（Dependency failure）：如果当前事务依赖的事务提交失败，那么当前事务失败，产生错误 41301;
• During regular processing, a transaction can read rows written by other transactions that are in the validation or commit phase, but have not yet committed. The rows are visible because the logical end time of the transactions has been assigned at the start of the validation phase.

Phase2：验证阶段，从该阶段开始时，在逻辑上事务已经完成，只是没有提交，其他事务能够看到当前事务更新之后的数据值；

• 在验证阶段开始时，事务的更新操作已经完成，认为事务逻辑上完成，这使得事务更新对其他事务可见。在该阶段，事务并没有提交，SQL Server对事务更新进行验证；
• The validation phase begins by assigning the end time, thereby marking the transaction as logically complete. This makes all changes of the transaction visible to other transactions, which will take a dependency on this transaction, and will not be allowed to commit until this transaction has successfully committed. In addition, transactions which hold such dependencies are not allowed to return result sets to the client to ensure the client only sees data that has been successfully committed to the database.
• If any of the rows have been updated or changed, the transaction fails to commit with error 41305 ("The current transaction failed to commit due to a repeatable read validation failure.").
• 对于Serializable，检查数据范围是有更新，在数据范围中，检查是否有其他事务插入新的数据行，是否有数据行被其他事务删除，如果数据范围变化，那么事务验证失败，抛出错误 41325；
• The system validates that no phantom rows have been written to the database. The read operations performed by the transaction are evaluated to determine that no new rows were inserted in the scan ranges of these read operations.
• This phase comprises the repeatable read and serializable validation. For repeatable read validation it checks whether any of the rows read by the transaction has since been updated. For serializable validation it checks whether any row has been inserted into any data range scanned by this transaction.

Phase3：事务提交处理阶段，事务日志记录到日志文件，事务提交完成，一旦日志写入到Disk，控制权返回到客户端

• During the commit phase, the changes to durable tables are written to the log, and the log is written to disk.
• Once the log record for the transaction has been written to disk, control is returned to the client.
• After commit processing completes, all dependent transactions are notified that they can commit.

3，等待（Waiting）

• 如果一个事务依赖其他事务，那么将产生提交依赖，必须等待其他事务提交成功，当前事务才能提交；
• 等待事务日志持久化写入到Disk上的事务日志文件（.ldf）中；
• 提交依赖等待不能避免，通常持续的时间非常短暂；

• 使用Delayed Durability；
• 创建Non-Durable的MOT，使用SCHEMA_ONLY将完全避免日志写操作，对非持久化表执行的任何更新操作都不会产生任何的日志IO操作；

## 六，冲突检测和重试逻辑（Conflict Detection and Retry Logic）

1，冲突检测

• 并发事务之间产生冲突，分为更新冲突（Update Conflict）和验证失败（Validation Failure）：
• 更新冲突：在同一时刻，有两个并发事务尝试更新同一数据行；错误代码是41302；
• This error condition occurs if two concurrent transactions attempt to update or delete the same row at the same time. One of the two transactions receives this error message and will need to be retried.
• 依赖失败：当前事务依赖其他事务，而依赖的事务提交失败；错误代码是 41301；

2，重试逻辑（Retry Logic）

-- Retry logic, in Transact-SQL.
CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
DECLARE @retry 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, 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; 
View Code

## 七，事务的懒提交（Lazy Commit）

• 事务提交不需要等待写日志操作的完成，一旦查询语句执行完成，就把控制权返回给Client，提高了数据更新的响应速度；
• 减少并发的事务产生写日志竞争的可能性；
• 在懒提交模式下，日志被缓存起来，系统一次能够将更大块的日志记录写入到Disk，减少了Disk IO竞争，提高了数据更新的性能；

1，将数据库设置为懒提交模式

ALTER DATABASE DatabaseName
SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }  

2，在Natively Compiled SP中，将Atomic Block设置为懒提交

CREATE PROCEDURE <procedureName> …
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English' …
)
END

3，在Commit子句中，指定懒提交选项

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

Transactions in Memory-Optimized Tables

Introduction to Memory-Optimized Tables

Transactions with Memory-Optimized Tables

Control Transaction Durability

posted @ 2017-01-05 19:02  悦光阴  阅读(1914)  评论(4编辑  收藏