返回提示词目录第 1350 页

数据库迁移安全审计

核查生产环境迁移的锁表、版本兼容及恢复路径,输出风险矩阵与分级整改报告。

完整提示词

I want an exhaustive analysis of all database migrations and the operational procedures by which schema and data modifications enter production environments.

Main objective:

> Identify migration changes capable of acquiring exclusive table locks, crashing mixed-version deployments, losing data, resulting in partial migration states, preventing safe rollback, or causing prolonged production outages.

## 1. OBJECTIVE AND NON-GOALS

For every pending and recent migration, prove whether it can run against the real production database, at production size and under production traffic, while old and new application versions coexist, and whether the system can recover if it fails halfway.

Non-goals:

- reviewing the logical schema design (a separate schema audit covers that; mention a design issue only when it makes the migration unsafe)
- rejecting a migration only because its syntax looks dangerous (DROP, ALTER TYPE) without analyzing the rollout
- generic advice such as "always take a backup first" instead of a concrete rollout and recovery plan
- prescribing a specific migration tool

## 2. CONTEXT DISCOVERY

Establish before judging any migration:

```text
Engine and exact version (and managed-service variant):
Migration tool and version:
How migrations run (CI step, dedicated job, app startup, init container, manual):
Transaction wrapping (per migration, per file, none):
Deployment strategy (rolling, blue/green, canary, serverless):
Largest affected tables (rows, bytes, write rate, peak hours):
Long-running transactions and jobs that hold locks:
Replication topology (replicas, logical replication, CDC consumers):
Lock timeout and statement timeout settings used by the migration session:
Backup / PITR status:
```

DDL behavior (which operations rewrite, scan or lock, and which locks) differs between engines and between versions of the same engine. Verify the behavior for the detected version; if it cannot be verified, mark the conclusion **NOT VERIFIED** instead of assuming the worst or the best case.

## 3. EVIDENCE MODEL

```text
A - executed: migration run against a production-sized copy or measured in production (duration, locks, disk, lag)
B - complete path: exact generated SQL + engine/version DDL semantics + table size + deployment order
C - strong static evidence: migration file and table size suggest the risk, but generated SQL or version is unverified
D - inference: plausible risk that needs verification
E - hardening: process or tooling improvement without a current failure path
```

Always base findings on the SQL that actually runs (generated by the tool), not on the ORM or DSL definition.

## 4. FINDING STATUS

- **CONFIRMED** - the lock, rewrite, compatibility or data-loss path is demonstrated (tier A or B).
- **LIKELY** - strong static evidence (tier C).
- **NOT VERIFIED** - depends on engine version, table size or deployment order that could not be established.
- **NOT APPLICABLE** - the operation is safe for this engine, version or table size.
- **CONTROLLED** - the risk is real but the rollout already handles it (expand-contract, online build, batching, timeouts).
- **HARDENING** - improvement without a current failure path (P4).

Do not report a missing best practice as a confirmed defect unless there is a concrete lock, outage, compatibility, data-loss or recovery path.

## 5. FALSE-POSITIVE RULES

The following are **not** findings by themselves:

- A destructive migration (DROP COLUMN, DROP TABLE) is not automatically unsafe if the rollout proves that no running code uses the object and recovery is possible.
- ADD COLUMN, ADD INDEX or ALTER TYPE is not automatically blocking: many engines and versions perform some of these as metadata-only or online operations. Verify.
- A migration on a small or rarely written table is not a lock risk just because the same statement would be dangerous on a large table.
- A missing down migration is not a defect if rollback is designed through forward fixes and backward-compatible schema changes.
- Running migrations as a CI step instead of a dedicated job is not automatically wrong if only one runner can execute them.

## 6. MIGRATION INVENTORY

For every migration:

```text
Migration:
Tool:
Schema/data:
Tables:
Estimated rows:
Potential lock:
Backward compatible:
Rollback:
```

## 7. OPERATION CLASSIFICATION

Classify every statement in every migration for the detected engine and version:

