Distributed Scheduled Jobs

Solution

Schema:

CREATE TABLE scheduler_leases (
    lease_name VARCHAR(64) PRIMARY KEY,
    holder_token VARCHAR(64) NULL,
    expires_at INT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO scheduler_leases (lease_name, holder_token, expires_at)
VALUES ('renewal-reminders', NULL, 0);

expires_at plays the same role as the Redis lease's EX TTL — a worker that dies mid-run leaves a row with a holder_token set, but once expires_at is in the past, that token is treated as stale and available to reclaim, exactly like Redis's automatic key expiry.

Acquiring atomically:

<?php

declare(strict_types=1);

// no-execute: requires a live MySQL connection via PDO.
function acquireLease(PDO $pdo, string $leaseName, int $ttlSeconds): ?string
{
    $token = bin2hex(random_bytes(16));
    $now = time();

    $stmt = $pdo->prepare(
        'UPDATE scheduler_leases
         SET holder_token = :token, expires_at = :expires
         WHERE lease_name = :lease_name
           AND expires_at < :now'
    );
    $stmt->execute([
        'token' => $token,
        'expires' => $now + $ttlSeconds,
        'lease_name' => $leaseName,
        'now' => $now,
    ]);

    return $stmt->rowCount() === 1 ? $token : null;
}

This is the same atomic-UPDATE-with-a-WHERE-condition pattern from the poor man's cron lesson's database exercise, applied to a lease instead of a plain timestamp: WHERE expires_at < :now only matches a row whose current lease has genuinely expired (or never been held). Two workers' cron jobs firing in the same second both issue this UPDATE; the database serializes the two writes to the same row, and only the first one finds the row still matching the WHERE clause — the second one's UPDATE runs after the first has already advanced expires_at into the future, so it matches zero rows and correctly gets null back.

Releasing, with ownership check:

<?php

declare(strict_types=1);

// no-execute: requires a live MySQL connection via PDO.
function releaseLease(PDO $pdo, string $leaseName, string $token): void
{
    $stmt = $pdo->prepare(
        'UPDATE scheduler_leases
         SET holder_token = NULL, expires_at = 0
         WHERE lease_name = :lease_name
           AND holder_token = :token'
    );
    $stmt->execute(['lease_name' => $leaseName, 'token' => $token]);
}

This checks holder_token = :token in the same statement as the release, for the same reason the article's Redis release does a token comparison before deleting: if this worker's lease already expired and a second worker has since acquired a new lease (with a different token), this worker's release must not clear that new lease. Checking the token as part of the WHERE clause means the release only succeeds (only matches a row) if this worker still actually holds the current lease — a stale release attempt from an expired holder simply matches zero rows and does nothing, leaving the current legitimate holder's lease untouched.

Crash scenario, walked through: worker A acquires the lease at 2:00:00 with expires_at set to 2:00:00 + TTL (say a 10-minute TTL, so 2:10:00), then crashes mid-send at 2:03:00 without ever calling releaseLease(). The row sits with A's stale holder_token and expires_at = 2:10:00. Any acquire attempt from worker B between 2:00:00 and 2:10:00 fails (rowCount() === 0, since expires_at is still in the future) — B correctly believes A might still be legitimately working. Only after 2:10:00 does B's next scheduled attempt see expires_at < now, succeed in acquiring a fresh lease, and run the job. The TTL is what bounds how long A's crash can block the job — chosen the same way the article describes for the Redis case: comfortably longer than the job's realistic runtime, but not so long that a genuine crash blocks reminders for an excessive stretch.

Diagnosis

The direct cause: the crontab entry for the billing job is installed identically on all 3 servers, and cron on each server has no awareness of the other two — this is precisely the N-times problem the article opens with. At the scheduled time, all 3 servers independently fire the job, and each one processes the full set of pending invoices and charges each one, with nothing coordinating between them. A customer with one pending invoice gets charged by however many of the 3 servers' job instances happen to reach that invoice before anything (if anything) stops them — which explains the reported 2-3 charges directly: it's bounded by the server count, because that's exactly how many independent, uncoordinated instances of the job ran.

Why this didn't happen before scale-out: with one server, there was only ever one crontab, so the job only ever ran once per scheduled time — there was no N-times problem to have, not because the job was ever protected against it, but because there was only ever one instance in existence. Scaling to 3 servers didn't introduce a new bug into the billing job's logic — it exposed a coordination assumption ("there's only ever one instance of this cron entry running") that was implicitly true before and silently became false the moment the crontab was replicated across servers without anyone revisiting the job's scheduling design.

The immediate question — is the job idempotent: this determines everything about how urgent and how sufficient a locking fix is. If charging is not idempotent — if "charge this invoice" doesn't first check whether it was already charged — then a distributed lock alone is necessary but not sufficient: even a well-implemented lease can theoretically still allow a rare double-run (a lease expiring mid-run under load, a network partition), and with no idempotency, that rare event is still a real customer charge, not a harmless no-op. Given customers have already been charged multiple times, this also needs a data-correctness fix (refunding or reversing the extra charges) independent of whatever prevents recurrence.

Two-part durable fix, in priority order:

1. Immediate and most urgent: make the charge operation idempotent, keyed on something stable per invoice per billing period (an idempotency key like invoice-{id}-{billing_period}), so a database unique constraint or an explicit "already charged" check makes a duplicate charge attempt a no-op rather than a second real charge — regardless of whether it came from a lock failure, a retry, or (as here) three completely uncoordinated instances all running at once. This is the single fix that would have prevented every customer complaint already received, and it protects against every future coordination failure too, not just this specific one.
2. Also needed, but secondary in urgency: add a distributed lock (Redis lease or database lease, per this lesson) so the job only actually runs once across the fleet, rather than running 3 times and relying entirely on idempotency to absorb it. Idempotency alone technically prevents the customer-facing harm, but running the full billing job 3 times unnecessarily still wastes resources and increases load on whatever the job touches — the lock is what makes the system efficient, not just correct, and the article's framing (locking reduces frequency, idempotency makes the remainder harmless) applies exactly here: you want both, not either.

Review

Is the locking approach sound? Yes, as far as it goes — a Redis lease with NX and a TTL, released in a finally block, is exactly the pattern this lesson and the previous one describe, and it correctly solves the N-times problem across the 5-server fleet: only one server's job instance will successfully acquire the lease and run the sync. Nothing about the locking mechanism itself, as described, is wrong.

What's missing for total-failure scenarios: the design has no missed-run monitoring — no dead man's switch. If the server that acquired the lease loses power or network connectivity mid-sync, the lease will still correctly expire after its 10-minute TTL (Redis doesn't need the crashed server to do anything for that), so locking isn't actually broken here. But nothing in this design tells anyone that the sync didn't complete — the job never reported success anywhere external, so a night where the sync silently fails partway looks, from outside, identical to a night where it succeeded: nobody gets an alert, and the gap is only discovered later, likely when someone notices stale inventory data and has to work backward to figure out when it stopped being accurate. This is precisely the failure mode the article's dead-man's-switch section describes as invisible to locking mechanisms by design — locks control how often a job runs, not whether it ran to completion at all.

The plain-UPDATE-per-row risk: this connects directly to the previous lesson's fencing-token discussion, not just this lesson's lease mechanics. If the lease is somehow lost mid-run — the TTL is shorter than the sync actually takes under some conditions, or a Redis blip causes an unexpected loss — and a second server's instance starts before the first has truly stopped writing, both instances can end up issuing UPDATE statements against the same inventory rows around the same time, with no way for the database to know one of them is operating on a stale, no-longer-authoritative view of "the latest sync." Plain UPDATEs have no built-in protection against this; the previous lesson's fencing-token idea (rejecting writes carrying an older token than the highest one seen) is the mechanism that would close this specific gap, at the cost of the inventory-writing code needing to understand and check tokens — worth doing if a stale double-write to inventory counts is a real correctness risk for this business, and worth skipping (accepting the small residual risk) if it isn't, but the design document should say which was decided rather than leaving it unconsidered.

What to add before approving:

1. A dead man's switch — the job pings an external monitoring endpoint on successful completion, with alerting configured for a missed nightly ping, per this lesson's pattern.
2. An explicit decision on whether the TTL (10 minutes) has been validated against the sync's actual worst-case runtime, not just its typical one — the previous lesson's TTL-mismatch failure mode applies here exactly as it did for the invoice-export example.
3. A stated decision on the fencing-token question above — either "not needed, because [reason]" or a plan to add it — rather than the current document's silence on what happens to the underlying data during a rare lease-loss-and-overlap event.