[SQL SERVER]Something about In-Memory isolation

2017-09-24

493
0
SQL Server 2016
2017-09-26

SQL Server預設將交易隔離等級套用至disk和memory table，

而共同目標都是要達到交易完整性，

而交易初始模式(Transaction Initiation Modes)將直接影響我們要使用那種isolation level，

開發人員有必要了解其中差異，以避免交易更新資料發生非預期錯誤或資料不一致情況。

SQL Server有四種交易初始模式

自動提交(autocommit)

簡單查詢或DML一開始會開啟隱性交易，而陳述句結尾將自動隱性提交交易，這是預設模式。

Update tbl set c2=’rico’

Where c1=1

明確(Explicit):

TSQL包含begin tran和commit tran，這時你必須針對memory table指定相關交易隔離等級。

Begin tran
Update tbl WITH (SNAPSHOT) set c2=’rico’
Where c1=1
Commit

隱性(Implicit)

必須啟用SET IMPLICIT_TRANSACTIONS ON，這好處就是讓你不用多寫begin tran和commit，但我個人幾乎沒使用過.

Atomic 區塊(block)

Atomic區塊內所有陳述式(需透過native compiled SP)，一律使用單一交易，錯誤就是整體rollback，成功即是整體commit。

當我們在查詢或更新disk table時，我們可以在陳述句中指定5種交易隔離等級的一種，

來達到資料一致性目標，但如果存取memory table，這其中有一些差異，

而這差異相依於使用什麼樣的交易初始模式。

begin tran
select c3
from myEmail_mem
where c2=9790
commit

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

明確交易模式下，沒有支援read committed，必須指定以下之一交易隔離等級

begin tran
select c3
from myEmail_mem with(SNAPSHOT)
where c2=9790
commit

begin tran
select c3
from myEmail_mem with(serializable)
where c2=9790
commit

begin tran
select c3
from myEmail_mem with(repeatableread)
where c2=9790
commit

而這也是我當時所遇到的第一個麻煩，明確交易初始模式且查詢memory table無法支援read committed，

當然也不支援read uncommitted(In-memory世界中，該層級完全沒有存在意義)，

這表示你得修改某些交易(查詢)所使用的table hint，

In-memory世界中的交易處理，是否一定要改寫TSQL都加上with(snapshot)?，

因為read commited只能在autocommitt交易初始模式下運作，如下

--begin tran
select c3
from myEmail_mem
where c2=9790
--commit
SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

但你卻不行透過table hint來指定readcommiteed，你將得到不支援的錯誤

select * from (
select c3
from myEmail_mem 
where c2=9790
union
select c3
from myEmail_mem with(readcommitted)
where c2=9791
) base

回到剛剛的問題，難道一定要改寫TSQL嗎?當然不一定要，

我後來是把DB啟用MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT，

該選項自動幫我們把memory table都加上with(snapshot) hint，

另外要注意，RCSI是控制陳述句層級，而Snapshot是控制交易層級。

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

跨容器交易

跨容器交易是指同時存取disk table和memory table，

這時各自資料表皆可使用原本所支援的交易隔離等級，且各種交易初始模式皆可運作，

但要注意disk table的snapshot 將不在支援，如下

begin tran
 
  SELECT *
  FROM dbo.myEmail_disk d WITH(READCOMMITTED)
  INNER JOIN dbo.myEmail_mem m WITH(SNAPSHOT) ON d.c1 = m.c1
 
commit

SELECT *
  FROM dbo.myEmail_disk d WITH(serializable)
  INNER JOIN dbo.myEmail_mem m WITH(SNAPSHOT) ON d.c1 = m.c1

Disk table原本使用shapshot，將不在支援，驗證階段直接拋出錯誤

SELECT *
  FROM dbo.myEmail_disk d WITH(SNAPSHOT)

SELECT *
  FROM dbo.myEmail_disk d WITH(SNAPSHOT)
  INNER JOIN dbo.myEmail_mem m WITH(SNAPSHOT) ON d.c1 = m.c1

begin tran
 
  SELECT *
  FROM dbo.myEmail_disk d WITH(SNAPSHOT)
  INNER JOIN dbo.myEmail_mem m WITH(SNAPSHOT) ON d.c1 = m.c1
commit