SQL Server: Why Retrying an INSERT Can Create Duplicate Rows

Retrying an INSERT can create duplicate rows when SQL Server treats each attempt as a new request. An identity column gives every row a different number. It cannot tell whether two inserts represent the same order.

The missing reply creates uncertainty

Imagine an application submits an order. SQL Server commits it, but the connection fails before the application receives confirmation. The application sees an error and sends the order again.

That sequence can leave two committed orders. A client error alone does not establish whether the original transaction committed. Record the request identity before deciding what to repeat.

This does not mean every timeout commits a write. A failure before execution, an explicit rollback, and a lost acknowledgment are different cases. I want the retry design to handle uncertainty without guessing which case occurred.

An identity column permits the duplicate

Run this example in a fresh SSMS query window using autocommit mode. It uses a temporary table and removes it afterward. The two statements represent the original attempt and its retry:

SET NOCOUNT ON;
CREATE TABLE #OrdersWithoutRequestKey
(
    OrderId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
-- First request succeeds.
INSERT INTO #OrdersWithoutRequestKey (CustomerId, Amount)
VALUES (42, 125.00);
-- The application retries the same business request.
INSERT INTO #OrdersWithoutRequestKey (CustomerId, Amount)
VALUES (42, 125.00);
SELECT OrderId, CustomerId, Amount
FROM #OrdersWithoutRequestKey
ORDER BY OrderId;
DROP TABLE #OrdersWithoutRequestKey;

The result contains order IDs 1 and 2, both for customer 42 and amount 125.00. The primary key accepts both rows because their IDs differ. SQL Server has no rule saying these attempts belong to one request.

This example deliberately repeats the insert. It demonstrates the data outcome, without creating a network failure or reproducing a client timeout. The screenshot shows the actual SSMS result from that script.

Give the request a stable identity

Create a request ID before sending the first attempt. Keep it with the operation, and send that same ID on every retry. A genuinely new order gets a new request ID.

Generating a new GUID inside every attempt defeats this design. Both attempts then look like new requests. The application must retain the original ID across reconnects and whatever recovery period it supports.

Do not make customer and amount unique instead. A customer can legitimately place two separate orders for the same amount. The request ID distinguishes a repeated attempt from another valid order.

The database must enforce the rule with a unique constraint. A separate existence check followed by an insert leaves a concurrency gap. Two sessions can both check before either inserts.

Replay the existing order

The next example keeps the request ID on the order row itself. The loop represents two attempts with identical inputs. It returns the original order ID after the unique constraint rejects the second insert.

The fixed GUID below makes the demo repeatable. Generate a fresh ID for each new application request, then retain it for retries.

The error handler checks for an existing matching request before treating a duplicate error as a replay. It rejects changed customer or amount values. Other errors still reach the caller.

SET NOCOUNT ON;
CREATE TABLE #OrdersWithRequestKey
(
    OrderId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    RequestId uniqueidentifier NOT NULL UNIQUE,
    CustomerId int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
DECLARE @RequestId uniqueidentifier =
    '6D24BD35-1338-485C-AF48-22DA736C96AE';
DECLARE @CustomerId int = 42;
DECLARE @Amount decimal(10,2) = 125.00;
DECLARE @Attempt int = 1;
DECLARE @Results TABLE
(
    Attempt int,
    Outcome varchar(20),
    OrderId int,
    CustomerId int,
    Amount decimal(10,2)
);
WHILE @Attempt <= 2
BEGIN
    DECLARE @Outcome varchar(20) = 'Created';
    BEGIN TRY
        INSERT INTO #OrdersWithRequestKey
            (RequestId, CustomerId, Amount)
        VALUES (@RequestId, @CustomerId, @Amount);
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() NOT IN (2601, 2627)
            THROW;
        IF NOT EXISTS
        (
            SELECT 1 FROM #OrdersWithRequestKey
            WHERE RequestId = @RequestId
        )
            THROW;
        IF EXISTS
        (
            SELECT 1 FROM #OrdersWithRequestKey
            WHERE RequestId = @RequestId
              AND (CustomerId <> @CustomerId OR Amount <> @Amount)
        )
            THROW 50001, 'RequestId already has different order details.', 1;
        SET @Outcome = 'Replayed';
    END CATCH;
    INSERT INTO @Results
    SELECT @Attempt, @Outcome, OrderId, CustomerId, Amount
    FROM #OrdersWithRequestKey
    WHERE RequestId = @RequestId;
    SET @Attempt += 1;
END;
SELECT Attempt, Outcome, OrderId, CustomerId, Amount
FROM @Results
ORDER BY Attempt;
SELECT COUNT(*) AS StoredOrders
FROM #OrdersWithRequestKey;
DROP TABLE #OrdersWithRequestKey;

Both attempts return order ID 1. The second outcome is Replayed, and StoredOrders remains 1. The loop and results table make the demonstration visible; they are not an application retry scheduler.

Test the cases that break the promise

Change the amount only for a third attempt, while keeping the same request ID. The handler should reject it with error 50001. Reusing an ID must not silently accept different order details.

Also try a new request ID on every attempt. Two orders should appear, because those IDs describe separate requests. This catches an easy application mistake before the retry policy reaches production.

In my test, the changed payload failed with 50001 and left one order. A NULL amount raised error 515 rather than being swallowed. The normal replay returned the original ID and kept the row count at one.

Keep the production boundary clear

This sample uses separate autocommit statements and one order table. An application transaction needs its own rollback and recovery handling. Do not continue inside an uncommittable transaction after catching an error.

If an operation writes several tables, commit the request record and related changes in one transaction. Return success only after that transaction commits. Otherwise, the request marker can survive while the business operation remains incomplete.

Keep original request inputs immutable for comparison, and retain the request record throughout the supported retry period. Deleting that record removes the duplicate protection. Scope request keys appropriately for tenants and operations, and still enforce authorization.

Retries remain useful for temporary failures, but they need bounded attempts and delays. A database constraint also cannot deduplicate an email or another external action. Those actions need their own repeat-safe design.

Give each request one identity, and make every retry account for the result already stored.

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