When In-Memory OLTP first came out, Microsoft liked to go around bragging at conferences that “Nothing stops a Hekaton transaction.” I remember hearing that and kinda chuckling at the time, and I was reminded of it by a recent client who was running into blocking issues with…yep, Hekaton.
To reproduce it:
- Do a large insert into an in-memory OLTP table
- In another window, try to truncate that table (which gets blocked by query #1)
- In another window, try to another insert into that table (which gets blocked by query #2)
The results of sp_WhoIsActive show blocking:
The reason is rather amusing: In-Memory OLTP tables don’t actually support TRUNCATE TABLE. If you try it, you hit an error:
|
1 2 |
Msg 10794, Level 16, State 96, Line 1 The statement 'TRUNCATE TABLE' is not supported with memory optimized tables. |
But SQL Server doesn’t check for that until after the TRUNCATE TABLE command gets a schema modification lock. In order to get that, the first query has to finish – and until he does, query #3 can’t run. Presto, good ol’ blocking. (I can’t call that a SQL Server bug with a straight face, either.)
The solutions:
- When your code throws errors, don’t swallow ’em. Log them somewhere, and actively track them down. In this case, the TRUNCATE TABLE was a leftover from before the table was migrated to In-Memory OLTP.
- Before you migrate, read the T-SQL limitations of In-Memory OLTP and unsupported features.
- If you need to quickly delete the contents of an entire In-Memory OLTP table, don’t bother trying swapping back and forth between different In-Memory OLTP tables for staging and production, either. sp_rename is supported as of SQL Server 2017, but it faces the same schema mod lock problem as TRUNCATE TABLE.
Shout out to the client manager who said, “You have to blog about that to show that we were ambitious but rubbish.” Cheers, Tiny Irish: y’all were smart enough to hire me, so there’s always that.
2 Comments. Leave new
I’ve caused deadlocks in memory optimized tables. Had inserts, updates and deletes running from different SPIDs on the same rows in a high-concurrency environment and got deadlocks for multiple transactions on rows already being written. It was because I left off the SPID part of a Where clause on a “memory optimized temp table” on accident. When I fixed that, the error went away. But it did stop the transactions.
Not impressed by Hekaton optimistic locking.
It is like me jumping off cliff and being optimistic I will sprout wings in time.
Having updlock so we don’t need to retry a task 200 times would be very useful.