Zero-Downtime Database Migration: Stock, Incremental, Validation & Cutover in Spring Boot

Scenario Analysis

Three common migration scenarios: table sharding (single table >100M rows split into 16/64 shards), heterogeneous database replacement (Oracle/DB2 → MySQL/PG, MySQL → TiDB), and cloud/cross-datacenter migration (self-hosted → cloud RDS). The core challenge across all: business cannot stop, data keeps changing, and you must move a continuously changing dataset safely.

Overall Rhythm: Incremental First, Then Stock, Then Cutover

Execution order is critical — start incremental capture before stock migration begins, otherwise changes during stock migration are lost. The sequence:

Record incremental position / start incremental consumption
↓
Stock shard migration (resumable, rate-limited)
↓
Incremental catch-up (lag stable at seconds)
↓
Multi-round full CRC + field-level diff
↓
Read gray → write switch → observe / prepare rollback

Downtime = write-switch time (seconds) + rollback fallback. Stock migration (TB-scale), incremental catch-up (hours), and validation rounds run during business hours without occupying the maintenance window.

Stock Migration Implementation

Cursor-Based Shard Scanning

Never use LIMIT offset, size — deep pagination degrades to full table scan at million-level offsets. Use ordered primary key cursor:

SELECT id, order_no, user_id, amount, status, update_time
FROM t_order
WHERE id > #{cursor} AND id <= #{endPk}
ORDER BY id
LIMIT #{batchSize};

Shard planning: query min/max PK, split into ranges of 100K–500K rows. For non-monotonic keys (UUID, snowflake), fall back to unique index cursor or MOD(CRC32(id), N) bucketing.

Guarded Batch Upsert: Stock vs Incremental Conflict Prevention

Stock writes must not overwrite newer incremental versions. Use conditional upsert with update_time or version guard:

INSERT INTO t_order (id, order_no, user_id, amount, status, update_time)
VALUES ...
ON DUPLICATE KEY UPDATE
  order_no = IF(VALUES(update_time) >= update_time, VALUES(order_no), order_no),
  user_id  = IF(VALUES(update_time) >= update_time, VALUES(user_id), user_id),
  amount   = IF(VALUES(update_time) >= update_time, VALUES(amount), amount),
  status   = IF(VALUES(update_time) >= update_time, VALUES(status), status),
  update_time = IF(VALUES(update_time) >= update_time, VALUES(update_time), update_time);

Place update_time last to avoid MySQL left-to-right assignment quirks. MySQL 8.0.20+ deprecates VALUES() — use alias syntax. Prefer version column over timestamp for precision safety. INSERT IGNORE swallows warnings (truncation, type mismatch) — log warnings at minimum.

Batch size 500–1000 rows. Enable rewriteBatchedStatements=true (MySQL) or reWriteBatchedInserts=true (PostgreSQL) for true batching.

Rate Limiting by Rows, Not Requests

Token bucket keyed on rows/sec, configurable via config center (default 1000 rows/s). Monitor source replica lag, target write RT, CPU before increasing.

Checkpoint Resume & Observability

Task table t_migrate_shard_task tracks cursor_pk, rows_done, status (PENDING/RUNNING/SUCCESS/FAILED), retry_count. Executor updates cursor every batch; on restart, recover PENDING/FAILED/RUNNING tasks (with lease/heartbeat for multi-instance). Progress = SUM(rows_done) / estimated_total pushed to Prometheus.

Source Pressure Control

Read from replica (stale reads acceptable — incremental catches up). Dedicated connection pool, maximumPoolSize single-digit. Concurrency = min(shard_count, config_max), typically 4–8 threads. Schedule during low-traffic windows.

Incremental Synchronization

Approach Trade-offs

Dual-write (app-level) : flexible, heterogeneous transform in app, easy rollback. Cons: high code intrusion (must cover all write paths), poor ordering under async concurrency.

