Memory Optimized table update failure

  • 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

  • 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?

  • 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.

  • 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.

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

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