我正在尝试更新内存中 OLTP 表。有一种情况我们可能必须并行更新同一行。在同一记录的并发更新期间，我收到以下报告的错误。这是我的示例更新声明。
在 SQL 窗口 1 中同时在窗口 2 中执行以下命令时，我正在执行第二个更新命令
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
BEGIN TRY
UPDATE [TestInmemory] SET CreatedDate = GETDATE() WHERE Id = 112
WAITFOR DELAY '00:00:30'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE( )
ROLLBACK TRANSACTION
END CATCH
窗口 2：
UPDATE [TestInmemory] SET CreatedDate = GETDATE() WHERE Id = 112
现在我收到以下报告的错误。但普通表也是如此，第二个窗口正在等待完成第一个窗口事务。如何为内存优化表设置至少相同的行为。
System.Data.SqlClient.SqlException (0x80131904)：当前事务尝试更新自该事务启动以来已更新的记录。交易被中止。该声明已终止。
在 System.Data.SqlClient.SqlCommand.<>c.b__126_0(Task1 result) 在 System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() 在 System.Threading.ExecutionContext.RunInternal( ExecutionContextexecutionContext、ContextCallback回调、对象状态）
I'm trying to update a table which is In-Memory OLTP. There is a scenario where we may have to update a same row in parallel. During concurrent update of a same record I am getting below reported error. Here is my sample update statement.
In SQL Window 1 executing below command at the same time in Window 2 I am executing 2nd update command
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
BEGIN TRY
UPDATE [TestInmemory] SET CreatedDate = GETDATE() WHERE Id = 112
WAITFOR DELAY '00:00:30'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE( )
ROLLBACK TRANSACTION
END CATCH
Window 2:
UPDATE [TestInmemory] SET CreatedDate = GETDATE() WHERE Id = 112
Now I am getting below reported error. But the same is working for normal table, the second window is waiting to complete first window transaction. How do I set at least same behavior for memory optimized table also.
System.Data.SqlClient.SqlException (0x80131904): The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted. The statement has been terminated.
at System.Data.SqlClient.SqlCommand.<>c.b__126_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
发布评论
评论（2）
情况略有不同，但结果我看到了同样的错误。在我将表格重新设计为 MOT 后，这种情况就开始发生。这对我来说看起来很麻烦。如果人们还没有这样做，我们应该写一份重现并告诉微软。
有点悲伤，因为他们对 Hekaton 做了很多工作，这应该是他们解决锁定问题的答案。
Slightly different scenario, but I am seeing this same error as a result. It started to happen right after I reworked the table as MOT. This looks buggy to me. We should write up a repro and tell microsoft, if people haven't done so already.
Kinda sad because they made a huge deal about hekaton and this was supposed to be their answer to locking problems.
我找到了这个问题的答案。长话短说，除非您进行快照隔离，否则不支持显式事务（BEGIN TRAN）。
解决方案一：
更改数据库当前
设置MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON；
可能是最方便的。运行上面的 ALTER DATABASE 将告诉您的数据库一次性自动对所有内存优化内容使用快照隔离。这是一种一次性的、一劳永逸的选项。
解决方案2：
在每个查询中的表名后使用 with (snapshot)。有点像你的做法（nolock）。
从上到下阅读此链接，它解释了一切。
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver16
I found answer to this. Long story short, explicit transaction (BEGIN TRAN) is not supported unless you do snapshot isolation.
Solution 1:
ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
probably most convenient. Running the ALTER DATABASE above will tell your database to automatically use snapshot isolation for all your memory optimized stuff in one shot. This is a one-time, set-it-and-forget-it option.
Solution 2:
use with (snapshot) after table name in every query. kinda like how you do (nolock).
Read this link top to bottom and it explains everything.
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver16