Transactional vs Non-Transactional Databases

The single most consequential difference between PostgreSQL and MySQL for migrations is what happens when a multi-statement DDL change fails halfway through. PostgreSQL wraps DDL inside the same transaction machinery as data: a BEGIN ... ALTER ... ALTER ... ROLLBACK leaves the schema exactly as it started. MySQL does the opposite — every DDL statement triggers an implicit commit before and after it runs, so a migration that fails on its third ALTER leaves the first two permanently applied and unrollback-able. That one fact reshapes how you structure migrations, how you recover from a failed deploy, and how you write the rollback contract. This page serves backend engineers and DBAs who must guarantee that a failed migration leaves a recoverable database, not a half-changed one.

This split sits underneath everything else in Database Migration Fundamentals. It dictates why idempotent script design is mandatory on MySQL rather than merely nice, it changes what the migration tool’s lock and rollback behavior can actually deliver, and it raises the stakes on environment parity because a transactional staging engine will hide a non-transactional production failure. Below, the focus is the engine mechanism and the recovery patterns it forces.

Concept & Mechanism

PostgreSQL: DDL is transactional. In PostgreSQL, ALTER TABLE, CREATE TABLE, DROP, and most other DDL participate in the surrounding transaction. Multiple DDL statements between BEGIN and COMMIT are atomic — they all succeed or the whole set rolls back, including the catalog changes. The recovery model is therefore simple: on failure, the schema is exactly as it was, and you retry. The one important exception is CREATE INDEX CONCURRENTLY, which builds the index without an ACCESS EXCLUSIVE lock and cannot run inside a transaction block; it commits incrementally and, if it fails, leaves an INVALID index you must drop and rebuild.

MySQL: DDL forces an implicit commit. In MySQL (InnoDB, 8.0), every DDL statement causes an implicit commit of any open transaction immediately before it executes, and the DDL itself auto-commits. Wrapping ALTER TABLE in BEGIN ... ROLLBACK is a no-op for the schema change — there is nothing to roll back. A migration of several ALTER statements is therefore a sequence of independent, individually-committed operations. If the fourth fails, the first three are permanent. This is why MySQL migrations must be decomposed into independently-recoverable steps and made idempotent, the subject of handling non-transactional DDL in MySQL migrations.

The lock dimension. Both engines take metadata locks during DDL, but the failure interaction differs. PostgreSQL holds locks until COMMIT, so a long transactional migration blocks longer but releases everything atomically. MySQL’s online DDL (ALGORITHM=INPLACE, LOCK=NONE) releases per statement, which is gentler on availability but means each statement is its own commit boundary — reinforcing that partial failure is the default risk, not the exception. The deeper trap of statements that appear online but silently force an implicit commit is covered in avoiding implicit commits in MySQL DDL migrations.

Prerequisites & Decision Criteria

Classify the engine before you write a line of DDL, because the engine decides whether atomic rollback is even available to you. Use this checklist:

• You have confirmed the target engine and storage engine (InnoDB vs anything else) for production, not just dev.
• For MySQL, every migration is decomposed into independently-committable, idempotent steps.
• For PostgreSQL, multi-statement DDL that must be atomic is wrapped in one explicit transaction.
• Any CREATE INDEX CONCURRENTLY (PG) or ALGORITHM=INPLACE (MySQL) statement is isolated into its own step, never inside an atomic block.
• A lock_timeout (PG) or lock_wait_timeout (MySQL) is set so a blocked migration fails fast instead of stalling traffic.
• Staging runs the exact same engine and version as production so a non-transactional failure cannot hide.

The decision is rarely “which engine” — it is usually given — but always “what recovery contract does this engine force on me.” On MySQL the answer is always idempotency; on PostgreSQL it is atomic transactions plus careful handling of the concurrent-index exception.

Step-by-Step Procedure

The procedure differs by engine. Run the branch that matches production.

1. Confirm the engine’s DDL model on the real target. Do not assume from the dev container.

-- MySQL 8.0 · read-only · run against production replica to confirm storage engine.
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND ENGINE <> 'InnoDB';
-- Any row here is a table where online DDL guarantees do NOT hold.

Verify before proceeding: the query returns no rows; every target table is InnoDB.

2a. PostgreSQL — wrap atomic DDL in one transaction. Set a lock timeout so the migration fails fast rather than queuing behind traffic.

-- PostgreSQL · migration role · safe in a transaction; run during a low-write window.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS fulfillment_status VARCHAR(32) DEFAULT 'pending';
COMMIT;

Verify before proceeding: the column exists and BEGIN ... ROLLBACK of an equivalent change leaves the table unmodified in a scratch database.

2b. PostgreSQL — build the index outside the transaction. This step cannot be atomic; isolate it.

-- PostgreSQL · migration role · MUST run outside any transaction block; safe online but slow.
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_fulfillment ON orders (fulfillment_status);

Verify before proceeding: \d orders shows the index as valid, not INVALID.

3. MySQL — apply each step independently and idempotently. Because each statement self-commits, design so a re-run after a mid-sequence failure is safe.

-- MySQL 8.0 · migration role · each statement implicitly commits; safe to re-run.
-- Step A: add column online (its own commit boundary).
ALTER TABLE inventory ADD COLUMN IF NOT EXISTS sku_v2 VARCHAR(64) NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

