2

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)

Dale K
  • 25,246
  • 15
  • 42
  • 71
Guna
  • 113
  • 3
  • 9

2 Answers2

0

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.

Jun Sato
  • 111
  • 2
0

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

Jun Sato
  • 111
  • 2
  • MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT would allow explicit transactions, and joining in the same query in memory and on disk tables but it does not the solve the simultaneous update issue. I had this error on update, even without explicit/implicit transactions & enabled memory optimized snapshot. On the site you linked it describes this issue under the 41302 error code. There is no workaround for concurrent update on the same row, YET – S4V1N Jun 09 '23 at 13:26