EF6处理高并发插入操作时出现异常问题求助

Hey there, let's tackle this EF6 high-concurrency insert issue you're facing with SQL Server for your log records. Since your workload is write-heavy, read-only after insert, and runs in isolated threads where batching isn't feasible, here are actionable fixes tailored to your scenario:

1. Implement a Retry Strategy for Transient Failures #

The error explicitly points to transient failures—these are temporary blips like connection timeouts, lock contention, or resource spikes that can be resolved by retrying the operation. Even though the message mentions SqlAzureExecutionStrategy, it works great for on-prem SQL Server too, or you can build a custom one:

Option A: Use the Azure Execution Strategy (Simplest Fix) #

Register it with your DbContext to automatically retry on common transient errors:

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
    }
}

// Attach the configuration to your DbContext
[DbConfigurationType(typeof(MyDbConfiguration))]
public class MyDbContext : DbContext
{
    // Your context setup here
}

Option B: Build a Custom Retry Strategy #

If you want fine-grained control over which errors to retry, create a custom strategy:

public class SqlServerRetryStrategy : DbExecutionStrategy
{
    public SqlServerRetryStrategy() : base(maxRetryCount: 3, maxDelay: TimeSpan.FromSeconds(2)) { }

    protected override bool ShouldRetryOn(Exception ex)
    {
        if (ex is SqlException sqlEx)
        {
            // Retry on common transient SQL Server errors
            return sqlEx.Number switch
            {
                1205 => true, // Deadlock victim
                -2 => true, // Timeout
                4060 => true, // Cannot open database
                40197 => true, // Service error
                _ => false
            };
        }
        return ex is TimeoutException;
    }
}

Register it the same way as the Azure strategy above.

2. Optimize DbContext for Write-Only Workloads #

Since your logs are never modified, strip out all unnecessary EF overhead to reduce contention:

• Disable proxy creation (no need for change-tracking proxies):

  public class MyDbContext : DbContext
  {
      public MyDbContext()
      {
          this.Configuration.ProxyCreationEnabled = false;
          this.Configuration.AutoDetectChangesEnabled = false; // You already did this—great call!
      }
  }
  

• Stick to short-lived contexts: Your current using pattern is perfect. Each thread should have its own isolated context to avoid cross-thread issues and minimize change-tracking overhead.

3. Bypass EF Change Tracking with Direct SQL Inserts #

EF's change tracker adds unnecessary overhead for single, write-only inserts. Skip it entirely by executing raw SQL directly—this is faster and reduces the chance of transient issues:
Modify your repository to use ExecuteSqlCommand instead of EF's Add/SaveChanges:

public void AddLog(LogData log)
{
    string insertSql = @"INSERT INTO Logs (EventName, EventTime, Details) 
                         VALUES (@EventName, @EventTime, @Details)";
    _context.Database.ExecuteSqlCommand(insertSql,
        new SqlParameter("@EventName", log.EventName),
        new SqlParameter("@EventTime", log.EventTime),
        new SqlParameter("@Details", log.Details));
}

This cuts out EF's object tracking pipeline entirely, making each insert lighter and more resilient under high concurrency.

4. Database-Level Tweaks for Fast Inserts #

Optimize your log table schema to handle high-volume writes:

• Use a Heap Table: Remove the clustered index if you don't need to query logs in strict order (or add a non-clustered index for queries if needed). Heap tables have faster insert performance because they don't require maintaining index order.
• Lower Isolation Level: For log inserts, you don't need strong consistency. Use ReadUncommitted or Snapshot isolation to reduce lock waits:

  using (var transaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
  {
      try
      {
          repo.Add(logData);
          repo.Complete();
          transaction.Commit();
      }
      catch
      {
          transaction.Rollback();
          throw;
      }
  }
  

• Disable Auto-Statistics: If you rarely query the logs, turn off auto-update statistics on the table to avoid lock contention during statistics updates.

5. Adjust Connection Pool Settings #

High concurrency can exhaust your SQL Server connection pool. Tweak your connection string to increase the pool size (default is 100):

Server=yourServer;Database=yourLogDB;User Id=yourUser;Password=yourPass;Max Pool Size=200;

Ensure connections are properly disposed (your using pattern already handles this) to prevent pool exhaustion.

Content of the question comes from Stack Exchange, asked by Xi Duan