MySQL Under Load in a Sharia-Compliant Pipeline

We fixed the unglamorous things first: bigger disks. Then we gave the PDF service room to breathe — longer timeouts, more browser sessions, Chrome kept warm — which helped, though in hindsight we were treating a symptom we would only cure properly a year later (Case 04). The changes that mattered were structural. Work that ran synchronously inside requests moved into queued jobs. Jobs started carrying lightweight record IDs instead of whole serialized objects, because one stuck serialization had been pinning entire orders. And the single shared queue became three dedicated worker containers. Three. It felt like a fleet at the time.

It took three rounds to actually win. In the first, we treated each incident as it came: remove this lock, log that deadlock (innodb_print_all_deadlocks on, so at least the next one would leave a confession)deadlocks ↓. Every individual fix worked — and the class of problem kept coming back with a new address.

In the second round, April 2025, we went after the class itself and switched the platform to READ COMMITTEDisolation ↓: dramatically less lock contention, in exchange for a strictness the Murabaha flow never actually relied on. Worth being precise about that trade, because in a financial system it sounds alarming. What REPEATABLE READ buys you is a stable snapshot across a long transaction — read a row twice, see the same value both times. No step in this pipeline does that. Each step reads the state it needs, acts, and commits inside a second, and the invariants that actually matter — a unit belongs to one order, a counter never goes negative — are enforced by explicit row locks and database constraints, not by the snapshot. We gave up a guarantee we were not using; the ones we were using are still there.anomalies ↓

The third round came during the summer stress tests, when the sell path deadlocked anyway — the global setting was not reaching every transaction, so the hottest services got it explicitly. And the last deadlocks were removed the cheapest way of all: not by locking more, but by making every code path write to tables in the same order, so transactions could no longer meet head-on.

