[SQL Server ][MemoryOptimizedTable]記憶體資料表使用快照隔離時的查詢一致性與衝突

2017-12-16

2275
0
SQLMemoryOptimized
2017-12-21

經過上一篇的實驗，磁碟資料表(Disk-based table)啟用讀取認可快照(RCSI)或是快照隔離(Snapshot isolation)都能到使用資料列版本讀取到前一版的資料而避免封鎖(blocked)，特別兩者在處理”資料一致性的層級”以及”交易發生衝突的處理上”有些不同；那麼到了記憶體資料表(Memory-optimized table)?

在記憶體資料表的存取上，因為最基本的交易隔離就是快照了，來實驗記憶體資料表在快照隔離層級時的資料一致性層級與交易衝突點。

先看實驗結果: 記憶體資料表在快照隔離時採用交易層級的一致性，執行update/delete語法時就會檢查衝突，若有衝突，SQL產生41302 WRITE_CONFLICT訊息。

測試版本: SQL Server 2016 SP1

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT

由於記憶體資料表使用外顯交易時(Explicit ,BEGIN TRAN)不支援使用READ COMMITTED隔離等級，為了避免收到SQL 41368的錯誤訊息，又不想DML語法都要加上Table Hint With(SNAPSHOT)，我們這次從資料庫層設定MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT屬性，當交易隔離層級使用READ COMMITTED(預設)或READ UNCOMMITTED時，自動隱含提升記憶體Table的層級提升至SNAPHOT。

41368本人生活照:

41368 IMPLICIT_AND_EXPLICIT_TX_NOT_SUPPORTED :

只有自動認可交易才支援使用 READ COMMITTED 隔離等級來存取記憶體最佳化的資料表。明確或隱含交易則不支援。為使用資料表提示例如 WITH (SNAPSHOT) 的記憶體最佳化資料表，提供支援的隔離等級。

建立資料庫並將記憶體資料表提升至快照隔離層級

CREATE DATABASE IMDb;
Go
USE IMDb;
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;

建立記憶體資料檔案群組、檔案及記憶體資料表

ALTER DATABASE IMDb ADD FILEGROUP IMDb_mod CONTAINS 
MEMORY_OPTIMIZED_DATA  
ALTER DATABASE IMDb ADD FILE (name='IMDb_mod1', 
filename='F:\Data\IMDb_mod1') TO FILEGROUP IMDb_mod

USE IMDb;
DROP TABLE IF EXISTS T1 
CREATE TABLE T1
(
           ID INT IDENTITY ,
           NAME VARCHAR(30)
		    CONSTRAINT PK_T1 PRIMARY KEY NONCLUSTERED HASH(ID)  
    WITH (BUCKET_COUNT=1024)  
)
WITH  (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA);
INSERT INTO T1 VALUES('安娜')

測試Snapshot isolation的一致性(記憶體資料表版)

查詢視窗1

USE IMDb
SELECT *,GETDATE() FROM T1
WAITFOR DELAY '00:00:01'
BEGIN TRAN 
UPDATE T1   
   SET NAME = '艾莎' 
 WHERE ID = 1
SELECT *,GETDATE() FROM T1
WAITFOR DELAY '00:00:10'
COMMIT
SELECT *,GETDATE() FROM T1

10秒以內啟動查詢視窗2

--Memory optimized tables and natively compiled modules 不需要設定交易層級snaphot，否則會出sql error 41368)
--SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
USE IMDb
BEGIN TRAN 
SELECT *,GETDATE() FROM T1 
--UPDATE T1   
--   SET NAME = 'elsa' 
 WHERE ID = 1
WAITFOR DELAY '00:00:10'
SELECT *,GETDATE() FROM T1 
COMMIT
SELECT *,GETDATE() FROM T1

session2交易過程中，即使在session1 交易中或交易後的時間點，session2 都是讀到相同的資料(修改前的安娜)，session2擁有自己交易開始時的版本，如同磁碟資料表在snapshot isolation，維持著Transaction level的一致性。

(預設的read committed因為剛剛MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT的參數，因此會提升至snapshot)

session2交易中的兩次查詢都是安娜

更新衝突

因為session 2交易不會被封鎖(blocked)，如果session2在交易中也更新了t1這1筆資料，之前實驗磁碟資料表RCSI與snapshot isolation時，兩個機制會有截然不同結果，即使磁碟資料表在snapsht isolation，也是session1 commit時檢查。

但在記憶體資料表，session2相較session1是後發生的交易，一執行到DML Update馬上出現sql error 41302 WRITE_CONFLICT:目前交易嘗試更新自從此交易啟動以來已經更新的記錄。交易已中止。

USE IMDb
BEGIN TRAN 
SELECT *,GETDATE() FROM T1 
UPDATE T1   
   SET NAME = 'elsa' 
 WHERE ID = 1
WAITFOR DELAY '00:00:10'
SELECT *,GETDATE() FROM T1 
COMMIT
SELECT *,GETDATE() FROM T1

41302生活照

小結

整理磁碟資料表(A,B)與記憶體資料(C)表在使用快照隔離層級時的比較:

比較項目	A.磁碟資料表 +RCSI	B.磁碟資料表 +Snspshot Isolation	C.記憶體資料表 +ELEVATE TO SNAPSHOT
資料庫屬性設定	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION	MEMORY_OPTIMIZED_ ELEVATE_TO_SNAPSHOT
交易設定	不用，預設就是READ_COMMITTED	Set transaction isolation level SNAPSHOT	不用，設了快照層級還會出 SQL Error 41368
資料一致性層級	Statement	Transaction	Transaction
交易衝突	系統不會警示	Session 1 Commit時檢查，若有衝突，Session2 回SQL Error 3960 update conflict	Session 2執行update語法時就會檢查。若有衝突41302 WRITE_CONFLICT
基礎	Tempdb	Tempdb	記憶體結構

從上表也注意到，當一個交易同時存取磁碟及記憶體資料表(跨容器交易)時，或是同一句statement想要讓磁碟資料表和記憶體資料表join/union(interop query)時，為了想要兩者使用相同的隔離層級，像是都想要讀取前一版資料時會選擇快照隔離，但B和C，前者必須宣告Set transaction isolation level SNAPSHOT，後者宣告就會產生41368錯誤，但在同一個交易使用時就產生了使用互斥。

Trade off: 啟動RCSI讀磁碟資料表? 或是磁碟資料表用read committed，記憶體資料表用snapshot的混搭風?

磁碟資料表是以tempdb為基礎管理版本，記憶體資料表則是記憶體結構。
單純存取記憶體資料表時也支援隔離層級還有更嚴謹的REPEATABLE READ(讀了鎖別人刪修)及SERIALIZABLE(讀了鎖別人增刪修)，跨容器或interop就不行了。
碰到41302時，就像SQL 1205 Deadlock，AP Re-try。

Let it Go姊妹花