Java.DBMigrationTools.How do migration tools handle transactional vs non-transactional statements?

This is a classic senior trap question. The key is: the database decides, migration tools just adapt.

---

Core rule (memorize this)

Migration tools run each migration inside a transaction only if the database supports transactions for the statements being executed.

Everything else follows from that.

---

How tools decide (high level)

Tool checks DB capabilities

Tool inspects migration type / configuration

Tool either:

• wraps migration in a transaction, or
• runs it auto-commit / non-transactional

On failure:

• transactional → rollback
• non-transactional → partial state remains

Transactional migrations (safe case)

When they work

• Database supports transactional DDL
• Statements are transaction-safe

Examples (PostgreSQL):

ALTER TABLE users ADD COLUMN age INT;
CREATE TABLE orders (...);

What happens

• Tool opens a transaction
• Executes migration
• Commits on success
• Rolls back fully on failure

✅ Clean
✅ Atomic
✅ Retry-safe

---

Non-transactional migrations (danger zone)

When they happen

• DB does not support transactional DDL
• Or statement explicitly forbids it

Common examples

• MySQL (many DDLs)
• Oracle (most DDL auto-commits)
• PostgreSQL: CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE (older versions)

What happens

• Statements auto-commit
• Partial execution is possible
• Failure leaves DB in intermediate state

🚨 This is why migrations are scary.

How Flyway handles this

Flyway behavior

• By default: one transaction per migration
• If migration contains non-transactional statements:
  • transaction is disabled
  • or migration fails (depending on DB + config)

Example config:

flyway.mixed=true

This allows:

ALTER TABLE users ADD COLUMN age INT;
CREATE INDEX CONCURRENTLY idx_users_age ON users(age);

⚠️ Mixed transactional + non-transactional = partial commit risk

How Liquibase handles this

Liquibase behavior

• Changeset-level control

<changeSet id="add-index" author="stanley" runInTransaction="false">
    <sql>
        CREATE INDEX CONCURRENTLY idx_users_age ON users(age);
    </sql>
</changeSet>

Liquibase lets you:

• opt out explicitly
• isolate dangerous statements
• keep other changes transactional

This is one reason enterprises like Liquibase.

Failure behavior (this matters in interviews)

Scenario	Result
Transactional migration fails	Full rollback
Non-transactional fails	Partial DB state
Tool crash mid-DDL	DB-dependent
App restart	Migration not retried automatically

👉 No tool can magically undo non-transactional DDL.

Senior best practices

✅ Isolate dangerous statements

• One migration = one risky operation
• No mixing with safe DDL

✅ Avoid non-transactional ops during peak traffic

• Schedule off-hours
• Or use online-safe alternatives

✅ Design migrations to be restart-safe

• IF NOT EXISTS
• idempotent patterns where possible

✅ Prefer forward-only fixes

• Never assume rollback will save you

Interview-ready answer (2–3 sentences)

Migration tools wrap migrations in transactions when the database supports transactional DDL, allowing full rollback on failure.
For non-transactional statements, the tool either disables transactions or requires explicit configuration, meaning failures can leave partial state.
That’s why risky operations are isolated and handled with forward-only recovery strategies.

One-line senior takeaway

Transactions protect migrations only when the database allows it — tools can’t override database semantics.