Does CREATE TABLE inside an open transaction commit the work already done by that transaction?
answer
- schema changes are not ordinary DML
- some engines cannot undo them
- an invisible COMMIT before and after
- migration left half-applied after a failure
basics
~20 sIt depends on the engine. MySQL and Oracle commit implicitly before and after DDL, so a later ROLLBACK undoes nothing that came earlier. PostgreSQL, SQL Server and SQLite treat DDL as transactional and roll it back with everything else.
solid answer
~50 sThis is one of the few places where a transaction ends without you writing `COMMIT`. On MySQL and Oracle, DDL such as `CREATE TABLE`, `ALTER TABLE` or `DROP TABLE` causes an **implicit commit** of the transaction in progress — and commits again afterwards — so a `ROLLBACK` issued later finds nothing to undo and the earlier DML is already permanent. PostgreSQL, SQL Server and SQLite support **transactional DDL**: the schema change lives inside your transaction and `ROLLBACK` removes both the new table and the rows you inserted before it. The practical consequence is in migrations: on an implicit-commit engine a migration that fails halfway leaves the schema partially applied with no automatic recovery, so each step must be small, individually recorded, and safe to re-run. Never mix DML and DDL in one transaction and assume the pair is atomic — check what your engine does first.
code
sql · 5 lines-- MySQL: CREATE TABLE commits implicitly, so ROLLBACK undoes nothing
START TRANSACTION;
INSERT INTO audit_log (msg) VALUES ('migration start');
CREATE TABLE staging_orders (id INT PRIMARY KEY); -- implicit COMMIT here
ROLLBACK; -- the audit_log row and the new table both remaingo deeper
Know that a schema change is not just another statement: on some engines it silently ends the transaction you are in, so ROLLBACK afterwards may undo nothing.
Name which engines commit implicitly around DDL and which support transactional DDL, and show the two-line script whose outcome differs between them.
Demonstrate how this shapes migration practice: small recorded steps, re-runnable statements, DML separated from DDL, and a forward-repair plan where rollback does not exist.
Own the standard the whole organisation migrates by — one that is correct on the weakest engine in the estate, so a half-applied change is detectable and recoverable rather than a manual archaeology exercise.
## Two families of engines Everything else in a transaction obeys the rule "nothing is permanent until `COMMIT`". DDL is where that rule breaks, and engines split into two camps. **Implicit-commit engines.** MySQL and Oracle Database end the current transaction when you issue DDL. MySQL documents a list of *statements that cause an implicit commit*: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX`, `DROP INDEX` and friends. The commit happens **before** the DDL runs and again **after** it, so the DDL statement effectively sits in its own transaction with your earlier work already flushed permanently ahead of it. Oracle behaves the same way: a DDL statement commits the transaction in progress. **Transactional-DDL engines.** PostgreSQL, SQL Server and SQLite let schema changes participate in an ordinary transaction. You can `BEGIN`, create a table, insert into it, change a column type, decide the whole thing was wrong, and `ROLLBACK` — the table never existed as far as any other session is concerned. ## The failure this produces ```sql -- MySQL: the CREATE TABLE commits everything before it START TRANSACTION; INSERT INTO audit_log (msg) VALUES ('migration start'); CREATE TABLE staging_orders (id INT PRIMARY KEY); -- implicit COMMIT ROLLBACK; -- undoes nothing: the audit row and the table both survive ``` Run the identical script on PostgreSQL and the `ROLLBACK` removes both the audit row and the table. Same SQL, opposite outcome — which is why "it worked on my local Postgres" is such a common preface to a broken MySQL migration. ## Why migration tooling cares so much A migration is a sequence of schema and data changes that must move the database from version N to version N+1. On a transactional-DDL engine, a tool can wrap the whole version in one transaction: if step 4 of 6 fails, steps 1–3 disappear and the database is still cleanly at version N. On an implicit-commit engine that safety net does not exist. Steps 1–3 are permanently applied, step 4 failed, and the database is in a state no version number describes. The disciplines that follow are: - **One change per step, recorded individually**, so the tool knows exactly how far it got. - **Write steps to be re-runnable** where the engine lets you (`DROP TABLE IF EXISTS`, `CREATE TABLE IF NOT EXISTS`, checking the catalog first), so a re-run after a partial failure is not a second failure. - **Do not mix DML and DDL in one step.** A backfill next to an `ALTER TABLE` is the exact shape that leaves half-migrated data behind. - **Have a written forward-repair path**, because there is no rollback to fall back on. ## Even transactional DDL has exceptions Supporting DDL in transactions is not the same as supporting *every* statement in one. PostgreSQL, for example, refuses to run `CREATE DATABASE`, `DROP DATABASE`, `VACUUM` and `CREATE INDEX CONCURRENTLY` inside a transaction block — they error out telling you so. These are operations that touch things outside the transactional storage the engine can roll back, or that deliberately need to commit intermediate state. So the accurate claim is "most DDL is transactional here", not "everything is". ## Locking is a separate concern A transactional `ALTER TABLE` still takes locks, and holding a schema change open inside a long transaction blocks other sessions for as long as the transaction lives. That is an execution concern rather than a language one, but it is the reason experienced engineers keep DDL transactions short even on engines that support them: the ability to roll back is not permission to leave the transaction open. ## How to answer this in an interview Say the rule engine-by-engine, do not generalise. "MySQL and Oracle commit implicitly around DDL, so DDL and DML in one transaction is not atomic there; PostgreSQL, SQL Server and SQLite give you transactional DDL, so it is. I write migrations assuming the weaker guarantee — small steps, each recorded, each re-runnable — because that is correct on both." That answer shows you know the divergence, know which side each major engine is on, and have a portable habit that survives being wrong about a specific version.
- How do you write migrations for an engine that commits implicitly around DDL?Assume no rollback. Split the change into the smallest steps that are individually meaningful, record each applied step so the tool knows where it stopped, make each step re-runnable (`IF EXISTS` / catalog checks), and never pair a data backfill with a schema change in one step. Keep a written forward-repair path instead of relying on rollback.
- Does transactional DDL mean every statement can run inside a transaction block?No. PostgreSQL, for instance, rejects `CREATE DATABASE`, `DROP DATABASE`, `VACUUM` and `CREATE INDEX CONCURRENTLY` inside a transaction block and tells you so in the error. Those operate outside what the transaction can undo or deliberately need intermediate commits. The correct claim is that most DDL is transactional there, not all statements.
- On an engine with transactional DDL, is it a good idea to keep a long transaction containing an ALTER TABLE open?No. Being able to roll it back does not make it free: the schema change holds locks for the whole life of the transaction, so other sessions touching that table wait. Keep DDL transactions short and do the surrounding data work separately.
saying these in an interview costs you the question
- Claims DDL is always rollback-safe because it runs inside BEGIN
- Says no database can roll back a CREATE TABLE
- Mixes a backfill and an ALTER TABLE assuming they are atomic
- Thinks the implicit commit happens only after the DDL, not before
- Assumes transactional DDL means every statement works inside a transaction