In-Memory：記憶體優化表的事務處理

記憶體優化表（Memory-Optimized Table，簡稱MOT）使用樂觀策略（optimistic approach）實現事務的併發控制，在讀取MOT時，使用多行版本化（Multi-Row versioning）創建數據快照，讀操作不會對數據加鎖，因此，讀寫操作不會相互阻塞。寫操作會申請行級鎖 ...

記憶體優化表（Memory-Optimized Table，簡稱MOT）使用樂觀策略（optimistic approach）實現事務的併發控制，在讀取MOT時，使用多行版本化（Multi-Row versioning）創建數據快照，讀操作不會對數據加鎖，因此，讀寫操作不會相互阻塞。寫操作會申請行級鎖，如果兩個事務嘗試更新同一數據行，SQL Server檢測到寫-寫衝突，產生錯誤（Error 41302），將後後創建的事務作為失敗者，回滾事務的操作。雖然MOT事務使用無鎖結構（Lock-Free），不會產生阻塞，但是，訪問MOT仍然會產生Wait，通常情況下，等待時間是非常短暫的。

一，MOT使用樂觀併發事務控制

1，併發控制策略

事務的併發控制策略分為樂觀策略和悲觀策略，SQL Server支持兩種併發策略。

1.1，悲觀策略（Pessimistic Approach）

悲觀策略認為每一個數據更新都潛在地存在衝突，為了避免數據爭用，事務在讀取數據時申請共用鎖，在更新數據時對數據加互斥鎖（Locking）。在衝突發生時，通過加鎖阻塞其他事務；其他事務檢測到衝突後，等待擁有資源的事務釋放互斥鎖，其他事務只有獲取到資源上的加鎖，才能執行讀寫操作。

悲觀策略主要用於數據爭用激烈，並且發生髮衝突時用鎖保護數據的成本低於回滾事務的成本的環境中。

1.2，樂觀策略（Optimistic Approach）

樂觀策略認為執行的數據更新操作很少存在衝突，事務在讀取數據時，不鎖定數據；在更新數據時，事務只在提交時檢查更新的有效性，如果有其他事務更新該數據，將產生更新衝突的錯誤，那麼事務不等待，SQL Server選擇一個事務作為失敗者，並回滾事務執行的操作。樂觀策略效率更高，部分原因是在大多數情況下，更新衝突不經常發生。當衝突發生時，使用悲觀策略，事務需要等待；使用樂觀策略，SQL Server使事務失敗，回滾事務操作。

樂觀策略主要用於數據爭用不大，並且偶爾回滾事務的成本低於讀取數據時鎖定數據的成本的環境中。

樂觀估計效率更高，部分原因是在大多數情況下，事務衝突不經常發生。當衝突發生時，使用悲觀估計法，事務需要等待；使用樂觀估計法，SQL Server使事務失敗，並回滾事務操作，因此，在發生更新衝突時，需要在客戶端進行異常檢測，重新執行事務。

2，MOT使用樂觀併發控制（Optimistic Concurrency Control，簡稱OCC）

樂觀策略使用行版本化（row versioning）實現併發控制，對於disk-based table，使用tempdb存儲行版本數據；對於MOT，在記憶體中存儲行版本數據。

樂觀策略認為衝突和失敗是不常見的，OCC認為訪問MOT的事務不會和其他併發執行的事務產生衝突，任何操作都會執行成功。在訪問MOT時，事務不會加鎖（Lock或Latch）以保證讀操作的隔離性，因此，讀寫操作互不阻塞，也不會產生等待。一旦產生寫-寫衝突，SQL Server將選擇創建時間晚的事務作為失敗者，並回滾該事務操作。

二，MOT支持的事務隔離級別（Transaction Isolation Level）

在In-Memory OLTP系統中，存在兩種事務隔離級別，訪問硬碟表（Disk-Based Table，簡稱DBT）的事務，和訪問MOT的事務；和傳統的事務隔離級別不同，在一個事務中，存在兩個隔離級別。

1，MOT的SNAPSHOT隔離級別

實際上，訪問MOT，事務必須處在SNAPSHOT隔離級別下，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.

