Partial migration causing django_migrations table out of sync during zero-downtime deploy

Direct Answer

No single Django setting makes DDL atomic on MySQL or SQLite. The reliable pattern is to split each logical change into backward‑compatible additive migrations, run migrations as a separate pre‑deploy step under a deployment lock, and write idempotent RunPython operations that check state before applying changes. Migration.atomic=False only removes Django’s transaction wrapper; it does not prevent implicit commits from individual DDL statements on non‑transactional backends.

Backend Behavior Summary

• PostgreSQL: Supports transactional DDL. A migration runs inside a single transaction; django_migrations is written only after commit, guaranteeing sync.
• MySQL (InnoDB): Each DDL statement causes an implicit COMMIT. atomic=False disables the outer transaction but cannot roll back DDL. A crash mid‑migration leaves schema partially applied while the migration record may be missing or present depending on failure point.
• SQLite: Treated as non‑transactional for migrations (even in WAL mode). Same limitations as MySQL.

What Migration.atomic=False Actually Does

It tells Django not to wrap the migration in BEGIN/COMMIT. RunPython then executes without an outer transaction. However, any DDL issued via schema_editor inside RunPython still triggers implicit commits on MySQL/SQLite. You cannot group multiple DDL statements atomically on these backends.

Recommended Zero‑Downtime Pattern

1. Additive‑first migrations: Add nullable columns, create new tables, or add indexes in one migration. Deploy code that works with both old and new schema.
2. Separate deploy and migrate: Run migrate in CI/CD before deploying the new application code. Use a deployment lock so only one instance runs migrations.
3. Idempotent RunPython: For MySQL/SQLite, write RunPython functions that inspect the database (e.g., check if column exists) before executing DDL. This makes re‑running safe after a partial failure.
4. Cleanup migration: After the new code is live, run a second migration to drop old columns, add NOT NULL constraints, or remove legacy tables.

Deployment Coordination Steps

# 1. Acquire lock (PostgreSQL example)
SELECT pg_try_advisory_xact_lock(123456);
# 2. Run migrations
python manage.py migrate --plan   # verify order
python manage.py migrate
# 3. Release lock (automatic at transaction end)
# 4. Deploy new application code
# 5. Run cleanup migration (same lock process)

On MySQL use SELECT GET_LOCK('migrate', 10) / RELEASE_LOCK('migrate'). Ensure health checks tolerate intermediate schema states (e.g., nullable columns present but not yet required).

One Missing Diagnostic Detail

Which database backend(s) are you targeting in production? The locking mechanism and the need for atomic=False differ between PostgreSQL and MySQL/SQLite, so the exact deployment script changes slightly.