```text
metadata-only      catalog change, near-instant, brief lock
table scan         validates every row (constraint validation, NOT NULL check) but does not rewrite
table rewrite      copies the table (type change, some defaults, column reorder), needs time and double storage
blocking DDL       holds a lock that blocks reads and/or writes for its whole duration
online/concurrent  runs alongside traffic but may take short locks at start and end, and may fail midway
data backfill      UPDATE/INSERT over existing rows; cost scales with row count
destructive        removes data or objects; irreversible without restore
```

Then estimate at production scale: rows and bytes touched, expected duration, and whether it runs inside one transaction. A statement that is metadata-only on one version can be a full rewrite on another.

## 8. APPLIED MIGRATION

Never edit an already-applied migration script as if it were a new change.

## 9. ORDER

Ensure deterministic execution ordering.

## 10. CONCURRENT RUNNERS

Can multiple application instances execute the same migration script concurrently?

## 11. MIGRATION OWNERSHIP

Exactly one runner may execute a migration:

- Where does the migration run: CI job, release job, application startup, Kubernetes init container, serverless cold start?
- If it runs at application startup, what prevents ten replicas from starting it at the same time?
- Does the tool take a lock (for example an advisory lock or a lock table), and is that lock released if the runner is killed?
- What happens if the runner loses its connection mid-migration: does the tool record partial state?
- Who is allowed to run migrations manually against production, and are those runs recorded in the migration history?

## 12. LOCK TABLE

Evaluate DDL locking semantics for the specific database engine and version.

## 13. LOCK ACQUISITION AND LOCK QUEUE

Even a "fast" DDL statement can cause an outage:

```text
long-running report query holds a shared lock
↓
ALTER TABLE waits for an exclusive lock
↓
on engines where waiting DDL blocks later requests, every new query on the table queues behind it
↓
connection pool fills up
↓
application-wide outage, although the DDL itself would take milliseconds
```

For each DDL determine:

- which lock mode it needs, at the start, during and at the end of the operation
- whether a waiting DDL blocks new readers or writers on this engine and version
- which transactions or jobs can hold conflicting locks for a long time (reports, backups, long batch jobs, idle-in-transaction sessions)
- whether the migration sets a short lock timeout and retries, rather than waiting indefinitely

## 14. ADD COLUMN

Usually relatively safe, but default value assignment and backfill semantics vary widely across engines.

## 15. ADD NOT NULL

Can trigger full table scans, table rewrites, or long-held exclusive locks.

## 16. DEFAULT

Evaluate volatility functions and engine version behaviors.

## 17. DROP COLUMN

Breaks mixed-version deployments where older instances still query the dropped column.

## 18. RENAME

Utilize expand-contract phased deployment patterns.

## 19. TYPE CHANGE

Carries data rewrite, lock acquisition, and truncation risks.

## 20. ENUM CHANGE

Evaluate backward compatibility across running application fleets.

## 21. FK ADD

Validation over large tables can hold extensive shared locks.

## 22. UNIQUE ADD

Fails if existing duplicate rows are present.

## 23. CHECK CONSTRAINT

Fails if existing invalid rows violate the constraint.

## 24. INDEX CREATE

Lock duration analysis.

## 25. ONLINE/CONCURRENT

Deploy online or concurrent index creation where supported and needed.

## 26. BACKFILL

Execute data backfills in separate, bounded batches.

## 27. HUGE UPDATE

A single massive update transaction generates immense WAL/transaction log volume and holds locks indefinitely.

## 28. BATCH SIZE

Tune batch sizes appropriately.

## 29. PAUSE/THROTTLE

Introduce pauses or throttling between batches if necessary.

## 30. RESUMABILITY

Long backfills must be architected to survive interruptions.

## 31. PROGRESS

Track migration progress via persistent cursors or checkpoints.

## 32. IDEMPOTENT BACKFILL

Must be safe to rerun repeatedly.

## 33. BACKFILL CONTRACT

Every data backfill must answer:

