Prevent Duplicate Payments and Orders

Scenario

A user clicks Pay or Place Order multiple times, or the application retries after a timeout. The same payment/order must not be processed twice.

The main solution is idempotency: multiple requests with the same business operation produce only one result.

1. Generate an idempotency key

The client generates a unique key for each payment attempt:

PAY-20260917-8F32A1

The same key must be reused when retrying the same request.

request.Headers.Add("Idempotency-Key", idempotencyKey);

A genuinely new payment should receive a new key.

2. Enforce uniqueness in SQL Server

Application checks alone are insufficient because two requests could pass the check simultaneously.

CREATE UNIQUE INDEX UX_Payments_IdempotencyKey
ON dbo.Payments(IdempotencyKey);

The database constraint guarantees that only one record can exist for that request.

3. Process it inside a transaction

CREATE PROCEDURE dbo.ProcessPayment
    @IdempotencyKey VARCHAR(100),
    @OrderId        BIGINT,
    @Amount         DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Payments
        (
            IdempotencyKey,
            OrderId,
            Amount,
            PaymentStatus,
            CreatedDate
        )
        VALUES
        (
            @IdempotencyKey,
            @OrderId,
            @Amount,
            'Processing',
            SYSDATETIME()
        );

        UPDATE dbo.Orders
        SET PaymentStatus = 'Paid'
        WHERE OrderId = @OrderId
          AND PaymentStatus = 'Pending';

        IF @@ROWCOUNT = 0
            THROW 50001, 'Order already paid or unavailable.', 1;

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

        -- 2601 and 2627 indicate duplicate-key errors.
        IF ERROR_NUMBER() IN (2601, 2627)
        BEGIN
            SELECT
                PaymentId,
                OrderId,
                Amount,
                PaymentStatus
            FROM dbo.Payments
            WHERE IdempotencyKey = @IdempotencyKey;

            RETURN;
        END;

        THROW;
    END CATCH;
END;

How it works

For two simultaneous requests:

1. Both use the same idempotency key.

2. The first request inserts the payment.

3. The second insert violates the unique index.

4. The second request returns the existing payment instead of processing it again.

5. The order status changes only from Pending to Paid.

Additional protection

Use a unique business reference

If an order can have only one successful payment:

CREATE UNIQUE INDEX UX_Payments_OrderId_Success
ON dbo.Payments(OrderId)
WHERE PaymentStatus = 'Success';

Use controlled status transitions

UPDATE dbo.Orders
SET Status = 'Processing'
WHERE OrderId = @OrderId
  AND Status = 'Pending';

IF @@ROWCOUNT = 0
    THROW 50002, 'Order is already being processed.', 1;

Only one request can successfully change the order from Pending to Processing.

Validate the request

When the same idempotency key is reused, confirm that these values also match:

• Customer

• Order

• Amount

• Currency

Reject the request if the same key is reused with different payment details.

External payment gateway

Do not hold a SQL transaction open while calling an external payment provider.

A safer flow is:

1. Create the payment as Pending.

2. Commit the transaction.

3. Call the gateway using the same idempotency key.

4. Store the gateway reference.

5. Update the payment to Success or Failed.

6. Make callback/webhook processing idempotent as well.

For reliable event publishing, consider the Transactional Outbox pattern.

Interview Answer

I would assign an idempotency key to every payment or order request and enforce it with a unique constraint in SQL Server. Processing would use an atomic transaction and conditional status updates, such as changing an order only from Pending to Processing. If the request is retried, the system returns the previously stored result instead of processing it again. I would also make payment-gateway callbacks idempotent and avoid holding database transactions open during external calls.

Key Points

• Use an idempotency key for every business operation.

• Enforce uniqueness at the database level.

• Use transactions and conditional status updates.

• Do not rely only on a “check then insert” application check.

• Reuse the same key for retries.

• Make external callbacks idempotent too.