.net - SQL Server 2014 In-Memory previous transaction aborted exception -

i'm working on large scale project in production. have big process changed use in-memory tables sql 2014 performance efficiency.

the process uses:

• 51 in-memory sql tables.
• 50 stored procedures loads data(insert) 150 regular sql tables.
• 300 validations (short stored procedure) selecting 50 in-memory tables (and insert in-memory table save validation errors if exists).

we calling process ado.net, loads stored procedure first , validations, each sp use different sql connection. in normal use, works fine , takes 1.5 second.

under stress test (6 clients x 100 tasks) 30 minutes. after several minutes starting sql exception (1 sql exception every 20 tasks):

a previous transaction current transaction took dependency on has aborted,  , current transaction can no longer commit. 

transactions in memory-optimized tables

the exception not clear. not using begin transaction in process. sql exception occurs in different stored procedures each time.

after days of investigation stuck , have no ideas more. asking undersand can cause exception , how deal it.

there not enough info track down problem, can try trying explain error means , how happen.

first, know already, when memory optimized table (mot) participates in transaction, kind of optimistic concurrency control, avoid locks , block waiting on locks. instead, when kind of concurrency conflict detected, 1 of conflicting transactions doomed , rolled back.

each row of mot has several timestamps assigned define if transaction can see row or not.

for transactions access mot, special validation phase performed before commit. whole transaction consists of 3 phases - regular, validation , commit.

during regular phase, writes tables made transaction not visible other transactions, except deletes , updates visible other deletes , updates, , if 1 transaction writes same row other, write-write conflict occurs , 1 transaction doomed immediatly.

now interesting problem validation phase. here, transaction validates things repeatable read or serialized isolation levels violated. suppose transaction runs under repeatable read, read row @ beginning of transaction, , in validation phase sees same row updated transaction (remember writes of other transactions not visible unless write same row, here read). transaction doomed here , rolled back.

now, important thing when validation phase begins, writes made transaction (let's name transaction a, in validation phase) become visible transactions. note not commited yet. if transaction (b) reads such data (written transaction in validation phase not yet commited), gets dependency on a. means should commited , after b commited. if reason transaction fails in validation phase, transaction b doomed too, exception have in question.

now remember if don't explicitly begin transactions, each statement executed inside transaction anyway. might think simple statements cannot cause such problems, there statements merge internally might execute several read , write operations, , performed inside transaction.

an example how error might occur (this give ideas):

1. statement performs merge statement on table
2. it enters validation phase.
3. statement b performs merge , reads data written a.
4. we run under serializable isolation level, , in validation phase notices phantom row inserted other statement c. serializable level violated , rolled back.
5. b has dependency on , rolled exception.

hope information find root of problem.

what can track issue set isolation level snapshot. validation step not performed understand , error should not appear more.