Binlog subscription (Canal/Debezium/Flink CDC) : zero intrusion, natural ordering, low source impact. Requires binlog_format=ROW, binlog_row_image=FULL. Parsing & heterogeneous transform complexity; large transactions & DDL need governance.

Triggers : only for temporary firefighting — write amplification, lock contention, DDL maintenance burden.

Prefer binlog; fallback to dual-write only if no binlog access or transform too complex for pipeline.

Dual-Write Engineering (AOP + afterCommit)

@Around("@annotation(dualWrite)")
public Object around(ProceedingJoinPoint pjp, DualWrite dualWrite) {
  Object result = pjp.proceed();
  if (TransactionSynchronizationManager.isSynchronizationActive()) {
    TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronization() {
      @Override public void afterCommit() { dispatcher.dispatch(dualWrite, pjp.getArgs()); }
    });
  } else { dispatcher.dispatch(dualWrite, pjp.getArgs()); }
  return result;
}

Must use afterCommit — writing target inside transaction causes dirty data if source rolls back. Dispatcher uses bounded queue + dedicated thread pool; overflow logs to t_dual_write_failed for retry job. Failures never propagate to main flow.

Idempotent Writes & Ordering

Guarded upsert (as in stock) or UPDATE ... WHERE id=? AND update_time<=?.

Soft delete (tombstone) — physical delete risks "deleted row resurrected by stock snapshot".

Ordering: single-instance binlog is ordered; parallel consumption routes by PK hash to fixed partition/thread. Never use global thread pool for same table — INSERT/UPDATE reorder corrupts data.

Data Comparison System

Chunked CRC Validation

Full row-by-row comparison infeasible at 100M+. Aggregate per PK range:

SELECT COUNT(*) AS cnt,
       SUM(CRC32(CONCAT_WS('#',
         IFNULL(CAST(id AS CHAR), '\\N'),
         IFNULL(CAST(order_no AS CHAR), '\\N'),
         IFNULL(CAST(amount AS CHAR), '\\N'),
         IFNULL(CAST(status AS CHAR), '\\N'),
         IFNULL(CAST(UNIX_TIMESTAMP(update_time) AS CHAR), '\\N')
       ))) AS crc
FROM t_order
WHERE id BETWEEN #{startPk} AND #{endPk};

Pitfalls handled: CONCAT_WS skips NULL → use IFNULL with sentinel; explicit CAST to string avoids 1.0 vs 1 mismatch; CRC32 32-bit → limit chunk ≤100K rows to reduce collision; unify timezone via UNIX_TIMESTAMP; escape delimiters via HEX/length-prefix.

Field-Level Diff

For CRC-mismatched chunks, load both sides into maps (10K–50K rows/chunk), compute missing/extra/field-diff via equalsSemantically handling null /empty, BigDecimal.compareTo, timestamp truncation.

Diff Handling

MISSING

(source has, target lacks) → backfill with guard. EXTRA (target has, source lacks) → verify if new during migration window or soft-delete unsynced; soft-delete target after confirmation. DIFF (PK exists, fields differ) → pre-cutover: backfill from source; post-cutover: target is authority, reverse-audit.

Repairs via async task logged to t_migrate_diff. Auto-repair if diff rate low; spike → freeze & investigate. Re-run CRC after fix for closure.

Cutover & Rollback

Read Gray (AOP + ThreadLocal)

public enum RouteMode { SOURCE, TARGET, GRAY }
public class DataSourceRouter extends AbstractRoutingDataSource {
  @Override protected Object determineCurrentLookupKey() {
    RouteMode mode = RouteContext.get();
    if (mode == RouteMode.TARGET) return "target";
    if (mode == RouteMode.GRAY && grayMatcher.hit()) return "target";
    return "source";
  }
}

Gray matching priority: whitelist (users/orders) → percentage ( userId % 100 < grayPercent, stable per user) → global switch. RouteContext set at request entry and propagated to async threads via TransmittableThreadLocal — otherwise async tasks lose routing, causing split-brain reads.

Dual-Read Verification

