The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted

Viewed 424

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)

0 Answers
Related