Preventing Partial Updates When a Batch Database Operation Fails

Batch database operations are useful when an application needs to insert, update, or delete many records in one request. Instead of sending a separate database command for every record, the application can group related changes into a single operation and reduce network overhead.

However, batch processing introduces an important reliability problem. What happens when the database successfully updates some records but fails before completing the rest?

Imagine an order management system updating 100 inventory records after a warehouse shipment arrives. The first 60 updates succeed, but the remaining updates fail because one record violates a database constraint. If the application does not handle the operation correctly, the inventory may reflect only part of the shipment.

These inconsistencies can be difficult to detect because the application may report an error even though some changes have already been committed.

Database transactions help prevent partial updates by making a group of related operations succeed or fail as a unit. In this article, we will examine how partial updates happen, how to handle them in C# with SQL Server and Entity Framework Core, and when alternative strategies are more appropriate.

Why Batch Operations Can Leave Inconsistent Data

Consider an application that transfers money between two accounts. The application needs to subtract money from the source account and add it to the destination account.

A simplified implementation might look like this:

public async Task TransferAsync(
    int sourceAccountId,
    int destinationAccountId,
    decimal amount)
{
    await _repository.DebitAsync(
        sourceAccountId, amount);

    await _repository.CreditAsync(
        destinationAccountId, amount);
}

The code appears straightforward, but the two database operations may run in separate transactions. If the debit succeeds and the credit fails, money is removed from one account without being added to the other.

The same problem can occur during bulk imports, inventory adjustments, payroll processing, and order fulfillment.

Common causes include:

• A database constraint rejects one record.
• A connection fails while the batch is running.
• A command times out after some operations have completed.
• Application code commits changes before all related operations finish.
• Multiple database calls use separate transactions.
• An error occurs after the database has committed but before the application receives confirmation.

The last case deserves particular attention. A network failure does not always mean the database rolled back the operation. The database may have committed successfully while the client lost the response. Retrying blindly can therefore introduce duplicate changes.

Use Database Transactions for Atomicity

A transaction groups related database operations into a single unit of work. When the transaction commits, all its changes become durable. When it rolls back, its uncommitted changes are discarded.

This property is known as atomicity, the first property in the ACID transaction model.

For SQL Server, the basic pattern is:

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE Accounts
    SET Balance = Balance - 100.00
    WHERE AccountId = 1;

    UPDATE Accounts
    SET Balance = Balance + 100.00
    WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

Both updates execute inside the same transaction. If an error occurs before the commit, the catch block rolls back the transaction and rethrows the error so the caller can handle it.

This example demonstrates transaction boundaries, but a real money transfer must also validate the amount, confirm that both accounts exist, prevent an invalid negative balance, and verify that each update affected the expected number of rows.

For example, an update that affects zero rows is not necessarily a database exception. If the source account does not exist, the debit statement may complete without throwing an error. Application logic must check affected-row counts or use stronger database constraints to enforce its business rules.

Prevent Partial Updates with Entity Framework Core

Entity Framework Core normally wraps the changes from a single SaveChangesAsync call in a transaction when the database provider supports transactions.

For many applications, this means multiple tracked entity changes can be saved atomically without manually creating a transaction.

Consider an inventory adjustment that updates several products:

public async Task UpdateInventoryAsync(
    IReadOnlyCollection<InventoryAdjustment> adjustments,
    CancellationToken cancellationToken)
{
    foreach (var adjustment in adjustments)
    {
        var product = await _dbContext.Products
            .SingleOrDefaultAsync(
                p => p.Id == adjustment.ProductId,
                cancellationToken);

        if (product is null)
        {
            throw new InvalidOperationException(
                $"Product {adjustment.ProductId} was not found.");
        }

        int newQuantity =
            product.Quantity + adjustment.QuantityChange;

        if (newQuantity < 0)
        {
            throw new InvalidOperationException(
                $"Insufficient inventory for product {product.Id}.");
        }

        product.Quantity = newQuantity;
    }

    await _dbContext.SaveChangesAsync(cancellationToken);
}

The method loads and validates the affected products, updates the tracked entities, and calls SaveChangesAsync once. When that save uses EF Core's normal transactional behavior, a database failure during the save rolls back the changes made by that call.

However, the guarantee has boundaries:

• If the method performs an earlier SaveChangesAsync, those earlier changes may already be committed.
• If another method saves through a different context, it may use a separate transaction.
• External API calls are not automatically rolled back with a database transaction.
• Validation alone cannot prevent concurrent requests from changing inventory after it was read.

For a batch that requires strict consistency, keep the relevant changes within a clearly defined transaction and enforce important business rules at the database boundary.

Use an Explicit Transaction When Multiple Saves Are Required

Sometimes an application must save changes more than once within the same logical operation. In that case, an explicit transaction can keep those database changes together.

public async Task ProcessBatchAsync(
    IReadOnlyCollection<BatchItem> items,
    CancellationToken cancellationToken)
{
    await using var transaction =
        await _dbContext.Database.BeginTransactionAsync(
            cancellationToken);

    try
    {
        foreach (var item in items)
        {
            var entity = new ProcessedItem
            {
                ExternalId = item.ExternalId,
                Status = "Processed"
            };

            _dbContext.ProcessedItems.Add(entity);
        }

        await _dbContext.SaveChangesAsync(cancellationToken);

        await _dbContext.BatchAudits.AddAsync(
            new BatchAudit
            {
                ProcessedCount = items.Count,
                CreatedAtUtc = DateTimeOffset.UtcNow
            },
            cancellationToken);

        await _dbContext.SaveChangesAsync(cancellationToken);

        await transaction.CommitAsync(cancellationToken);
    }
    catch
    {
        await transaction.RollbackAsync(
            cancellationToken);

        throw;
    }
}

