Such concurrent violation of foreign key constraints is rare, and typically indicates an issue with the application logic or with data entry.
However, the error can also occur if there is no index on the columns involved with the FOREIGN KEY constraint.
SQL Server has the following modes for transaction initiation: Therefore it is necessary to have a table hint on the memory-optimized table.
The hint must be for SNAPSHOT or an even more isolating level. Customer Id; COMMIT TRANSACTION; Memory-optimized tables use a highly sophisticated row versioning system that makes the optimistic approach efficient, even at the most strict isolation level of SERIALIZABLE.
One of the two transactions receives this error message and will need to be retried.
This error can occur when using REPEATABLE READ or SERIALIZABLE isolation, and also if the actions of a concurrent transaction cause violation of a FOREIGN KEY constraint.
Supported for memory-optimized tables only when the autocommit mode is in effect.
This article describes all the aspects of transactions that are specific to memory-optimized tables and natively compiled stored procedures.
The transaction isolation levels in SQL Server apply differently to memory-optimized tables versus disk-based tables, and the underlying mechanisms are different.
The functional differences are due to pessimistic versus optimistic approaches to transaction integrity.
Memory-optimized tables use the optimistic approach: The optimistic approach is less overhead and is usually more efficient, partly because transaction conflicts are uncommon in most applications.