Building Idempotent DDL Retry Logic

A DDL controller that has crashed mid-run must be able to start again and do exactly nothing harmful — no second migration, no duplicate cutover — and the only way to guarantee that is to make “have I already done this?” a question the controller can answer deterministically before it acts.

Context

This page is the crash-safety layer of the control loop in Automating DDL Pipelines with Python, part of the wider discipline of Online DDL Orchestration & Migration Coordination. The loop persists a migration UUID and re-reads it on restart, which handles the common case. But two harder cases remain: the controller that crashed between submitting and persisting (so the store has no UUID, yet a migration is running), and the controller that retries so aggressively it hammers the control plane or trips Vitess’s own --singleton guard. Both are solved the same way — by making the migration’s identity a deterministic function of the change, so a retry can find the work it already started instead of starting it again.

Idempotency here does not mean “submitting twice is harmless.” Submitting twice is precisely what we must prevent, because a second concurrent migration on the same table is either rejected (a --singleton collision that stalls the pipeline) or, without --singleton, launched — doubling the copy load and destroying the single-cutover guarantee. Idempotency means the operation of running the pipeline converges to one migration no matter how many times it is retried.

The concept: identity before action

The mechanism is a deterministic --migration-context — a caller-supplied label Vitess attaches to every shard’s migration row. If the label is derived from the change itself (keyspace plus normalized DDL), then the same logical change always produces the same context, and a restarted controller can ask Vitess “is there already a migration with this context?” before it submits. That question, answered against the control plane rather than against the controller’s own possibly-lost state, is what closes the submit-then-crash gap.

Three properties make a context safe to key on:

  • Deterministic — the same change yields the same context across restarts and across hosts, so it survives the controller being rescheduled.
  • Normalized — cosmetic differences in the DDL (whitespace, case) must not produce different contexts, or a retry of a re-formatted statement looks like new work.
  • Collision-resistant across distinct changes — two genuinely different migrations must get different contexts, or the controller would mistake one for a retry of the other and skip a real change.

A short hash of the normalized statement satisfies all three. It is not a security boundary, so a fast digest is fine; it is a stable name for a unit of work.

Solution: a crash-safe, backoff-aware submitter

The submitter’s contract: given a keyspace and a DDL statement, return the UUID of the one migration for that change — submitting it if and only if no migration with its context already exists.

import hashlib
import json
import re
import subprocess
import time


def migration_context(keyspace: str, ddl: str) -> str:
    """Deterministic, normalized label for one logical change. Retrying the same
    change re-derives the same context, so it maps to the migration already in flight."""
    normalized = re.sub(r"\s+", " ", ddl.strip()).lower()
    digest = hashlib.sha1(f"{keyspace}::{normalized}".encode()).hexdigest()[:12]
    return f"ddl-{keyspace}-{digest}"


def find_in_flight(keyspace: str, context: str) -> str | None:
    """Ask the control plane whether a migration with this context already exists.
    Returns its UUID if so — the recovery handle after a submit-then-crash."""
    proc = subprocess.run(
        ["vtctldclient", "OnlineDDL", "show", keyspace, "--json"],
        capture_output=True, text=True, check=True, timeout=30,
    )
    rows = json.loads(proc.stdout or "[]")
    for r in rows:
        if r.get("migration_context") == context:
            return r["migration_uuid"]           # already submitted — reuse it
    return None


def submit_idempotent(keyspace: str, ddl: str) -> str:
    """Return the UUID for this change, submitting only if it is not already running."""
    context = migration_context(keyspace, ddl)
    existing = find_in_flight(keyspace, context)
    if existing is not None:
        return existing                          # retry path: do NOT submit again
    proc = subprocess.run(
        ["vtctldclient", "ApplySchema",
         "--ddl-strategy", "vitess --postpone-completion --singleton",
         "--migration-context", context,
         "--sql", ddl,
         keyspace],
        capture_output=True, text=True, check=True, timeout=120,
    )
    uuid = proc.stdout.strip().splitlines()[-1].strip()
    if not uuid:
        # Submission may have raced with another controller; re-check by context.
        uuid = find_in_flight(keyspace, context)
        if uuid is None:
            raise RuntimeError("ApplySchema returned no UUID and none is in flight")
    return uuid

The find_in_flight check runs before every submission attempt, so the submitter is safe to call any number of times: the first call submits, every subsequent call for the same change finds the existing migration by context and returns its UUID. The race-recovery branch after submission handles two controllers starting at once — if ApplySchema returns empty because a peer won the race, the loser re-reads by context and adopts the winner’s UUID rather than erroring.

Retries need pacing, or a crash-loop becomes a control-plane flood. Wrap transient operations in bounded exponential backoff with jitter:

import random


def with_backoff(fn, *, attempts: int = 6, base: float = 2.0, cap: float = 120.0):
    """Retry a transient control-plane call with exponential backoff and jitter.
    Jitter prevents synchronized controllers from retrying in lockstep (a retry storm)."""
    last = None
    for attempt in range(attempts):
        try:
            return fn()
        except (subprocess.TimeoutExpired, subprocess.CalledProcessError) as e:
            last = e
            if attempt == attempts - 1:
                break
            delay = min(cap, base * (2 ** attempt))
            delay = delay / 2 + random.uniform(0, delay / 2)   # full-ish jitter
            time.sleep(delay)
    raise RuntimeError(f"control-plane call failed after {attempts} attempts") from last


# Idempotent submission, retried safely: re-running submit_idempotent is a no-op
# once the migration exists, so wrapping it in backoff cannot double-submit.
uuid = with_backoff(lambda: submit_idempotent("commerce",
    "ALTER TABLE orders ADD COLUMN fulfilment_center_id BIGINT UNSIGNED NULL"))