The batch records and audit record are saved within the same transaction. If the second save fails before the transaction commits, the first save is also rolled back.

The transaction must be supported by the configured database provider. When retrying execution strategies are enabled, follow EF Core's guidance for executing explicit transactions through the configured strategy. Retrying a transaction also requires care when the client cannot determine whether a previous commit succeeded.

A rollback attempt can itself fail if the connection has already been lost. For this reason, do not assume that every exception guarantees a successful rollback. If the outcome is uncertain, use a durable operation identifier and check the database state before retrying.

Validate the Entire Batch Before Writing

Transactions protect against partial commits, but they do not replace input validation.

If a batch contains 500 records and record 420 has an invalid product identifier, the application may discover the problem only after doing substantial work. A validation pass can identify predictable errors before starting the write operation.

public static IReadOnlyList<string> ValidateBatch(
    IReadOnlyCollection<InventoryAdjustment> adjustments)
{
    var errors = new List<string>();

    foreach (var item in adjustments)
    {
        if (item.ProductId <= 0)
        {
            errors.Add(
                "Every adjustment must have a valid product ID.");
        }

        if (item.QuantityChange == 0)
        {
            errors.Add(
                $"Adjustment for product {item.ProductId} " +
                "must change the quantity.");
        }
    }

    return errors;
}

This is an illustrative validation function. The actual rules depend on the application. For example, some systems may allow zero-quantity adjustments for audit purposes.

Application-level validation is useful for readable error messages, but the database should still enforce essential invariants. Data can change between validation and writing, especially when several workers operate concurrently.

For large batches, validate input in manageable chunks and avoid loading an entire dataset into memory unnecessarily.

Handle Concurrent Updates Correctly

Even when every batch uses a transaction, concurrent updates can still cause problems if the transaction isolation level and write conditions are insufficient.

Suppose two warehouse workers read an inventory quantity of 10. Each processes a request to remove 7 units. Both may validate their requests against the same original value. Without suitable concurrency control, the final result could violate the rule that inventory must not become negative.

One approach is to enforce the condition directly in the SQL update:

UPDATE Products
SET Quantity = Quantity - @Quantity
WHERE ProductId = @ProductId
  AND Quantity >= @Quantity;

The application must check the number of rows affected. If it is zero, the product may not exist or may not have enough inventory.

Another option is optimistic concurrency control using a version column or EF Core concurrency token. When the stored version differs from the version originally read, the update fails and the application can reload the data, revalidate the operation, and decide whether to retry.

Transactions and concurrency control solve related but distinct problems: a transaction groups changes atomically, while concurrency control prevents conflicting operations from silently overwriting one another.

Large Batches Need a Different Strategy

A transaction that modifies a few dozen records may be easy to manage. A transaction that processes millions of records can hold locks for a long time, generate substantial transaction-log activity, and increase contention with other workloads.

For very large imports, consider staging the data before applying it to the target tables.

A staging workflow typically looks like this:

1. Upload the incoming records to a staging table.
2. Validate identifiers, required fields, and business rules.
3. Identify duplicate or conflicting records.
4. Apply the validated changes using set-based SQL operations.
5. Commit the target-table changes in a controlled transaction.
6. Record the outcome and report rejected records.

Staging makes it easier to inspect an import before changing production data. It also separates file parsing and validation from the final database operation.

If the batch is too large for a single transaction, divide it into explicitly defined units of work. Each unit should have a clear completion status, an idempotency key, and a recovery procedure. This changes the business guarantee from all-or-nothing for the entire batch to all-or-nothing for each unit, so the distinction should be documented.

Common Mistakes to Avoid

Best Practices for Production Applications

• Define which records must succeed or fail together before designing the transaction.
• Keep transactions short and avoid user interaction or slow network calls inside them.
• Use database constraints to enforce important invariants.
• Check affected-row counts when updates must target specific records.
• Use optimistic concurrency or suitable isolation levels for conflicting updates.
• Make retryable operations idempotent and track their operation identifiers.
• Log batch IDs, record counts, transaction outcomes, and failure categories without exposing sensitive data.
• Test failures during each stage, including connection loss around commit time.
• Monitor transaction duration, deadlocks, lock waits, and transaction-log growth.
• Use staging and explicit recovery procedures for large imports.

Advantages and Limitations

Advantages

• Transactions prevent related database changes from being partially committed.
• Explicit transaction boundaries make failure handling easier to reason about.
• Validation and database constraints reduce invalid writes.
• Concurrency controls protect shared records from conflicting updates.
• Staging supports safer validation and recovery for large data imports.

Limitations

• Long-running transactions can reduce throughput and increase contention.
• Transactions do not automatically roll back external API calls or messages sent to other systems.
• A lost connection can make the commit outcome uncertain to the client.
• Large batches may require staging or smaller units of work.
• Idempotency and recovery logic still need to be designed explicitly.

Summary

Partial database updates can leave applications in an inconsistent state when related writes run in separate transactions or when failure handling is incomplete.

Use database transactions to group changes that must succeed or fail together. In Entity Framework Core, understand the transactional behavior of SaveChangesAsync and use explicit transactions when several saves belong to one operation. Combine these techniques with validation, database constraints, concurrency control, and idempotent retries.

For large imports, staging tables and bounded batches can provide a more manageable alternative to a single long-running transaction. The right design depends on which changes must remain atomic and how the application will recover when a failure occurs.