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?imoltp?ON PRIMARY(NAME=?[imoltp_data], FILENAME='c:dataimoltp_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:dataimoltp_dir')--?physical?path?to?thecontainer ,(NAME=?[imoltp_dir2],--?logical?name?of?the?2nd?memory-optimized?filegroupcontainer FILENAME='c:dataimoltp_dir2')--?physical?path?to?thecontainer LOG?ON(name?=?[imoltp_log],Filename='C:DATAimoltp_log.ldf',size=100MB) 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 ?
场景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_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 ?
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_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和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,?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数据库研究的还不是太多，自己的理解可能有问题，欢迎大家指正。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