Because submit_idempotent is itself idempotent, wrapping it in a retrying helper is safe — the retry re-checks for the in-flight migration and returns it, rather than launching a second one. That composition, an idempotent operation under a bounded-backoff retry, is the whole pattern.

Coordinating multiple controllers

The deterministic context makes a single controller crash-safe. When more than one controller can act on the same keyspace — a redundant pair for availability, or a job that can be triggered concurrently by two events — the context is still the safety net, but two controllers can now race to submit the same change in the same instant, before either has written a row find_in_flight could see. --singleton closes the window on Vitess’s side (only one migration per table is admitted, the loser gets a rejection), and the race-recovery branch in submit_idempotent turns that rejection into an adoption of the winner’s UUID. That is enough for correctness, but it is wasteful if both controllers then poll and both try to complete.

The clean fix is to make one controller the actor and the others observers, using an advisory lock in the same durable store the pipeline already uses:

def try_acquire(store, context: str, owner: str, ttl_s: int = 900) -> bool:
    """Best-effort leader lock keyed by migration context. Only the holder acts."""
    with store.cursor() as cur:
        cur.execute(
            """INSERT INTO ddl_lock (context, owner, expires_at)
               VALUES (%s, %s, NOW() + INTERVAL %s SECOND)
               ON DUPLICATE KEY UPDATE
                 owner = IF(expires_at < NOW(), VALUES(owner), owner),
                 expires_at = IF(expires_at < NOW(), VALUES(expires_at), expires_at)""",
            (context, owner, ttl_s),
        )
        store.commit()
        cur.execute("SELECT owner FROM ddl_lock WHERE context = %s", (context,))
        return cur.fetchone()[0] == owner

A controller that fails to acquire the lock does not exit — it keeps polling in read-only mode so it can take over if the leader’s lease expires (the leader crashed). The TTL is what makes the lock self-healing: a dead leader’s lease lapses and a standby claims it, then adopts the in-flight migration by context exactly as a restarted single controller would. The lock is an optimization to avoid redundant work and duplicate completion attempts; --singleton plus context-keyed adoption is the correctness guarantee underneath it.

Edge cases and gotchas

  • Retry storms. A controller in a tight crash-restart loop, or many controllers reacting to the same event, can hammer vtctldclient and the topology server. Exponential backoff caps the rate; the jitter term is what desynchronizes multiple controllers so they do not all retry on the same tick. Without jitter, N controllers restarting together retry in lockstep and produce N-way spikes.
  • --singleton collisions. --singleton rejects a second concurrent migration on the same table — which is the desired safety net, but it surfaces as a CalledProcessError if your submitter did not check for the in-flight migration first. Always call find_in_flight before ApplySchema; treat a singleton rejection that still slips through as a signal to re-read by context, not as a hard failure. Note --singleton keys on the table while your context keys on the exact statement, so two different changes to the same table legitimately collide under --singleton — serialize them, do not strip the flag.
  • Stale locks and abandoned migrations. A migration whose controller died may sit in running or a postponed state indefinitely, and its context is still “in flight,” so find_in_flight returns it and a new controller correctly adopts it. But if that migration is genuinely wedged (a stuck copy, a lost tablet), adoption alone will not unstick it — the adopting controller must still honour the poll deadline and escalate, cancelling with vtctldclient OnlineDDL cancel before re-submitting a fresh migration. Never re-submit around a stuck migration without cancelling it first, or you strand its artifact tables.
  • Context that is not actually deterministic. Embedding a timestamp, a run ID, or an unnormalized statement in the context defeats the entire mechanism — every retry derives a new context, finds no match, and submits again. Keep the context a pure function of keyspace and normalized DDL, and unit-test that migration_context(k, ddl) == migration_context(k, reformat(ddl)).
  • Normalization that is too aggressive. Lower-casing and collapsing whitespace is safe; stripping comments or reordering clauses can make two different statements hash equal and cause the controller to skip a real change as a duplicate. Normalize only what is genuinely cosmetic.
  • Backoff that outlives the change window. A capped exponential backoff can push a retry past the maintenance window the cutover was scheduled for. When retries are exhausted or the window is closing, fail the pipeline and alert rather than cutting over late; the per-shard states you are waiting on are described in decoding Online DDL migration states.

Verification

Prove the submitter is idempotent by running it twice for the same change and asserting exactly one migration exists with that context:

ddl = "ALTER TABLE orders ADD COLUMN fulfilment_center_id BIGINT UNSIGNED NULL"
u1 = submit_idempotent("commerce", ddl)
u2 = submit_idempotent("commerce", ddl)   # second call must NOT submit again
assert u1 == u2, "submitter is not idempotent — it launched a duplicate"

Then confirm against the control plane that only one migration carries the context, using the deterministic label to filter:

CTX="ddl-commerce-$(printf '%s::%s' commerce \
  'alter table orders add column fulfilment_center_id bigint unsigned null' \
  | sha1sum | cut -c1-12)"
vtctldclient OnlineDDL show commerce --json \
  | jq --arg ctx "$CTX" '[.[] | select(.migration_context == $ctx)] | length'

The count should equal the shard count (one migration row per shard) and never a multiple of it — a multiple means a duplicate migration slipped through and the idempotency check is not being honoured on the submit path. Cross-check with SHOW VITESS_MIGRATIONS LIKE '%' through VTGate and confirm the migration_context column shows your label on exactly the expected rows.

← Back to Automating DDL Pipelines with Python