在SQL Server 2016中，有兩種方式指定隔離級別：當在解釋性TSQL中訪問MOT時，使用Table Hint指定SNAPSHOT隔離級別；當在Natively Compiled 存儲過程中訪問MOT時，必須在Atomic Block中指定隔離級別為SNAPSHOT。

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，提升事務的隔離級別

在顯式事務（Explicit）模式中，如果預設的事務隔離級別低於SNAPSHOT，那麼必須提升事務隔離級別，才能訪問MOT，有兩種實現方式：

設置資料庫選項 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 為ON，該選項的作用是：當事務隔離級別比SNAPSHOT低時（比如，READ COMMITTED or READ UNCOMMITTED），訪問MOT的事務都會自動升級到SNAPSHOT隔離級別：

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

為MOT使用Table Hint：with(snapshot)

因此，在顯式事務中，通過解釋性（Interpreted）TSQL訪問MOT時，必須：

使用Table Hint指定隔離級別：WITH(SNAPSHOT)，WITH(REPEATABLEREAD) 和 WITH(SERIALIZABLE)
設置資料庫選項：MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 為ON

如果發生MSSQLSERVER_41333 錯誤，說明產生交叉事務隔離錯誤（CROSS_CONTAINER_ISOLATION_FAILURE），原因是當前事務的隔離級別太高，解決方法是：將Session-Level的事務隔離級別降低到Read Committed。

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的事務隔離級別

在訪問MOT時，最方便的做法是：使用預設的隔離級別 Read Committed，並且設置資料庫選項：MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 為ON。

1, 如果設置Session的隔離級別為Read Uncommitted，事務訪問MOT，將產生錯誤，MOT不支持Read Uncommitted隔離級別

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

2，如果設置Session的隔離級別為Read Committed：

在Autocommit （單語句事務）模式下，能夠訪問MOT；
在顯式和隱式模式下，不能訪問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).

要想在顯式事務或隱式事務模式下訪問MOT，有兩種方式：

使用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

訪問MOT，將產生錯誤，MOT 和 Natively Compiled模塊在Session的事務隔離為Snapshot時無法訪問或創建：

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

4，如果設置Session的隔離級別為Repeatable Read or Serializable時，訪問MOT必須使用snapshot隔離級別；

如果Session的隔離級別是Repeatable Read 或 Serializable，那麼訪問MOT必須使用Table Hint：with(snapshot)，在snapshot隔離級別下訪問MOT：

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.

綜上所述，訪問MOT時，需要設置相容的事務隔離級別：

四，行版本（Row Version）

對硬碟表（Disk-Based Table，簡稱DBT），Snapshot隔離級別將行版本化的數據存儲在tempdb中；在其他隔離級別（例如，Read Committed，Repeatable，Serializable）下，事務通過加鎖避免衝突。對於MOT，事務不會加鎖，MOT使用多行版本實現事務的併發控制，和Disk-Based Table不同的是，MOT的版本化數據存儲在MOT的記憶體數據結構中，而不是存儲在tempdb中。MOT的每一個數據行在記憶體中可能存在多個版本，每一個版本都保存在相同的數據結構中。實際上，MOT的數據結構是Row Version的集合，相同Row的不同Version不需要存儲在連續的記憶體地址中，每一個Row Version是分散地存儲在MOT中，每一個Row Version使用8B的記憶體地址來定址。

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的所有操作，都使用在事務上一致的快照（Transactionally-Consistent），所謂事務一致性是指在一個事務開始時，創建MOT的數據快照，在該事務活躍期間，事務的所有操作都是基於該數據行快照。如果其他事務修改數據，不會影響該事務讀取的數據，例如其他事務將數據由3更新成4，在當前事務中，讀操作讀到的數據仍然是3；如果在當前事務中嘗試修改已被其他事務修改的數據，將產生更新衝突。

訪問MOT的事務使用行版本化（row versioning）獲得一個事務一致性的數據快照（snapshot），在單個事務中，任何數據操作讀取的數據是：

在事務開始時，其他事務已經提交更新的數據版本，能夠被當前事務識別；如果其他事務沒有提交更新，那麼當前事務讀取不到更新之後的數據，只能讀取到已經存在，事務已經提交更新的數據；
在事務開始之後，其他事務所執行的數據更新不會被當前事務識別；例如：
- 其他事務插入的新數據不會被當前事務讀取到；
- 其他食物刪除的舊數據，當前事務仍然能夠讀取到；

