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!
Top comments (0)