Memory Optimized table update failure

Question

Memory Optimized table update failure

PHXHoward

SSCrazy Eights

Points: 8389
More actions
May 31, 2017 at 2:27 pm

#343034

Hello, we have migrated a database to SQL Server 2016 and created a memory optimized table.
The Java application is attempting to do an update of the memory optimized table and failing. This error message is what we get:
The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
The query looks like this:
set transaction isolation level repeatable read
update MEMOPTTABLE_test set LAST_MODIFIED_BY = 'abc' where transaction_id = 4532432
The MSDN article says that repeatable read is supported in mem optimized tables yet it fails.
Thanks for any suggestions,
Howard

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 1

I take it that the error message is trying to say this:

"Repeatable Read transactions must access memory optimized tables and natively compiled modules under snapshot isolation."

Does anyone know what that means?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Yes, you must specify WITH (SNAPSHOT) on the table to use that isolation level with a memory optimized table.

So:

set transaction isolation level repeatable read

update MEMOPTTABLE_test WITH (SNAPSHOT) set LAST_MODIFIED_BY = 'abc' where transaction_id = 4532432

Cheers!

EDIT: Sorry, completely misread that post; clearly not enough coffee 🙂

Specify REPEATABLEREAD as the table hint if that's the behavior you want.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

Thanks a lot for replying Jacob. That did work.

It means that the developer will need to change the query or else remove the repeatable read requirement. More work than I had hoped but very good to know.