Stopping Duplicate API Requests With an Idempotency Key Table

A timeout leaves a caller unsure whether its order was accepted. An idempotency key lets a retry ask for the same result instead of creating another order.

Give the Retry a Stable Identity

A retry is another attempt at the same business request. It must carry the same key as the first attempt. Generating a new key inside the server for every call cannot identify that relationship.

Have the client choose a request key before sending the operation. Keep it through transport failures and repeated attempts. A later, genuinely different order receives a new key, even when its product and quantity match.

I separate request identity from order identity when reviewing this design. An order number describes the completed business record. The request key describes the caller's intent before the server knows whether it will complete.

Use a unique key in SQL Server to arbitrate competing calls. An application check followed by an unprotected insert leaves a concurrency gap. Two sessions can both see no prior result before either session writes it.

The example requires SQL Server 2012 or later because it uses THROW. Run it in a disposable database. Its procedure owns the transaction and deliberately rejects an existing outer transaction.

Store Enough Information to Validate Key Reuse

Save the request's meaningful inputs beside the result. A caller reusing one key for different quantities must receive an error, not an unrelated old order. Larger requests need a defined payload-comparison strategy.

CREATE TABLE dbo.RequestOrders
(
    OrderID int IDENTITY PRIMARY KEY,
    ProductID int NOT NULL,
    Quantity int NOT NULL CHECK (Quantity > 0)
);
CREATE TABLE dbo.OrderRequestKeys
(
    RequestKey nvarchar(100) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY,
    ProductID int NOT NULL,
    Quantity int NOT NULL,
    OrderID int NULL REFERENCES dbo.RequestOrders(OrderID),
    CreatedAt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME()
);

The binary collation makes the stored key comparison explicit. Decide whether keys are case-sensitive in your API contract. Avoid allowing a database's inherited collation to surprise a caller using mixed-case identifiers.

OrderID starts null while the transaction builds its result. The key and result commit together, so another ordinary read does not see an unfinished committed request. Do not use dirty reads when retrieving a prior result.

For several clients or tenants, scope uniqueness to the intended owner. A composite key containing tenant identity and request key prevents unrelated clients from colliding. Authorization still needs to protect the returned order.

Commit the Idempotency Key and Order Together

The unique request-key insert happens first. A competing insert waits for the first transaction's outcome. If the first transaction commits, the competing insert raises a duplicate-key error that the procedure can interpret.

GO
CREATE PROCEDURE dbo.PlaceOrderOnce
    @RequestKey nvarchar(100), @ProductID int, @Quantity int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    IF @@TRANCOUNT <> 0
        THROW 50001, 'Call this procedure without an outer transaction.', 1;
    IF @RequestKey IS NULL OR @RequestKey = N'' OR @Quantity IS NULL OR @Quantity <= 0 OR @ProductID IS NULL
        THROW 50002, 'Provide a request key and a positive quantity.', 1;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.OrderRequestKeys(RequestKey, ProductID, Quantity)
        VALUES (@RequestKey, @ProductID, @Quantity);
        INSERT dbo.RequestOrders(ProductID, Quantity)
        VALUES (@ProductID, @Quantity);
        DECLARE @OrderID int = CONVERT(int, SCOPE_IDENTITY());
        UPDATE dbo.OrderRequestKeys
        SET OrderID = @OrderID WHERE RequestKey = @RequestKey;
        COMMIT TRANSACTION;
        SELECT @OrderID AS OrderID, CAST(0 AS bit) AS WasReplay;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber int = ERROR_NUMBER();
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        DECLARE @SavedOrderID int, @SavedProductID int, @SavedQuantity int;
        IF @ErrorNumber = 2627
        BEGIN
            SELECT @SavedOrderID = OrderID, @SavedProductID = ProductID,
                   @SavedQuantity = Quantity
            FROM dbo.OrderRequestKeys WHERE RequestKey = @RequestKey;
            IF @SavedOrderID IS NOT NULL AND @SavedProductID = @ProductID
               AND @SavedQuantity = @Quantity
            BEGIN
                SELECT @SavedOrderID AS OrderID, CAST(1 AS bit) AS WasReplay;
                RETURN;
            END;
        END;
        THROW;
    END CATCH;
END;
GO

