2014 OLTP Memory Database lock test

According to Microsoft, because the memory database uses Optimistic Concurrency Control, transactions do not need to be locked to lock resources to ensure consistency. Microsoft believes that there are very few conflicts and failures in the transactions in the memory database, so it is assumed that all parallel transactions are successful. Therefore, writing does not block reading and writing.

The following is the original article: Transactionsin Memory-Optimized Tables

Instead, transactions beyond 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

Here I did a test:

-- Create OLTP Memroy database

Create database imoltp ON

PRIMARY (NAME = [imoltp_data],

FILENAME = 'C: \ data \ imoltp_mod1.mdf ', size = 100 MB)

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 = 100 MB)

GO

-- Create OLTP Memroy table

Create table dbo. Table1 (

Id_tb1 intnotnullprimarykeynonclusteredhashwith (bucket_count = 20480 ),

Int_Valintnotnullindex ix_Int_Valnonclusteredhashwith (bucket_count = 10240 ),

CreateDate datetime2notnull,

[Description] varchar (255)

)

WITH (MEMORY_OPTIMIZED = ON)

GO

-- Insert data and query lock info

BEGIN TRAN

INSERT dbo. Table1 VALUES (1,427, getdate (), 'insert transaction ')

SELECT * FROM sys. dm_tran_locks WHERErequest_session_id =@@ SPID

SELECT * FROM sys. dm_db_xtp_transactions

COMMIT TRAN

 

Scenario 1: Session1 update data query in 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_locks WHERE request_session_id =@@ SPID

SELECT * FROM sys. dm_db_xtp_transactions

 

-- Session 2 query information, noblocking

SELECT * FROM dbo. Table1 WHERE Id_tb1 = 1

 

The lock information of Session1 is as follows. We can see that there are no locks such as tables or pages, only Schema and Database Lock.

650) this. width = 650; "title =" 2013-12-03 9-17-12.png "style =" float: none; "src =" http://www.bkjia.com/uploads/allimg/131229/2221495262-0.png "alt =" 103104837.png"/>

 

Session2 can normally query the data before the update, without blocking

 

650) this. width = 650; "title =" 1.png" style = "float: none;" src = "http://www.bkjia.com/uploads/allimg/131229/2221491c4-1.png" alt = "103102323.png"/>

Scenario 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_locks WHERE request_session_id =@@ SPID

SELECT * FROM sys. dm_db_xtp_transactions

 

--- Session 2 update the same row

BEGINTRAN

UPDATE dbo. Table1 WITH (SNAPSHOT)

SET [Description] = 'inserttransaction'

WHERE Id_tb1 = 1

--- Session 3 delete row

BEGINTRAN

DELETE dbo. Table1 WITH (SNAPSHOT)

WHERE Id_tb1 = 1

 

Session2 and session3 directly report an error with no waiting time. The error is as follows:

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.

 

This is different from a common database. In a general database, because session1 locks resources, session2 and session3 will be blocked. Therefore, a Retry Mechanism is required for the preceding situations:

Guidelinesfor Retry Logic for Transactions on Memory-Optimized Tables

 

For details about how to detect conflicts in an OLTP database, refer to Transactionsin Memory-Optimized Tables.

 

Because 2014 of database research is not too much, your understanding may be problematic. please correct me.

 

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1335258