EF Core中遍历关联表执行更新的‘Connection is busy’错误修复及大数据量优化方案咨询

1. Fixing the "Connection is busy" Exception #

The root cause of your original error is that you're enumerating an unmaterialized IQueryable (from db.Artist.Include(x => x.Songs)) while using the same DbContext to run SaveChanges(). When you iterate over an unmaterialized query, EF Core keeps the database connection open to stream results—trying to execute write operations on that same busy connection triggers the exception.

Here are two valid fixes:

• Materialize the query first: Load all artists into memory upfront with .ToList() (works for small datasets, but not feasible for millions of records):

  var artists = db.Artist.Include(x => x.Songs).ToList();
  foreach(var artist in artists) 
  {
      // Your existing processing logic
  }
  

• Use isolated per-batch DbContext instances: This is exactly what you implemented in your updated code! By wrapping each batch in a using (var db = new Context()) block, you ensure each batch uses a fresh connection and context, eliminating the connection conflict.

2. Efficiency for Large Datasets (Millions of Artists/Thousands of Songs) #

Your initial single-loop approach is not efficient for large-scale data. Here’s why:

• SaveChanges() processes tracked entities one by one (or in small batches), leading to thousands of unnecessary database round-trips.
• Tracking millions of entities in a single DbContext would cause massive memory bloat and cripple performance.

Your batch processing approach is a strong foundation, but we can refine it to handle scale better.

Review & Optimizations for Your Batch Processing Code #

Your current batch implementation is reasonable, but here are key improvements to make it more robust and efficient:

1. Replace Max ID Check with Empty Batch Termination #

Relying on maxId can fail if Artist IDs are non-continuous (e.g., deleted records) or if new records are added during processing. Instead, exit the loop when you fetch an empty batch:

int batchSize = 50;
int offset = 0;
while (true) 
{
    using (var db = new Context()) 
    {
        var artists = db.Artists
            .OrderBy(x => x.Id)
            .Skip(offset)
            .Take(batchSize)
            .Include(x => x.Songs)
            .ToList();

        // Exit loop when no more artists are found
        if (!artists.Any())
            break;

        var ids = string.Join(", ", artists.Select(x => x.Id));
        Console.WriteLine($"Working on batch starting at offset {offset}, ids: {ids}");

        foreach (var artist in artists) 
        {
            var fileData = LoadArtistFile(artist.DataFilePath);
            UpdateArtist(artist, fileData);
        }

        db.SaveChanges();
        offset += batchSize;
    }
}

2. Optimize Song Updates/Inserts #

Instead of modifying individual Song entities and letting SaveChanges() generate dozens of SQL statements, use EF Core’s bulk operations to cut down on database round-trips:

• Bulk Inserts: Use AddRange() for new songs instead of Add() to insert multiple records in one go.
• Bulk Updates: Use ExecuteUpdate() to modify existing songs without loading all of them into memory (far faster for large datasets):

  // Inside UpdateArtist()
  var existingSongIds = artist.Songs.Select(s => s.Id).ToList();
  var songsToUpdate = fileData.Songs.Where(s => existingSongIds.Contains(s.Id)).ToList();
  
  foreach (var song in songsToUpdate)
  {
      db.Songs
          .Where(s => s.Id == song.Id && s.ArtistId == artist.Id)
          .ExecuteUpdate(setters => setters
              .SetProperty(s => s.Title, song.Title)
              .SetProperty(s => s.Duration, song.Duration)
              // Add other properties to update
          );
  }
  
  // Bulk insert new songs
  var newSongs = fileData.Songs.Where(s => !existingSongIds.Contains(s.Id))
      .Select(s => new Song { ArtistId = artist.Id, Title = s.Title, Duration = s.Duration })
      .ToList();
  db.Songs.AddRange(newSongs);
  

3. Add Transaction Support for Batch Atomicity #

Wrap each batch in a transaction to ensure partial updates don’t happen if one artist in the batch fails. This prevents data inconsistency:

using (var db = new Context())
using (var transaction = db.Database.BeginTransaction())
{
    try
    {
        var artists = db.Artists
            .OrderBy(x => x.Id)
            .Skip(offset)
            .Take(batchSize)
            .Include(x => x.Songs)
            .ToList();

        if (!artists.Any())
            break;

        // Process artists...

        db.SaveChanges();
        transaction.Commit();
        Console.WriteLine($"Batch at offset {offset} completed successfully");
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, $"Failed to process batch at offset {offset}");
        transaction.Rollback();
        // Optional: Add retry logic for transient errors like database timeouts
    }
}

4. Adjust Batch Size Based on Memory Constraints #

You noted that 50 is optimal, but keep an eye on memory usage. If each artist has hundreds of songs, you might need to reduce the batch size to avoid out-of-memory exceptions. Conversely, if artists have few songs, you could increase it to cut down on context initialization overhead.

内容的提问来源于stack exchange，提问作者lifebythedrop