Dev.to WebDev πŸ›  Dev πŸ‘ 0 πŸ“– 9 min read

Preventing Duplicate Orders in PHP and MySQL: Transactions, Idempotency and Safe Retry Handling

How to build reliable order-processing workflows in business applications using PHP, CodeIgniter 4 and MySQL Duplicate orders are one of the most expensive and embarrassing bugs in e-commerce, B2B distribution portals,

How to build reliable order-processing workflows in business applications using PHP, CodeIgniter 4 and MySQL

Duplicate orders are one of the most expensive and embarrassing bugs in e-commerce, B2B distribution portals, and ERP applications. When a single checkout action creates two identical orders, the downstream fallout hits every department: inventory gets double-deducted, warehouse teams pack duplicate shipments, customers are billed twice, and support teams spend hours issuing manual refunds and reconciling ledgers.

Relying solely on JavaScript to disable a "Submit Order" button in the browser will never protect a production backend. Real reliability requires defensive backend engineering combining database transactions, idempotency keys, unique MySQL constraints, and safe retry handling.

Here is a practical architectural guide to building duplicate-proof order processing workflows using PHP 8+, CodeIgniter 4, and MySQL.

1. Why Duplicate Orders Happen in Business Applications

In real-world web and mobile applications, duplicate write requests rarely happen because a user maliciously spammed an endpoint. Instead, they happen naturally due to everyday network and user behavior:

  • Impatient Double-Clicks: A user on a slow mobile connection clicks "Place Order," sees no instant page transition, and taps the button again 800 milliseconds later.
  • Network Timeouts & Client Retries: The PHP backend successfully processes the order and commits it to MySQL, but the HTTP response drops over a spotty 4G/5G connection. The mobile app or browser automatically retries the POST /api/orders request.
  • Payment Gateway Webhook Retries: Payment providers (Razorpay, Stripe, PayU) guarantee at-least-once webhook delivery. If your webhook endpoint takes slightly too long to respond with 200 OK, the gateway sends the exact same payment.captured payload two or three times.
  • Background Queue Re-Deliveries: Worker processes that crash right after inserting a recordβ€”before acknowledging the jobβ€”re-run the same payload upon restart.

2. Why Frontend Button Disabling Is Not Sufficient

Disabling the submit button via JavaScript (button.disabled = true) is good UX, but it is not a data integrity control:

  1. Network-Level Retries Bypass UI State: Mobile HTTP clients, service workers, and reverse proxies retry timed-out POST requests without re-rendering the DOM.
  2. Browser Refresh / Back-Button Resubmission: Users refreshing a confirmation page or hitting "Back" on legacy form submissions can trigger a second POST payload.
  3. API & Mobile Consumers: REST APIs consumed by mobile apps, B2B dealer portals, or third-party integrations have no browser button to disable.

True duplicate protection must live at the API and database layer.

3. Understanding Database Transactions in CodeIgniter 4 & MySQL

Order creation is rarely a single INSERT. A typical order workflow writes across multiple tables simultaneously:

  1. Insert the parent row into orders.
  2. Insert multiple SKU rows into order_items.
  3. Deduct available stock from inventory.
  4. Record a ledger entry in payment_transactions.

