2014 OLTP Memory Database lock 测试

根据微软的说法，由于内存数据库使用了乐观并发控制 ，所以事务不需要锁来锁定资源保证一致性。微软认为在内存数据库的事务中冲突和失败是非常少的情况，所以假定所有的并行事务都是成功的。因此写不会阻塞读，写也不阻塞写。

下面是原文：Transactionsin Memory-Optimized Tables

Instead, transactions proceedunder the (optimistic) assumption that there will be no conflicts with othertransactions.Not usinglocks and latches and not waiting for other transactions to finishprocessing the same rows improves performance.

In addition, if a transactionreads rows that other transactions have finished processing and are in theprocess of committing, it will optimistically assume the transactions commit,rather than wait for the commit to occur. Transactions on memory-optimizedtables will take a commit dependency on these other transactions

这里我做了一个测试：

--Create OLTP Memroy database

CREATE DATABASE imoltpON

PRIMARY(NAME= [imoltp_data],

FILENAME = 'c:/data/imoltp_mod1.mdf', size=100MB)

FILEGROUP [imoltp_mod]CONTAINSMEMORY_OPTIMIZED_DATA(--name of the memory-optimized filegroup

NAME =[imoltp_dir], -- logical name of a memory-optimizedfilegroup container

FILENAME = 'c:/data/imoltp_dir')-- physical path to thecontainer

,(NAME= [imoltp_dir2], -- logical name of the 2nd memory-optimized filegroupcontainer

FILENAME = 'c:/data/imoltp_dir2')-- physical path to thecontainer

LOG ON(name= [imoltp_log],Filename='C:/DATA/imoltp_log.ldf',size=100MB)

GO

--Create OLTP Memroy table

CREATE TABLE dbo.Table1(

Id_tb1 int not null primarykeynonclustered hashwith (bucket_count=20480),

Int_Valint not null index ix_Int_Valnonclusteredhashwith(bucket_count=10240),

CreateDate datetime2 not null,

[Description] varchar(255)

)

WITH (MEMORY_OPTIMIZED=ON)

GO

--insert data and query lock info

BEGIN TRAN

INSERT dbo.Table1VALUES(1,427,getdate(),'Insert transaction')

SELECT *FROMsys.dm_tran_locksWHERErequest_session_id= @@SPID

SELECT *FROMsys.dm_db_xtp_transactions

COMMIT TRAN

场景1：session1更新数据在session2查询

---session 1 update not commit

BEGIN TRAN

UPDATE dbo.Table1 WITH(SNAPSHOT)

SET [Description] = 'Updatedtransaction'

WHERE Id_tb1 = 1

SELECT *FROMsys.dm_tran_locksWHERErequest_session_id= @@SPID

SELECT *FROMsys.dm_db_xtp_transactions

--Session 2 query information, noblocking

SELECT *

FROM dbo.Table1

WHERE Id_tb1 = 1

Session1的lock信息如下,可以看到没有表或者Page之类的Lock,只有Schema和Database Lock.

Session2可以正常查询到更新之前的数据，没有阻塞

场景2:

---Session 1 update not commit

BEGIN TRAN

UPDATE dbo.Table1 WITH(SNAPSHOT)

SET[Description] = 'Inserttransaction'

WHERE Id_tb1 = 1

SELECT *FROMsys.dm_tran_locksWHERErequest_session_id= @@SPID

SELECT *FROMsys.dm_db_xtp_transactions

---Session 2 update the same row

BEGIN TRAN

UPDATE dbo.Table1 WITH(SNAPSHOT)

SET[Description] = 'Inserttransaction'

WHERE Id_tb1 = 1

---Session 3 delete row

BEGIN TRAN

DELETE dbo.Table1 WITH(SNAPSHOT)

WHERE Id_tb1 = 1

Session2和session3 直接报错，没有等待时间。错误如下：

Msg 41302, Level 16, State 110, Line 15

The current transaction attempted to update a record that has been updatedsince this transaction started. The transaction was aborted.

Msg 3998, Level 16, State 1, Line 12

Uncommittable transaction is detected at the end of the batch. Thetransaction is rolled back.

The statement has been terminated.

这跟普通的数据库是不一样的，在普通数据库中由于session1锁定资源，所以session2和session3都会被block. 所以针对于前面的情况需要有重试机制：

Guidelinesfor Retry Logic for Transactions on Memory-Optimized Tables

OLTP数据库如何做冲突检测可以参考：Transactionsin Memory-Optimized Tables

因为2014数据库研究的还不是太多，自己的理解可能有问题，欢迎大家指正。