五，MOT的事務處理

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

交叉事務是指在一個事務中，解釋性TSQL語句同時訪問MOT和DBT。在交叉事務中，訪問MOT的操作和訪問DBT（Disk-Based Table）的操作都擁有自己獨立的事務序號，就像在一個大的交叉事務下，存在兩個單獨的子事務，分別用於訪問MOT和DBT；在sys.dm_db_xtp_transactions (Transact-SQL)中，訪問DBT的事務使用transaction_id標識，訪問MOT的事務序號使用xtp_transaction_id標識。

2，訪問MOT的事務生命周期

當事務涉及到MOT時，處理事務的生命周期（lifetime）分為三個phase：常規處理，驗證階段，提交處理，如圖：

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.
在驗證階段，對Repeatable Read 和 Serializable進行驗證，，檢查數據範圍是否有更新。
- 對於Repeatable Read，檢查行是否是重覆讀的，如果有數據行被其他事務更新，那麼事務提交失敗，拋出錯誤 41305；
  - 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）

訪問MOT使用樂觀多版本併發控制，不需要加鎖，不會產生阻塞，但是，仍然會產生等待（Waiting），但是，永遠不可能等待Lock釋放，而是等待：

如果一個事務依賴其他事務，那麼將產生提交依賴，必須等待其他事務提交成功，當前事務才能提交；
等待事務日誌持久化寫入到Disk上的事務日誌文件（.ldf）中；
提交依賴等待不能避免，通常持續的時間非常短暫；

在執行數據更新操作，需要等待事務日誌持久化寫入到Disk，雖然等待持續的時間通常非常短暫，但是，可以通過以下兩個方式來避免：

使用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.
- 驗證失敗：驗證事務更新是否滿足隔離級別Repeatable Read 和 Serializable的條件，檢查數據行是否重覆讀，檢查數據範圍是否不變；錯誤代碼是41305，41325；
依賴失敗：當前事務依賴其他事務，而依賴的事務提交失敗；錯誤代碼是 41301；

2，重試邏輯（Retry Logic）

如果事務失敗是由於上述兩種情況，那麼這個事務應該重新執行，重試邏輯可以實現在Client或Server端，通常推薦在Client實現重試邏輯，因為在Client端執行重試邏輯更高效，並能對事務失敗的異常進行複雜處理。

在Server端執行重試邏輯，僅用於在事務失敗時，不向Client返回任何結果集，重試邏輯的示例代碼如下：

-- 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）

在SQL Server中，事務提交可以是完全持久化的（Full Durable，預設），也可以是延遲持久化的（Delayed Durable），也叫做Lazy Commit。

完全持久化（Full Durable）事務是指：只有當事務日誌記錄寫入到Disk上的事務日誌文件(.ldf)之後，事務才提交成功，並將控制權返回到客戶端（Client）；而延遲持久化（Delayed Durable）事務是指：寫事務日誌的操作是非同步，事務在事務日誌寫入Disk之前，提交成功，就是說，一旦查詢語句執行成功，事務就提交成功，並將控制權返回到Client，但是數據更新可能並沒有記錄到事務日誌文件（.ldf）中，直到事務更新的日誌被持久化記錄到Disk上的事務日誌文件之後，數據更新才變成持久，存儲數據更新丟失的可能性。

懶提交事務持久化使用非同步寫模式，將事務日誌非同步地寫入到事務日誌文件（.ldf）中。在非同步寫日誌模式下，SQL Server把產生的事務日誌先保存在緩存中，直到填滿緩存空間，或發生緩存刷新事件，事務日誌才被寫入到事務日誌文件（.ldf）中。懶提交之所以能夠減少IO操作的延遲和競爭，是因為有以下三點優勢：

事務提交不需要等待寫日誌操作的完成，一旦查詢語句執行完成，就把控制權返回給Client，提高了數據更新的響應速度；
減少併發的事務產生寫日誌競爭的可能性；
在懶提交模式下，日誌被緩存起來，系統一次能夠將更大塊的日誌記錄寫入到Disk，減少了Disk IO競爭，提高了數據更新的性能；

在SQL Server 2016中，有以下三種方式使用懶提交模式：

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