During gray read, async sample (1–5%) re-reads source and compares semantically. Logs mismatches, increments dual_read_mismatch metric. Catches type-mapping errors before user complaints.

Write Switch (Three States, Config Center)

SOURCE

: write old only (initial, validation phase). DUAL: dual-write, old primary, sync/async to new. TARGET: write new primary, reverse-sync to old.

Before TARGET, spin up reverse sync and catch up. Switch moment: brief DUAL drain, not naked cut. Reverse sync is rollback backbone — keep it running during observation.

One-Click Rollback Sequence

Catch up reverse sync, pause reverse consumption.

Write switch TARGET → DUAL (old primary, forward sync continues).

Read switch TARGET → SOURCE.

Stop reverse sync, retain forward incremental for re-cut.

Observe business & DB metrics before cleanup.

Rollback script rehearsed ≥3 times in staging; every step timed, dependencies mapped, failure handling documented.

Monitoring & Emergency

Core Metrics

Stock progress: SUM(rows_done)/total, stall >10min → alert.

Migration rate: rows/min, drop >50% → investigate.

Incremental lag: position timestamp diff (not numeric diff), sustained >5s → alert.

Dual-write failure rate >0.1% → alarm.

Dual-read mismatch rate >0.01% → investigate.

Full diff rate >0.01% → pause.

Target CPU, slow SQL, connections per business thresholds.

Incremental lag is the single gating metric — must be stable ≤1s before cutover.

Dashboard (Grafana)

Four panels: progress (overall bar + shard state), rate (stock rate, incremental TPS, current limit), quality (diff rate trend, diff type distribution, dual-read mismatch TopN), health (source/target QPS, slow SQL, thread pool queue).

Consistency Patrol (Post-Cutover)

@Scheduled(cron = "0 0 3 * * ?")
public void dailyCrcCheck() {
  for (Shard shard : shardPlan) {
    CrcResult src = sourceMapper.crc(shard);
    CrcResult dst = targetMapper.crc(shard);
    if (!src.equals(dst)) diffService.collect(shard);
  }
}

Run ≥2 weeks. Keep reverse sync alive during patrol — otherwise old/new not comparable. If reverse sync stopped, switch to target sampling, backup compare, or business reconciliation. Alert + manual confirm on diff — no auto-repair (runtime diffs often business bugs, not migration bugs).

Key Pitfalls & Lessons

Deep pagination kills migration — use cursor.

No PK or non-monotonic PK — assess early, use unique index or bucketing.

Stock overwrites incremental — guarded upsert with update_time / version or INSERT IGNORE (with warning logging).

Deleted rows resurrected — soft delete, physical cleanup post-migration.

Timezone loss — datetime no TZ, cross-region shift 8h; unify connection TZ, compare via UNIX_TIMESTAMP.

Precision drift — NUMBER(20,6) → DECIMAL(18,4) truncates; float precision mismatch; review type mapping table pre-migration. NULL vs empty string — Oracle empty string = NULL; string handling may persist NULL as empty, breaking app null-checks.

Case sensitivity — MySQL insensitive, PG sensitive; WHERE name='abc' result sets differ.

Auto-increment collision — pre-set target AUTO_INCREMENT = source_max_id + buffer (not step size).

Dual-write swallowed by transaction — always afterCommit async.

Binlog position not persisted — restart replays from old position; idempotency is safety net, position persistence is mandatory.

Large transactions spike lag — 100K-row batch update → huge binlog, consumer stalls; govern large TX pre-migration.

Batch write fails on single conflict — catch BatchUpdateException, degrade to row-by-row with error collection.

CRC false positives — column order, implicit cast, NULL skipped by CONCAT_WS, collation diff; tune diff SQL on small table to zero false positives.

Rollback untested — rehearsal mandatory, not optional.

The essence: decompose one high-risk action into a chain of low-risk, reversible steps. Stock resumable, incremental replayable, validation multi-round convergent, cutover instantly reversible. Each step independently controllable — total risk never piles onto the final switch.