-- MySQL 8.0 · application loop · run in batches; safe to re-run after partial completion.
-- Step B: backfill by primary-key range, NOT LIMIT/OFFSET, until zero rows remain.
UPDATE inventory SET sku_v2 = CONCAT('SKU-', id)
WHERE sku_v2 IS NULL AND id BETWEEN @lo AND @hi;

Verify before proceeding: killing the backfill mid-run and restarting it produces the same final state with no duplicate or skipped rows.

Verification & Observability

After a migration, confirm the schema matches intent and no lock is still held. The queries differ by engine.

-- PostgreSQL · read-only · safe on production.
-- Any session still blocked on a lock from the migration?
SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- Catch a failed concurrent index left behind.
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

-- MySQL 8.0 · read-only · run from a second session.
-- Metadata locks held on the migrated table reveal a stuck DDL.
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = DATABASE();
SHOW ENGINE INNODB STATUS\G

For MySQL specifically, verify that each intended step actually committed, since there is no transaction to confirm atomicity:

-- MySQL 8.0 · read-only · confirm every step of a multi-statement migration landed.
SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'inventory' AND COLUMN_NAME = 'sku_v2';

Rollback Path

The rollback contract is engine-specific because the failure leaves different states.

PostgreSQL. If the migration is still inside its transaction, ROLLBACK is the entire rollback — the schema returns to its starting state with no residue. If it already committed, apply the inverse DDL inside a new transaction, under the same lock_timeout:

-- PostgreSQL · migration role · safe only after every app version reading the column is retired.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders DROP COLUMN IF EXISTS fulfillment_status;
COMMIT;

MySQL. There is no transactional rollback. Reversal is a forward, idempotent inverse-DDL sequence, applied step by step in the reverse order of the original — and only the steps that actually committed:

-- MySQL 8.0 · migration role · each line self-commits; run only steps that were applied.
-- Reverse order: drop the index first if one was added, then the column.
ALTER TABLE inventory DROP COLUMN IF EXISTS sku_v2, ALGORITHM=INPLACE, LOCK=NONE;

Safe conditions for either engine: no deployed application version still reads or writes the structure being removed, the inverse DDL is itself online/low-lock, and data the migration produced is not lost (if a backfill ran, dropping the column is destructive — prefer disabling the new path over destroying data). When data loss is possible, restore from a pre-migration snapshot instead of running the inverse DDL.

Common Errors & Fixes

ERROR 1205 (HY000): Lock wait timeout exceeded (MySQL). Root cause: a long-running transaction or query holds a metadata lock the DDL needs. Fix: set SET lock_wait_timeout so the migration fails fast, run during a low-write window, and confirm no long transaction is open via performance_schema.metadata_locks before retrying.

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block (PostgreSQL). Root cause: the concurrent index statement was wrapped in BEGIN/COMMIT, or the migration tool runs every statement in a transaction by default. Fix: move it to its own migration marked as non-transactional (Flyway -- executeInTransaction=false, or the runner’s equivalent) so it executes standalone.

Half-applied MySQL migration after a failure. Root cause: a multi-statement DDL migration failed mid-sequence and the implicit commits left earlier statements applied. Fix: make every step idempotent with IF NOT EXISTS/IF EXISTS and simply re-run; the migration must converge to the same state from any partial point, as detailed in avoiding implicit commits in MySQL DDL migrations.

Invalid index after a failed CREATE INDEX CONCURRENTLY (PostgreSQL). Root cause: the concurrent build was interrupted, leaving an INVALID index that still consumes space and is not used by the planner. Fix: DROP INDEX CONCURRENTLY the invalid index, then re-issue the build; check pg_index.indisvalid to find any others.

Child Page Index

This section covers the MySQL-specific hazards that the transactional/non-transactional split creates. Handling Non-Transactional DDL in MySQL Migrations covers decomposing migrations into independently-recoverable steps, explicit lock management with lock_wait_timeout, and chunked backfills that survive partial failure. Avoiding Implicit Commits in MySQL DDL Migrations covers the subtler trap — statements that silently force an implicit commit and break the illusion of a wrapped transaction — and how to structure migrations so an implicit commit never leaves you stranded. Both extend the broader Database Migration Fundamentals and lean on idempotent script design, which is mandatory rather than optional on non-transactional engines.

Frequently Asked Questions

Can I wrap a MySQL migration in a transaction to get atomic rollback? No. In MySQL, DDL statements trigger an implicit commit before and after they run, so BEGIN ... ROLLBACK has nothing to undo for the schema change. The only durable safety on MySQL is to decompose the migration into independently-committable, idempotent steps that converge to the same state on re-run.

Why can’t PostgreSQL’s CREATE INDEX CONCURRENTLY run inside my transactional migration? Because it builds the index in multiple phases with intermediate commits to avoid taking an exclusive lock, which is fundamentally incompatible with a single enclosing transaction. Isolate it into its own non-transactional migration step; if it fails, drop the resulting invalid index and retry.

How does this difference change my rollback contract? On PostgreSQL, rollback of an uncommitted migration is a single ROLLBACK and the schema is pristine. On MySQL, there is no automatic undo — rollback is a forward inverse-DDL sequence applied only to the steps that committed, and if a backfill ran, you must prefer disabling the new path over a destructive drop, or restore from a snapshot.