Why your idempotent POST endpoint is still creating duplicates under load

Stop Losing Data: Why Your Idempotent POST Endpoint is Still Creating Duplicates

If you have a .NET API that accepts POST requests, you’ve likely added idempotency keys to handle retries gracefully. It’s a standard pattern: client sends a key, server checks if it’s been seen before, and if not, processes the request.

The common implementation looks something like this:

1. Check Redis/MemoryCache for the key.
2. If it exists, return the cached response.
3. If it doesn’t exist, process the business logic.
4. Save the result to Redis.

This feels safe. But under real-world load, especially with load balancer timeouts or aggressive client-side retries, this pattern creates a race condition that results in duplicate data. In this article, we’ll look at why this happens and how to fix it using SQL Server constraints and proper exception handling in ASP.NET Core.

The "Check-Then-Act" Race Condition

Let’s visualize the failure. Imagine two identical HTTP requests hit your API endpoint POST /orders at 10:00:00.000.

Request A:

1. Checks Redis for key ord-123. Result: Miss.
2. Starts processing business logic (takes 20ms).

Request B:

1. Checks Redis for key ord-123. Result: Miss. (Request A hasn't finished writing to Redis yet).
2. Starts processing business logic.

Request A:

1. Completes logic. Writes order to SQL Server. Writes key to Redis. Returns 201 Created.

Request B:

1. Completes logic. Writes order to SQL Server. Duplicate!

Both requests believe they are the "first" one because the cache check was non-atomic. The cache is a hint, not a guard. If your system relies solely on the cache to prevent duplicates, you are vulnerable to concurrency issues.

Why Caching Isn't Enough

Redis is great for performance, but it doesn't provide strong consistency guarantees for this specific use case in a distributed environment. Even if you use SETNX in Redis, there’s still a window where the business logic is running but the key isn't reserved, or network partitions cause confusion.

For financial transactions, order creation, or any critical data, you need a source of truth that enforces uniqueness at the storage layer. That source of truth is your database.

The Fix: Unique Constraints + Exception Handling

The safest approach is to let the database enforce idempotency. Here’s how to do it in ASP.NET Core with EF Core and SQL Server.

1. Database Schema

Add a unique constraint to the table that stores your idempotency keys. Often, this is part of the main table (e.g., Orders) or a separate lookup table.

-- Assuming a separate table for tracking idempotency
CREATE TABLE IdempotencyKeys (
    Id INT IDENTITY PRIMARY KEY,
    TenantId UNIQUEIDENTIFIER NOT NULL,
    Key VARCHAR(100) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    CONSTRAINT UQ_Tenant_Key UNIQUE (TenantId, Key)
);

Or, if storing it in the main Orders table:

ALTER TABLE Orders
ADD CONSTRAINT UQ_Orders_Tenant_IdempotencyKey UNIQUE (TenantId, IdempotencyKey);

2. Application Logic

In your controller or service, you need to change the flow from "Check Cache -> Create" to "Try Create -> Handle Duplicate".

[HttpPost]
public async Task<IActionResult> CreateOrderAsync([FromBody] CreateOrderRequest request, [Header("X-Idempotency-Key")] string key)
{
    var tenantId = GetCurrentTenantId();

    // 1. Try to insert the key into the DB first (or as part of the transaction)
    // This ensures atomicity.
    try
    {
        // Check cache for fast path (optional optimization, not for safety)
        if (await cache.TryGetValueAsync($"idem:{tenantId}:{key}", out var cachedResponse))
        {
            return Ok(cachedResponse);
        }

        // Begin Transaction
        using var transaction = context.Database.BeginTransaction();

        // Insert Idempotency Record
        var idempotencyRecord = new IdempotencyKey { TenantId = tenantId, Key = key };
        context.IdempotencyKeys.Add(idempotencyRecord);

        // Perform Business Logic
        var order = MapToOrder(request);
        context.Orders.Add(order);

        // Commit
        await context.SaveChangesAsync();
        await transaction.CommitAsync();

        // Set Cache (Best effort)
        await cache.SetAsync($"idem:{tenantId}:{key}", order, TimeSpan.FromHours(24));

        return CreatedAtAction(nameof(GetOrder), new { id = order.Id }, order);
    }
    catch (DbUpdateException ex) when (IsUniqueConstraintViolation(ex))
    {
        // This means another request won the race.
        // The key already exists. We need to fetch the existing result.

        // Since we failed to commit, the idempotency key belongs to the OTHER request.
        // We must find the data created by that other request.
        var existingOrder = await context.Orders
            .FirstOrDefaultAsync(o => o.TenantId == tenantId && o.IdempotencyKey == key);

        if (existingOrder != null)
        {
            return Ok(existingOrder); // Return 200 or 201? Usually 200 since it's a replay.
        }

        // If the key exists but the order doesn't, it might be still in-flight by the other request.
        // In strict systems, you might need to wait/retry.
        throw new ConcurrentProcessingException("Request is already being processed.");
    }
}

private bool IsUniqueConstraintViolation(DbUpdateException ex)
{
    return ex.InnerException?.Message.Contains("UNIQUE") == true;
}

3. Handling the "In-Flight" Edge Case

The logic above handles the case where the other request has completed. What if the other request is still running?

If you use a separate IdempotencyKeys table, you can insert a "pending" key. When the other request finishes, it updates the key to "completed". If a duplicate arrives, it sees "pending" and can either:

1. Fail immediately with 409 Conflict (Recommended for simple systems).
2. Poll/Wait until the other request completes (Complex, requires locking).

For most .NET applications, returning a 409 Conflict or retrying the entire operation on the client side is sufficient if the DB commit is fast.

Practical Takeaway

Idempotency is not just a caching strategy; it’s a concurrency control mechanism.

1. Don't trust the cache alone. It’s a hint, not a lock.
2. Enforce uniqueness in the database. Use a unique constraint on (TenantId, IdempotencyKey).
3. Handle DbUpdateException gracefully. If the constraint fails, you know someone else got there first. Fetch their result and return it.
4. Keep transactions short. The window between inserting the key and committing the data should be as small as possible to minimize contention.

By making the "check and create" atomic at the database level, you ensure that no matter how many load balancers, retries, or concurrent clients you have, you’ll never create duplicates. Your users will appreciate the consistency, and your operations team will sleep better knowing your data integrity is enforced by the engine, not just your application code.