sp_Blitz shows "write_conflict" from In-memory OLTP

Question

My team came across a In-Memory OLTP (Hekaton) transaction issue with several of our machines showing that there were a varying number of write conflicts per machine. We do have Memory-Optimized TempDB Metadata turned on.

Since restart: 0 validation failures, 0 dependency failures, 63 write conflicts, 0 unique constraint violations.

and also the Performance message

In-Memory OLTP (Hekaton) In Use, 0% of your 2097152.00GB of your max server memory is being used for in-memory OLTP tables (Hekaton).

Should we be worried about those messages? It looks like the errors are being reported by the sys.dm_xtp_transaction_stats view, and the Microsoft definition of the "write_conflicts" is "Internal use only".

Answer 1

Memory Optimized tables are lock-free, latch-free data structures with optimistic concurrency. Which means that writers don't block writers, and first commit wins. So handling write conflicts is a normal part of working with Memory Optimized tables.

The common pattern is to have a retry loop client code or in an non-native compiled stored procedure that will catch the write conflict error and retry. But it's expected that write conflicts will occasionally occur.

Here it's SQL Server itself using Memory Optimized tables, so presumably the retry is handled properly, and the write conflicts are rare, so it doesn't look like there's any cause for alarm.

If you encounter a very large volume of write conflicts, then the optimistic concurrency model of Memory Optimized tables might not be the right design for the application. For instance you wouldn't want to use a Memory Optimized table where lots of clients need to update the same row in quick succession.