SQL Server 2014 Fixed error with previous transaction

I am working on a large-scale project that is currently working. We have a great process that has recently changed usage In-Memory Tablesfrom SQL 2014 to improve efficiency.

The process uses:

  • 51 SQL tables with memory.
  • 50 stored procedures that load data (Insert) from approximately 150 standard SQL tables.
  • 300 Checks (short stored procedure) Select from these 50 tables with memory (and insert into the "Internal memory" table, which saves check errors, if any).

We call this process from ADO.Net, first load the stored procedure, and then check that each SP uses a different one SQL Connection. Under normal use, everything works fine and takes about 1.5 seconds.

In stress tests (6 Clients X 100 Tasks) for 30 minutes. After a few minutes, we start getting this SQL Exception(1 SQL Exception for every 20 tasks):

A previous transaction that the current transaction took a dependency on has aborted, 
and the current transaction can no longer commit.

Transactions in memory optimized tables

The exception is not clear. We do not use BEGIN TRANSACTIONin this process. SQL Exceptionhappens in different stored procedures every time.

After days of investigation, we are stuck and we have no more ideas. Asking for your help to explain what this exception might cause and how to deal with it.

+4
2

, , , , .

-, , , , (MOT) , concurrency, . , - concurrency, .

MOT , , .

, MOT, . , : , .

, , , , , , , .

. , REPEATABLE READ SERIALIZED . , REPEATABLE READ, , , ​​ (, , , ). .

, , , ( A, ) . , . (B) ( , , ), A. , A , B . - A , B , .

, , . , , , MERGE, , .

, ( , ):

  • A MERGE
  • .
  • B MERGE , A.
  • SERIALIZABLE , , phantom ​​ - C. SERIALIZABLE .
  • B A .

, .

, , SNAPSHOT. , , .

+4

In-Memory COMMIT DEPENDENCIES, .

, - VALIDATION COMMIT PHASE. , , YET : 41301 ( " , ..." )

COMMIT , , , . " (*)" .

0

Source: https://habr.com/ru/post/1608318/


All Articles