```text
Selection:        how are the next rows chosen (keyset on a stable key, not OFFSET)?
Chunk size:       rows per batch and expected time per batch at peak load
Transaction:      one transaction per batch, never one for the whole table
Checkpoint:       where progress is stored, so a restart continues instead of starting over
Resume:           what happens after a crash, deploy or manual stop
Throttle:         pause between batches, and a way to slow down when replica lag or load grows
Idempotency:      running a batch twice produces the same result
Concurrent writes: how rows changed by the application during the backfill are handled
Verification:     counts, checksums or sampling that prove completion
Ownership:        which process runs it, and how it is stopped
```

A backfill that runs inside the deployment transaction or blocks the release until it finishes on a large table is a finding.

## 34. DUAL WRITE

Mitigate data consistency hazards during dual-write phases.

## 35. DATA COPY

Verify row counts, checksums, or sampling comparisons.

## 36. OLD/NEW APP

Compatibility matrix evaluation.

## 37. ROLLBACK

Verify the old application binary operates correctly after the migration runs.

## 38. NEW DATA

Verify legacy application code can gracefully handle new enum values or data states produced by the new code.

## 39. DELETE OLD COLUMN

Only drop legacy columns after older code instances are fully decommissioned.

## 40. CONTRACT PHASE

Execute the contract phase as a distinct, subsequent release.

## 41. ROLLBACK REALITY

Separate three kinds of rollback:

- **code rollback** - redeploying the previous application version; safe only if the old code works against the current schema and data
- **schema rollback** - a down migration; often untested, may lock or rewrite again, and cannot restore dropped data
- **data rollback** - restoring data; usually means PITR or a restore, which also discards every valid write made since

For every migration state explicitly:

- Is the change reversible without data loss? If not, what makes it acceptable (verified backup, expand-contract, data kept in another column)?
- Has the down migration ever been executed?
- After new data has been written in the new shape, can the old code still read it?
- Backup is not rollback: restoring means downtime and losing newer writes. It is a disaster-recovery plan, not a deployment rollback.

## 42. MIGRATION FAILURE

Handling failure midway through execution.

## 43. TRANSACTIONAL DDL

Engine-specific transactional DDL support.

## 44. NON-TRANSACTIONAL DDL

Mitigate half-applied or orphaned schema modifications.

## 45. MIGRATION RETRY

Is the failed migration safely retryable?

## 46. TIMEOUT

Prevent indefinitely blocked deployments.

## 47. LOCK TIMEOUT

Configure lock timeouts to avoid hanging migrations.

## 48. STATEMENT TIMEOUT

Configure statement timeouts on long DDL executions.

## 49. REPLICATION LAG

Heavy schema operations can cause severe replication lag on follower nodes.

## 50. DISK GROWTH

Index and table rewrites demand temporary storage headroom.

## 51. DOUBLE STORAGE

Building new indexes or rewritten tables requires substantial temporary storage capacity.

## 52. BACKUP

Backups represent a disaster recovery plan, not an excuse for executing unsafe migrations.

## 53. PITR

Verify point-in-time recovery is active prior to high-risk migrations.

## 54. DEPLOY ORDER

Determine whether migrations must execute before or after application binary rollout.

## 55. FEATURE FLAG

Decouple schema availability from functional feature activation.

## 56. MIGRATION JOB

Execute migrations via a dedicated singleton runner.

## 57. ORM AUTO-SYNC

Extremely dangerous in production if the ORM automatically mutates physical schemas.

## 58. `synchronize=true`

High-priority vulnerability check in ORM frameworks (e.g., TypeORM).

## 59. PRISMA/DRIZZLE/ALEMBIC/FLYWAY ETC.

Understand actual tool mechanics, locking behaviors, and transaction wrappers.

## 60. DRIFT

Identify drift between migration tracking tables and the live physical schema.

## 61. MANUAL HOTFIX

Hotfixes applied directly to production that are missing from migration history.

## 62. SHADOW DATABASE

Assess operational risks if the migration tooling utilizes a shadow database.

## 63. GENERATED SQL

Review the raw generated SQL statements, not merely the ORM DSL definitions.

## 64. PRODUCTION VOLUME

Validate migrations against production-scale cloned datasets or statistical profiles where feasible.

## 65. MIGRATION BENCHMARK

Benchmark lock acquisition durations and total execution times.

## 66. MIGRATION RISK MATRIX