Without an ACID database transaction (using MySQL's InnoDB storage engine), a failure on step 3 leaves behind a half-created order with missing stock deductions. Wrapping all writes inside a transaction ensures atomicity: either every step succeeds and commits together, or everything rolls back cleanly.

In CodeIgniter 4, manual transaction blocks give you explicit control over exceptions and rollback states:

$db = \Config\Database::connect();$db->transBegin();

try {
    // 1. Insert order
    // 2. Insert order items
    // 3. Deduct inventory with row locking
    $db->transCommit();
} catch (\Throwable $e) {$db->transRollback();
    throw $e;
}

4. Implementing Idempotency Keys in PHP

An idempotency key is a unique client-generated token (typically a UUIDv4) sent with a POST requestβ€”usually via an Idempotency-Key HTTP headerβ€”that allows the server to recognize retries of the exact same logical operation.

If the client retries a timed-out request using the same Idempotency-Key, the server detects that the key was already processed and returns the original saved response instead of creating a second order.

MySQL Schema for Idempotency & Orders

CREATE TABLE idempotency_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    idempotency_key VARCHAR(64) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    status ENUM('processing', 'completed', 'failed') NOT NULL DEFAULT 'processing',
    response_code SMALLINT UNSIGNED NULL,
    response_body JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    UNIQUE KEY uq_user_idempotency (user_id, idempotency_key),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    idempotency_key VARCHAR(64) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'confirmed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_order_number (order_number),
    UNIQUE KEY uq_orders_idempotency (user_id, idempotency_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Why the UNIQUE KEY Matters Under Concurrency
A common mistake is writing SELECT * FROM idempotency_keys WHERE key = ? followed by INSERT. If two duplicate requests arrive 2 milliseconds apart, both SELECT queries return empty before either inserts a row.

Instead, claim the key atomically via INSERT and let MySQL's UNIQUE constraint act as the concurrency lock:

PHP

<?php

namespace App\Services;

use CodeIgniter\Database\BaseConnection;
use CodeIgniter\Database\Exceptions\DatabaseException;
use RuntimeException;

class OrderCreationService
{
    public function __construct(private BaseConnection $db) {}

    public function placeOrder(int $userId, string $idempotencyKey, array$payload): array
    {
        $requestHash = hash('sha256', json_encode($payload));

        // Step 1: Atomically claim the idempotency key before doing any work
        try {
            $this->db->table('idempotency_keys')->insert([
                'user_id'         => $userId,
                'idempotency_key' => $idempotencyKey,
                'request_hash'    => $requestHash,
                'status'          => 'processing',
                'expires_at'      => date('Y-m-d H:i:s', strtotime('+24 hours')),
            ]);
        } catch (DatabaseException $e) {
            // Duplicate key (MySQL error 1062): inspect existing record
            return $this->handleExistingKey($userId, $idempotencyKey,$requestHash);
        }

        // Step 2: Execute order creation inside an ACID transaction
        $this->db->transBegin();

        try {
            $orderId = $this->createOrderAndDeductStock($userId, $idempotencyKey,$payload);

            $responsePayload = [
                'status'   => 'success',
                'order_id' => $orderId,
                'message'  => 'Order placed successfully.',
            ];

            $this->db->table('idempotency_keys')
                ->where('user_id', $userId)
                ->where('idempotency_key', $idempotencyKey)
                ->update([
                    'status'        => 'completed',
                    'response_code' => 201,
                    'response_body' => json_encode($responsePayload),
                ]);

            $this->db->transCommit();

            return ['code' => 201, 'body' => $responsePayload];
        } catch (\Throwable $e) {$this->db->transRollback();

            // Mark key as failed or delete so a legitimate retry can proceed
            $this->db->table('idempotency_keys')
                ->where('user_id', $userId)
                ->where('idempotency_key', $idempotencyKey)
                ->delete();

            throw $e;
        }
    }

    private function handleExistingKey(int $userId, string $idempotencyKey, string$requestHash): array
    {
        $existing =$this->db->table('idempotency_keys')
            ->where('user_id', $userId)
            ->where('idempotency_key', $idempotencyKey)
            ->get()
            ->getRowArray();

        // Guard against reusing the same key with a modified cart/payload
        if ($existing['request_hash'] !==$requestHash) {
            return [
                'code' => 422,
                'body' => ['status' => 'error', 'message' => 'Idempotency key reused with different payload.'],
            ];
        }

        // Request #1 is still in flight
        if ($existing['status'] === 'processing') {
            return [
                'code' => 409,
                'body' => ['status' => 'conflict', 'message' => 'Order request is currently being processed. Please retry shortly.'],
            ];
        }

        // Return cached response verbatim without re-creating the order
        return [
            'code' => (int) $existing['response_code'],
            'body' => json_decode($existing['response_body'], true),
        ];
    }

    private function createOrderAndDeductStock(int $userId, string $idempotencyKey, array$payload): int
    {
        $totalAmount = 0;

        // Lock inventory rows with FOR UPDATE to prevent race-condition overselling
        foreach ($payload['items'] as$item) {
            $product =$this->db->query(
                'SELECT id, price, stock_qty FROM products WHERE id = ? FOR UPDATE',
                [$item['product_id']]
            )->getRowArray();

            if (!$product || $product['stock_qty'] <$item['quantity']) {
                throw new RuntimeException("Insufficient stock for product ID {$item['product_id']}");
            }

            $this->db->query(
                'UPDATE products SET stock_qty = stock_qty - ? WHERE id = ?',
                [$item['quantity'],$item['product_id']]
            );

            $totalAmount += $product['price'] *$item['quantity'];
        }

        $this->db->table('orders')->insert([
            'order_number'    => 'ORD-' . date('Ymd') . '-' . random_int(100000, 999999),
            'user_id'         => $userId,
            'idempotency_key' => $idempotencyKey,
            'total_amount'    => $totalAmount,
            'status'          => 'confirmed',
        ]);

        return (int) $this->db->insertID();
    }
}

Notice two critical details in this implementation:

  • Payload Fingerprinting (request_hash): Hashing the request body prevents a buggy client from accidentally sending a new cart using an old idempotency key and receiving stale confirmation data.
  • Pessimistic Row Locking (SELECT ... FOR UPDATE): Locking product rows inside the transaction ensures concurrent orders cannot oversell the last unit in stock.

5. Using Unique Database Constraints as a Final Safety Net

Even with idempotency keys at the API layer, always enforce business-level uniqueness directly in MySQL:

  • Unique Payment Transaction ID: Add a UNIQUE index on gateway_payment_id in your payment_transactions table so a payment can never be credited twice.
  • Unique External Reference Number: For B2B ERP portals where dealers submit Purchase Orders, enforce UNIQUE (dealer_id, dealer_po_number).
  • Graceful 1062 Handling: Catch MySQL duplicate entry error code 1062 cleanly in PHP and fetch the existing record rather than exposing raw SQL stack traces.

6. Handling Payment Gateway Callbacks & Webhooks Safely

Payment gateways frequently fire both a frontend redirect callback and an asynchronous server-to-server webhook at virtually the same instant. Without proper locking, both processes can see order.status = 'pending' and trigger duplicate confirmation emails or warehouse dispatch tickets.

Use a state-machine conditional update or SELECT ... FOR UPDATE inside a transaction:

public function handlePaymentCaptured(string $gatewayOrderId, string$gatewayPaymentId): void
{
    $this->db->transBegin();

    try {
        // Lock the order row so concurrent webhook + browser callback serialize cleanly
        $order =$this->db->query(
            'SELECT id, status FROM orders WHERE gateway_order_id = ? FOR UPDATE',
            [$gatewayOrderId]
        )->getRowArray();

        if (!$order) {
            throw new RuntimeException('Order not found.');
        }

        // Idempotent exit: already processed by an earlier callback
        if ($order['status'] === 'confirmed') {$this->db->transCommit();
            return;
        }

        $this->db->table('orders')
            ->where('id', $order['id'])
            ->update([
                'status'             => 'confirmed',
                'gateway_payment_id' => $gatewayPaymentId,
                'paid_at'            => date('Y-m-d H:i:s'),
            ]);

        $this->db->transCommit();

        // Dispatch confirmation email/WhatsApp job only after commit succeeds
    } catch (\Throwable $e) {$this->db->transRollback();
        throw $e;
    }
}

7. Implementing Safe Retry Logic and Error Handling

When your PHP backend communicates with external shipping aggregators, ERP sync endpoints, or payment APIs, distinguish carefully between transient errors and permanent errors:

  • Retry Only Transient Failures: Retry network timeouts, 429 Too Many Requests, and 502/503/504 server errors using exponential backoff with jitter (e.g., 1s, 2s, 4s + random milliseconds) so retrying workers do not create a thundering herd.
  • Never Retry 4xx Validation Errors: Responses like 400 Bad Request, 401 Unauthorized, or 422 Unprocessable Entity will fail identically on every retry.
  • Forward Idempotency Keys Downstream: Always pass your internal order's idempotency_key to downstream courier or payment APIs so their servers also deduplicate your retries.

8. Testing Duplicate Requests and Concurrent Submissions

Unit tests alone rarely catch race conditions because single-threaded tests execute sequentially. Before deploying order workflows to production, simulate real concurrency:

  1. Parallel cURL / Shell Burst Test: Fire 10 simultaneous requests with the exact same Idempotency-Key header:
seq 1 10 | xargs -n1 -P10 -I{} curl -s -o /dev/null -w "%{http_code}\n" \
  -X POST [https://api.example.com/v1/orders](https://api.example.com/v1/orders) \
  -H "Authorization: Bearer $TOKEN" \
  -H "Idempotency-Key: 550e8400-e29b-41d4-a716-446655440000" \
  -H "Content-Type: application/json" \
  -d '{"items":[{"product_id":101,"quantity":1}]}'

Verify that exactly 1 row exists in orders, stock decrements by 1, and responses return 201 (or 409 while in-flight, followed by 201).

  1. Chaos / Timeout Simulation: Simulate a client disconnect mid-request and verify that replaying the same Idempotency-Key returns the exact saved JSON response without creating a second order.

9. Best Practices Checklist for Production-Ready Order Processing

  • Require Idempotency-Key Headers: Enforce client-generated UUIDv4 keys on all order creation and payment capture endpoints.
  • Scope Keys Per Authenticated User/Tenant: Always pair (user_id, idempotency_key) in your unique index so one user cannot collide with or probe another user's key.
  • Schedule Key Expiration Cleanup: Run a daily cron job (DELETE FROM idempotency_keys WHERE expires_at < NOW()) after a 24–72 hour retention window so the table stays lean and fast.
  • Keep External Side Effects Out of Open DB Transactions: Never send emails, WhatsApp notifications, or third-party HTTP calls inside an open MySQL transactionβ€”commit first, then queue asynchronous jobs.

Architecting resilient transactional backends and custom ERP/e-commerce order engines requires careful attention to concurrency and failure recovery. At Software Solutions, our custom software development team builds high-reliability PHP, CodeIgniter, and MySQL business applications engineered to handle real-world traffic without data inconsistencies.

How do you handle idempotency and concurrent order locking in your PHP applications? Share your patterns or war stories in the comments below!

πŸ“° Read the original article on Dev.to WebDev

Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes β€” full credit and traffic to the original publisher.