public function handle(): void
{
    DB::statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
    DB::beginTransaction();

    try {
        $this->inventoryService->assertOrderHasInventories($this->localMarketOrder);
        // ...

We raised the timeout to 120 seconds the same day — not as a fix, but as anesthesia, so testing could continue while we operated. Then came the actual surgery, a deliberate summer campaign. Buying commodities, creating trader orders, and holding units each got their own queue instead of sharing one line. The search for eligible commodities was separated from order initiation, so five workers could do in parallel what one had been doing alone. Inventory scans switched to cursors. We even dropped foreign keysforeign keys ↓ from the busiest join table — heresy in most engineering conversations, but their integrity checks were pure cost on a table written millions of times a day, and every write to it already goes through a single service that enforces the same invariants. It is a real trade rather than a free one: the guarantee moved out of the engine and into code, where it stays honest by review and tests instead of by construction. We would not make the same trade on a table that a dozen code paths could write to. And the units table finally got its first serious indexes.

First, we tuned the press. Longer timeouts, more concurrent sessions, Chrome kept warm and ready. It bought headroom for the next test — and changed nothing about the economics. Still one browser per document, still on the critical path.

Then we replaced the engine. A native PHP library went head-to-head against the browser and won convincingly: ~128 documents a minute against ~18 — seven times the throughput — with no external service, no per-document fees, documents staying on our own infrastructure, and proper Arabic right-to-left out of the box. Better in every way. And still seconds of rendering inside every step.

The winning move came from a different room. Sitting with the business and the Sharia requirements, we asked what the certificate actually had to be — and the answer was: it must exist and be obtainable, not be rendered before the step may complete. So the steps stopped generating PDFs entirely; a certificate renders the first time someone downloads it. And here is the quiet punchline: most certificates are never downloaded at all. Most of that work never needed speeding up. It needed to stop existing.

Generation one: compute the truth on demand. Correct, simple, and ruinously expensive — every order paid the full scan, and those scans were most of the read pressure behind the 94-million-read index in Case 09.

Generation two: the "settled" feature. When an order completes, mark its units as settled back to the market — in bulk, with a cheap flag — so eligibility queries can skip whole classes of units without inspecting them. The scans shrank. But the settlement chain groaned under its own weight (its jobs kept blowing past their time limits and eventually needed a full bulk rewrite), and the per-order scan was still there. Smaller, not gone.

Generation three: stop scanning altogether. If the question is always the same, keep the answer written down. A dedicated table now holds an eligible-quantity counter for every (customer, inventory) pair, and every event that changes the answer — a purchase, a settlement, a supplier switching on — updates the counter in that same moment, atomically. The schema itself refuses to let a counter go negative, and rebuild jobs can reconstruct any counter from the ground truth whenever we have a reason to doubt it.

public function up(): void
{
    // Make eligible_quantity non-negative by using an UNSIGNED integer.
    DB::statement('ALTER TABLE `local_market_eligible_quantities` '
        .'MODIFY `eligible_quantity` INT UNSIGNED NOT NULL');
}

The first one lived in the database. Ask for the eligible inventory that can absorb the most value — ordered by available quantity times unit price — take as many units as fit in what is left, exclude that inventory, ask again. Preferred commodity types first, then any type. It was simple, it was one query per pick, and while the market was small it worked. Its limit was structural: it could only ever walk one path forward. Having taken the biggest bucket first, it had no way to reconsider that choice, so it reported "no eligible commodities" on markets that genuinely did contain an exact answer — just not one reachable greedily. Every such report was a cancelled order.

The second one moved the whole search into memory. Load the eligible inventories once, then work on them without touching the database again. It still starts greedy — one linear pass, taking as many units of each inventory as still fit — because for the overwhelming majority of orders that single pass lands exactly on zero. What is new is what happens when it does not: it backtracks, putting the most-used inventory back, swapping in different ones, and repeating until the remainder closes or the options run out. Two things bound it. Before searching at all, a fast pre-check asks whether an answer can exist — if the most valuable 10,000 units on the table cannot reach the amount, the order fails immediately with a clear message instead of hunting for a solution that is not there. And the search itself is on a deliberate leash: at most 10,000 units per trade, at most three seconds of thinking. One order failing fast with a useful message beats five thousand orders waiting behind a search that refuses to give up.

Two details matter more than they look. It counts in whole halalas, never fractions — floating-point money that drifts by 0.01 is the difference between exact change and no change at all. And it writes down every gap it fails to close, so a failure arrives as a diagnosis rather than an error: the market lacks units at these prices. That list is market feedback; it tells a supplier what to list next.

For a year both algorithms ran side by side behind one interface so they could be judged on identical inputs. In 2026 the greedy one was deleted and the backtracking one became the only path.

What made that judgement possible was a harness. Neither algorithm could be trusted on intuition, and production is a terrible place to discover that a search has a blind spot — the evidence arrives as a cancelled customer order. So we built an internal tool that runs the algorithms against invented markets: thousands of synthetic price-and-quantity mixes crossed with thousands of financing amounts, the same inputs fed to both, recording for each run whether it found an exact cover, how long it took, and what remained uncovered. It turned "seems fine" into a table you could read. It is what caught the amount smaller than the cheapest unit on the market, the combinations only backtracking could reach, and the inputs where the search would have run past its time budget — each one fixed before an order ever met it.

private const DECIMAL_SCALE = 2;

public function __construct()
{
    $this->maxUnitsPerTrader = config('trader.providers.lynk.max_units_per_trader', 10000);
    $this->loanCoverageTimeout = config('trader.providers.lynk.loan_coverage_timeout', 3);
}

// ...

    $elapsedTime = microtime(true) - $startTime;
    if ($elapsedTime >= $this->loanCoverageTimeout) {
        // ...
        throw new Exception('Request timeout!', 408);
    }

// ...

private function toMinorUnits(float $value): int
{
    return (int) round($value * (10 ** self::DECIMAL_SCALE));
}

The fixes read like an apology to the database. One composite index turned the hot lookup from a scan into a seek.indexes ↓ Then we stopped asking the question and started remembering the answer: the last action an order took now lives on the order itself, written in the same transaction that appends the history row — the trail stays the record of truth, the column is just its most recent line, already read. The cost moved from every read to one write per transition, and roughly 90% of those history queries stopped running at all. And the scheduler grew up: it loads work in batches, skips anything it touched in the last five minutes, checks what is already queued before dispatching again, and staggers its dispatches with a small random delay — so a thousand jobs stop arriving in the very same second.

The percentile history let us bisect by deployment, and the answer was uncomfortable: the slowdown came from a bug-fix. A certificate had been naming the wrong previous owner; the correction was right, but it did its checking in the middle of every single purchase. We reverted it in production the same week — protecting the step while the fix stayed alive in testing — and then rebuilt the same correction so it did its work off the critical path.

Exclusive holds took years of iteration: counters that tolerate crashes without drifting, a repair job for corrupted holds, overlap middleware on the jobs that could collide — and one queue deliberately left at a single worker, because for that step, correctness genuinely could not be parallelized. Knowing when not to scale is also scaling.

The table's indexes have a history like a battlefield map:query plans ↓ added, removed, rebuilt as virtual-column composites, extended, pruned again. Every index makes the reads faster and the millions of ownership writes slower, so each one had to earn its keep with measurements — index hygiene became a recurring chore, not a one-time setup.

2024-11  add previous-owner index
2024-12  add indexes to the inventory-units table
2024-12  add more index columns to the same table
2025-01  remove indexes from the inventory-units table
2025-02  create a new index on the inventory-units table
2025-03  add index (market inventory units)
2025-07  add index (market inventory units)
2025-10  add index on the last purchasing order
2025-11  drop the previous-owner grouping index   (three times)
2026-04  add a covering index
2026-07  drop unused indexes

One experiment deserves its own confession. We added model-level caching to the hot tables to save reads — and months later reverted it for the write-heavy ones, because under real load the caching cost more than it saved. It stung. It is also exactly why we measure.

And the trigger met its match at settlement scale. The per-row trigger guarantees no ledger row is ever missed — and it became the slowest part of bulk settlement. We did not remove the guarantee; we changed who writes. The settlement rewrite produces the very same ledger rows through set-based bulk statements, thousands at a time, with the trigger stepped around only for that audited path.

The platform we run now starts from a different assumption: every job will eventually die mid-flight — the work is making that boring. Jobs retry on their own, waiting 1, 2, 3, 5, then 30 seconds between attemptsretries ↓: patient enough for a transient fault to clear, quick enough that the order barely notices. If we had to name the single most important mechanism behind zero failed transactions, it is this one — most failures are temporary, and a platform that retries well simply does not lose them.

Critical jobs are declared unique per order across the whole fleetunique jobs ↓ — Laravel's ShouldBeUnique, backed by the Redis lock store — so a duplicate dispatch from any server is refused at the queue itself, not caught after the damage. The locks learned to die: they expire shortly after the worker timeout, so a crashed worker's lock heals on its own, and an overlapping dispatch politely re-queues after a short delay instead of burning through its retries on the spot. Even deploys stopped being a hazard — a worker being shut down now finishes or hands back its job instead of taking it to the grave.

class TransferOwnershipToCustomer implements ShouldBeUnique, ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels, StopsOrderOnJobFailure;

    public $tries = 5;

    public $backoff = [1, 2, 3, 5, 30];

/**
 * WithoutOverlapping with safe defaults.
 *
 * Laravel's WithoutOverlapping defaults to expiresAfter = 0, meaning the lock
 * never expires. When a worker is killed mid-job (timeout, OOM, deploy) the
 * lock is orphaned forever, permanently blocking that order's job and producing
 * MaxAttemptsExceededException with no underlying error. These defaults make a
 * dead worker's lock self-heal:
 *
 * - expiresAfter: comfortably above the worker --timeout (120s) so a legitimately
 *   long-running job keeps its lock, while a crashed worker's lock is reclaimed.
 * - releaseAfter: re-queue an overlapping dispatch after a delay instead of
 *   spinning and burning all retry attempts instantly.
 */
class PreventOverlapping extends WithoutOverlapping
{
    public function __construct($key = '', $releaseAfter = 10, $expiresAfter = 180)
    {
        parent::__construct($key, $releaseAfter, $expiresAfter);
    }
}