The catch rolls back before reading the committed result. With XACT_ABORT enabled, a failed write can leave the transaction uncommittable. Continuing to write inside that failed transaction would produce another error rather than a useful replay.

Only the matching duplicate request is converted into a replay. Other errors are thrown. A future unique constraint on the order table must not turn an unrelated failure into a successful-looking response.

Test Repeated Calls With the Same Idempotency Key

Run the same call twice and inspect the returned order identity. Then inspect the tables. These queries supply evidence from your own execution without assuming a measured row count in advance.

EXEC dbo.PlaceOrderOnce N'request-example-a', 7, 2;
EXEC dbo.PlaceOrderOnce N'request-example-a', 7, 2;
SELECT RequestKey, ProductID, Quantity, OrderID, CreatedAt
FROM dbo.OrderRequestKeys WHERE RequestKey = N'request-example-a';
SELECT o.OrderID, o.ProductID, o.Quantity
FROM dbo.RequestOrders AS o
JOIN dbo.OrderRequestKeys AS k ON k.OrderID = o.OrderID
WHERE k.RequestKey = N'request-example-a';

On my test server, the first call returned order 1 with WasReplay 0. The second call returned order 1 with WasReplay 1, and both tables held one row.

Use another test call with the same key and a different quantity. It should fail because the existing payload does not match. Keep that behavior in the client contract rather than silently choosing the first or latest quantity. In my test, that call ended with the original duplicate-key error 2627.

For a concurrency test, run identical calls from two query windows. Check that both successful responses refer to the same committed order. Also test a failed first attempt, so you know whether a later retry can create the intended order.

What should the caller do after a response is lost? Retry the same request with the same key. Searching for a similar order by product and time cannot reliably identify the original intent.

Bound the Lifetime Without Forgetting the Contract

Retaining every request key indefinitely grows the table. Define a retention period that exceeds the permitted retry window. A scheduled Agent job can remove eligible old key records in small batches.

DECLARE @Cutoff datetime2(7) = DATEADD(day, -30, SYSUTCDATETIME());
SELECT COUNT_BIG(*) AS EligibleKeyTotal
FROM dbo.OrderRequestKeys
WHERE CreatedAt < @Cutoff;

-- Run only under the approved key-expiry policy.
DELETE TOP (1000) FROM dbo.OrderRequestKeys
WHERE CreatedAt < @Cutoff;

The thirty-day period is an example policy, not a recommended universal value. Once a key is removed, a later call with that key can create another order. Expiry therefore changes the guarantee offered to callers.

Add an index on CreatedAt when the cleanup workload justifies it. Review its write cost and keep cleanup transactions small. Decide how retained order records relate to expired key records before implementing deletion.

Keep External Effects Outside the Illusion

I check the operation's side effects before calling it idempotent. The database transaction covers these tables, but it does not undo an email or an external charge already sent elsewhere. Those effects need their own coordinated design.

Persist work for external delivery in the same transaction when using a durable queue pattern. Let a separate worker process it under a defined retry contract. Replaying an order response should not resend unrelated effects accidentally.

Return the original business result consistently. For a larger API response, store the response information required to reproduce it. Keep payload validation, authorization, expiry, and error handling together with the request-key rule.

Make key validation part of the API boundary. The sample stores at most one hundred Unicode code units, so reject oversized keys before binding the procedure parameter. Silent shortening can turn two different client keys into the same stored value. Define permitted characters and comparison rules before distributing the contract to callers.

The replay branch captures the stored values once before checking them. This avoids checking for a result and then performing a separate read whose row disappears during concurrent retention cleanup. Cleanup still needs coordination with the supported retry window, including requests arriving near its expiry boundary.

Retries are enthusiastic clerks. They need a way to recognize paperwork already accepted. Keep the replay indicator in diagnostics so you can distinguish repeated delivery from newly created work without labeling a replay as a failure.

Test a crash after commit but before the caller receives its response. That is the case this design exists to handle. Also test a crash before commit, when the key and order must both roll back. Record those outcomes with the procedure version before relying on the guarantee in an application.

An idempotency key needs a defined retention period as well as a uniqueness constraint. Keep the idempotency key stable across a retry so the server can identify the original business operation.

Related reading on this blog: Connection Retry Logic for Cloud Databases and Unique Indexes on Nullable Columns.

An idempotency key is not a second order number, it is a promise that one request keeps one committed result.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.