| Migration | Operation class | Table size | Lock mode / duration | Disk / WAL growth | Replica impact | Old app compatible | Reversible | Status |
|---|---|---|---|---|---|---|---|---|

## 67. FINDING FORMAT

```text
ID:
Severity:
Status:
Evidence tier:
Scope (migration, table, statement):
Trigger (when and how it runs):
DB engine / version:
Operation class:
Current behavior (generated SQL, lock mode, rewrite/scan):
Table size and write rate:
Expected invariant (no blocking, mixed-version safety, no data loss):
Failure path:
Impact (lock duration, outage, data loss, stuck deploy):
Blast radius:
Evidence:
Root cause:
Mixed-version risk:
Rollback / recovery:
Remediation (safer rollout steps):
Verification (how to prove the new rollout is safe):
Regression risk:
```

## 68. SEVERITY

- **P0** - irreversible data loss or corruption in production, or a migration that takes down the whole system with no fast recovery.
- **P1** - a proven blocking lock, table rewrite or mixed-version break on a critical table that causes a production outage or failed deployment; a destructive change without a working recovery path.
- **P2** - a material risk on important tables (long locks off-peak, significant replica lag, unsafe backfill design, untested rollback for a risky change).
- **P3** - limited risk: small tables, missing timeouts on low-traffic paths, minor tooling drift.
- **P4** - hardening: process improvements, better observability, documentation of rollout steps.

## 69. OUTPUT

`DATABASE_MIGRATION_SAFETY_AUDIT.md`

## 70. SECOND PASS

For every destructive, large-scale or blocking migration simulate:

- the old application binary running against the new schema (mixed-version period)
- the new application binary running against the transitional schema
- a long-running transaction holding a conflicting lock when the migration starts
- the migration failing midway through execution, and the state it leaves behind
- a retry of the migration after that failure
- an application rollback after new data has been written
- maximum anticipated production row counts and write rates
- replica replication lag and CDC / logical replication consumers
- disk space, temporary space and WAL / redo / binlog headroom on the primary and replicas
- two runners attempting to execute the migration at the same time

Then try to disprove each finding: is the operation actually metadata-only on this version? Is the table actually small? Does the rollout already use expand-contract?

## 71. FINAL QUALITY GATE

Before returning the report, verify that:

- engine and version were identified and DDL semantics were checked for that version
- conclusions are based on the SQL that actually runs, not only the ORM definition
- every migration was classified (metadata-only, scan, rewrite, blocking, online, backfill, destructive)
- production row counts, bytes and write rates were considered for large tables
- lock acquisition waits, lock queue effects and timeouts were analyzed
- disk, temporary space, WAL and replica headroom were checked for rewrites and index builds
- mixed-version compatibility (old app / new schema, new app / old schema) was checked for every rollout
- backfills have chunking, checkpoints, resume, throttling, idempotency and verification
- only one runner can execute each migration
- rollback is described separately for code, schema and data, and irreversible changes are identified
- statuses and evidence tiers are applied consistently

# FINAL RULE

Looking for issues such as:

```text
migration:
ALTER TABLE users ADD COLUMN country TEXT NOT NULL DEFAULT 'RS'

↓
table has 400M rows
↓
actual DB/version rewrites/validates table
↓
migration runs inside deployment
↓
exclusive lock blocks traffic
↓
deployment outage
```

Other failure chains to look for:

```text
migration: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY ...
↓
constraint is validated immediately
↓
validation scans 300M rows while holding a lock that blocks writes to orders
↓
checkout requests time out for the duration of the scan
```

```text
migrations run at application startup
↓
rolling deploy starts 12 replicas at once
↓
no migration lock: two replicas run the same backfill concurrently
↓
duplicate rows and a deadlock
↓
half the fleet crashes on boot; the other half runs against a half-migrated schema
```

```text
column renamed in one release (no expand-contract)
↓
new version writes to the new name
↓
deploy fails health checks and is rolled back
↓
old version reads the old column name, which no longer exists
↓
rollback itself causes the outage
```

分类与标签

编程开发、数据库迁移、锁表风险、版本兼容、回滚恢复

前往 AI 图片创作