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!