2014 OLTP Memory Database lock 测试
根据微软的说法，由于内存数据库使用了乐观并发控制 ，所以事务不需要锁来锁定资源保证一致性。微软认为在内存数据库的事务中冲突和失败是非常少的情况，所以假定所有的并行事务都是成功的。因此写不会阻塞读，写也不阻塞写。 下面是原文：Transactionsin Me
根据微软的说法，由于内存数据库使用了乐观并发控制 ，所以事务不需要锁来锁定资源保证一致性。微软认为在内存数据库的事务中冲突和失败是非常少的情况，所以假定所有的并行事务都是成功的。因此写不会阻塞读，写也不阻塞写。
下面是原文：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 imoltp ON
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 primarykey nonclustered hash with (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 *FROM sys.dm_tran_locksWHERErequest_session_id = @@SPID
SELECT *FROM sys.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 *FROM sys.dm_tran_locksWHERErequest_session_id = @@SPID
SELECT *FROM sys.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 *FROM sys.dm_tran_locksWHERErequest_session_id = @@SPID
SELECT *FROM sys.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数据库研究的还不是太多，自己的理解可能有问题，欢迎大家指正。