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/ordersrequest. - 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 samepayment.capturedpayload 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:
- Network-Level Retries Bypass UI State: Mobile HTTP clients, service workers, and reverse proxies retry timed-out
POSTrequests without re-rendering the DOM. - Browser Refresh / Back-Button Resubmission: Users refreshing a confirmation page or hitting "Back" on legacy form submissions can trigger a second
POSTpayload. - 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:
- Insert the parent row into
orders. - Insert multiple SKU rows into
order_items. - Deduct available stock from
inventory. - 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:
- 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).
- 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!

