locked
Read past lock in the Memory Optimized Table RRS feed

  • Question

  • Hi,

    I have a big problem in working with table in memory in SQL 2014.

    i know that there is no readpast lock in SQL. but in some scenarios it can cause decrease in performance.

    suppose that there are 20 records in one table. Eech record has one column 'LockStatus' which its initial value is 'Wait'.

    Now if two consumer want to pick top(10) of records, what happens?

    consumer one gets first 10 records  and changes its status to 'Locked' and while it is using them, the second consumer tries to pick top(10) but it will be aborted:

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

    by readpast  lock we could say to the consumer 2 to pick second 10 records, but here it is aborted.

    it will be your kindness if help me.

    Wednesday, January 6, 2016 10:39 AM

Answers

  • Well, I did get two ideas:

    Run with isolation level REPEATABLE READ (so that you are not disturbed by INSERTs) trap the error with TRY-CATCH and retry.

    Use application locks and serialise access to the table. (Not possible in a natively-compiled procedure, though.)

    I doubt that any of them would perform better than a disk-based table with READPAST, though.

    • Proposed as answer by pituachMVP Thursday, January 7, 2016 12:31 AM
    • Marked as answer by Eric__Zhang Thursday, January 21, 2016 11:41 AM
    Wednesday, January 6, 2016 10:35 PM

All replies

  • So why did you make this an in-memory table in the first place?

    Keep in mind that In-Memory OLTP is built on optimistic concurrency and does not use lock and latches. But you have a queue table, and such a table requires pessimistic concurrency, or so I think. I will have to admit that I don't know for sure, and I will consult my MVP mates. But unless you hear anything more, assume that this is a lost cause, and you want to make this table disk-based.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 6, 2016 8:31 PM
  • Well, I did get two ideas:

    Run with isolation level REPEATABLE READ (so that you are not disturbed by INSERTs) trap the error with TRY-CATCH and retry.

    Use application locks and serialise access to the table. (Not possible in a natively-compiled procedure, though.)

    I doubt that any of them would perform better than a disk-based table with READPAST, though.

    • Proposed as answer by pituachMVP Thursday, January 7, 2016 12:31 AM
    • Marked as answer by Eric__Zhang Thursday, January 21, 2016 11:41 AM
    Wednesday, January 6, 2016 10:35 PM