# Transactional DDL in PostgreSQL, MySQL and CockroachDB: Which Failed Migrations Roll Back?

> If the third statement of a migration fails, PostgreSQL, SQL Server and SQLite undo the whole migration, and MySQL and CockroachDB do not. MySQL commits before (and after) every DDL statement, so earlier statements and any data changes made before them stay. CockroachDB does the same by default through `autocommit_before_ddl`, and with that setting off it can still commit the data changes while undoing the schema change.

Source: https://schemity.com/blog/transactional-ddl-postgres-mysql-cockroachdb/

Whether a failed migration rolls back depends on the database as much as on the `BEGIN` around it. With the migration in one transaction and a rollback on error, PostgreSQL, SQL Server and SQLite undo every statement when the third one fails. MySQL and CockroachDB keep the first two, and both also keep any data change the same transaction made before them. On CockroachDB, turning off the setting that causes this can still leave the data change committed while the schema change is undone.

The migration below was run on each database as it comes out of the box, against a `customers` table with two rows, one of them with a `NULL` email:

```sql
BEGIN;
UPDATE customers SET credit = 100 WHERE id = 1;           -- a data fix
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);       -- statement 1
CREATE INDEX customers_phone_idx ON customers (phone);    -- statement 2
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;    -- statement 3 fails
ROLLBACK;
```

Statement 3 was always going to fail: one row has no email. The versions were PostgreSQL 18.3, MySQL 8.4.11 and 9.7.4, CockroachDB v26.3.4, SQL Server 2022 (CU26) and SQLite 3.54.0, each in a throwaway container or in memory. MySQL spells the `NOT NULL` change `MODIFY email VARCHAR(255) NOT NULL`. SQL Server needs `BEGIN TRANSACTION`, `ADD phone NVARCHAR(30) NULL` without the word `COLUMN`, and `ALTER COLUMN email NVARCHAR(255) NOT NULL`. SQLite cannot add `NOT NULL` to an existing column, so its third statement was a `UNIQUE` index that the data breaks.

## What happens when a migration fails halfway?

On PostgreSQL, SQL Server and SQLite nothing is left behind; on MySQL and CockroachDB the statements before the failure stay applied.

| Database | After statement 3 fails | Why |
| --- | --- | --- |
| PostgreSQL 18.3 | Nothing changed: no `phone`, no index, `credit` unchanged | DDL runs inside the transaction |
| SQL Server 2022 | Nothing changed | DDL runs inside the transaction |
| SQLite 3.54 | Nothing changed | DDL runs inside the transaction |
| MySQL 8.4 and 9.7 | `phone` and its index exist, `credit = 100` | Every DDL statement commits the open transaction first |
| CockroachDB 26.3, default | `phone` and its index exist, `credit = 100` | `autocommit_before_ddl` commits before each schema change |
| CockroachDB 26.3, setting off, table unlocked | The new column was not added, but the `UPDATE` stayed | The schema change failed after the transaction committed |

The MySQL and CockroachDB rows are the ones to look at closely. The `ROLLBACK` at the end ran without an error on both and changed nothing. The data fix at the top, which the developer wrote inside `BEGIN` because they wanted it undone on failure, was committed the moment the first `ALTER TABLE` arrived.

One condition sits under the first three rows: something has to roll back. After a failed statement, PostgreSQL refuses everything except `ROLLBACK`, and turns a `COMMIT` into a rollback. SQLite undoes only the failed statement and leaves the transaction open, so a script that sends `COMMIT` next keeps statements 1 and 2. Migration tools, Schemity included, roll back on the first error, which is the case the table shows.

A schema left halfway is worse than a migration that did nothing. The migration tool records the migration as failed. Running it again then fails on statement 1 instead of statement 3, because `phone` already exists, so someone has to work out by hand which statements ran.

## Why does PostgreSQL roll back DDL, and what does that cost?

PostgreSQL rolls back DDL because `ALTER TABLE` and `CREATE INDEX` run inside the transaction like any `UPDATE`, and the price is that their locks last until `COMMIT`.

Inside the migration above, just before statement 3, `pg_locks` held two locks on `customers`: `AccessExclusiveLock` from the `ADD COLUMN` and `ShareLock` from the `CREATE INDEX`. `ACCESS EXCLUSIVE` blocks every read and write on the table, and it is not released when the `ADD COLUMN` finishes. It is released when the transaction ends. A migration that adds a column and then backfills a million rows in the same transaction blocks the table for the whole backfill. That is why [adding a NOT NULL column to a large table](https://schemity.com/blog/postgres-add-not-null-column-large-table/) splits the work into separate steps with a `lock_timeout` on each.

Two statements do not fit inside the rule, and PostgreSQL 18.3 rejected or limited both in a transaction:

- `CREATE INDEX CONCURRENTLY` fails with `ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block`. The [`CREATE INDEX` page](https://www.postgresql.org/docs/current/sql-createindex.html) says it directly: "a regular `CREATE INDEX` command can be performed within a transaction block, but `CREATE INDEX CONCURRENTLY` cannot." It needs a migration of its own, with the transaction turned off for that migration.
- `ALTER TYPE tier ADD VALUE 'team'` runs, but `SELECT 'team'::tier` in the same transaction fails with `unsafe use of new value "team" of enum type tier`. The [`ALTER TYPE` notes](https://www.postgresql.org/docs/current/sql-altertype.html) say the new value "cannot be used until after the transaction has been committed", so a migration that adds an enum value and backfills rows with it in one transaction fails.

## Can you roll back a schema change in MySQL?

No: MySQL commits the open transaction before every DDL statement, and again after it, so a `ROLLBACK` after an `ALTER TABLE` has nothing left to undo.

The [implicit commit page](https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html) lists `ALTER TABLE`, `CREATE INDEX` and `CREATE TABLE` among the statements that "implicitly end any transaction active in the current session, as if you had done a `COMMIT` before executing the statement". In the test, that is exactly what happened on 8.4.11 and on 9.7.4. Statement 3 failed with `ERROR 1138 (22004): Invalid use of NULL value`. The `ROLLBACK` succeeded. And `customers` still had `phone`, `customers_phone_idx` and `credit = 100` on row 1.

MySQL 8.0 introduced atomic DDL, which is easy to read as the fix, and the [atomic DDL page](https://dev.mysql.com/doc/refman/8.4/en/atomic-ddl.html) says it is not: "Atomic DDL is not transactional DDL." It makes one statement all-or-nothing. A crash in the middle of an `ALTER TABLE` no longer leaves the data dictionary and the table files disagreeing. A migration of five statements is still five commits.

So on MySQL, the unit of rollback is the statement. Statement-at-a-time behaviour also explains why a MySQL change can be slow and still safe: how long each statement holds the table is a separate question, measured in [which MySQL ALTER TABLE changes lock the table](https://schemity.com/blog/mysql-alter-table-lock-instant-inplace-copy/).

## Does CockroachDB roll back a failed migration?

Not by default: CockroachDB commits before each schema change, and turning that off does not make DDL and data changes fail together.

CockroachDB's [session variables page](https://docs.cockroachlabs.com/docs/stable/session-variables) describes `autocommit_before_ddl` as causing "any DDL statement received during an explicit transaction to auto-commit before executing the schema change", default `on`, "because CockroachDB does not fully support multiple schema changes in a single transaction." On v26.3.4 it announced itself on the first `ALTER TABLE` with `NOTICE: auto-committing transaction before processing DDL due to autocommit_before_ddl setting`. After that, the results matched MySQL: the `NOT NULL` check failed, and `phone`, its index and the `UPDATE` all stayed.

Turning the setting off is the obvious next step, and on 26.3 it stops at the first `ALTER`. `SHOW CREATE TABLE` showed the new table as `WITH (schema_locked = true)`, and with the setting off, `ALTER TABLE customers ADD COLUMN fax STRING` inside `BEGIN` was refused: `this schema change is disallowed because table "customers" is locked and this operation cannot automatically unlock the table`. CockroachDB's own hint says that locking "improves changefeed performance". Unlocking every table you migrate trades that away.

With the table unlocked and the setting off, the migration ran inside one transaction, and the result was still not all-or-nothing. CockroachDB validates the `NOT NULL` after the transaction commits, and it reported:

```text
ERROR: transaction committed but schema change aborted with error:
(23502): validation of column "email" NOT NULL failed on row: id=2, email=NULL, ...
SQLSTATE: XXA00
HINT: Some of the non-DDL statements may have committed successfully,
but some of the DDL statement(s) failed.
```

Afterwards, `fax` did not exist and the `UPDATE` in the same transaction had been kept. On CockroachDB, keep data changes and schema changes in separate migrations, whatever the setting says.

## How should you write migrations for a database that cannot roll them back?

Assume each statement commits on its own, and write the migration so the statement most likely to fail can fail before anything else has changed.

- **Check the data first.** `SELECT count(*) FROM customers WHERE email IS NULL` before a `NOT NULL`, and a `GROUP BY ... HAVING count(*) > 1` before a unique index. The statements that fail most often are the ones that check existing rows: `NOT NULL`, `CHECK`, `UNIQUE` and foreign keys. Lock timeouts, name clashes and type conversions are the other usual causes.
- **Put the checking statement first where the order allows.** A migration whose only failing statement runs first fails clean, even on a database that commits each statement.
- **Keep data changes out of schema migrations.** A backfill in its own migration can be rerun; one that gets committed as a side effect of the next `ALTER TABLE` cannot be told apart from a successful run.
- **Keep migrations small.** One schema change per migration on MySQL and CockroachDB means a failure leaves a state the migration tool can describe: done or not done.
- **Write the reverse statements before you apply.** On these two databases, `DROP INDEX customers_phone_idx` and `ALTER TABLE customers DROP COLUMN phone` are your rollback.

On PostgreSQL the transaction does the cleanup, so the list there is about locks rather than leftovers. Keep each transaction short and give it a `lock_timeout`.

## How Schemity shows which statement will fail before you apply it

Schemity's impact analysis names the statements that can fail on existing rows, and its migration dialog says whether your database will roll back the rest.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Make `customers.email` required on a connected diagram, or let an AI agent propose it, and [impact analysis](https://schemity.com/doc/impact-analysis/) puts the `SET NOT NULL` under **Can fail on existing data** with the table's estimated row count. Its **Count exactly** button runs `SELECT COUNT(*) FROM customers WHERE email IS NULL` when you ask, so you know before applying whether statement 3 will fail. A new unique index gets the same treatment for duplicates. That is what it means to see the impact of every change: the failure moves from the middle of a migration to the review before it.

![Schemity's Impact drawer for customers.email made NOT NULL on PostgreSQL 18.3: under Can fail on existing data, Sets customers.email NOT NULL, fails if any of ~100K rows is NULL, with Exactly 1000 rows after Count exactly; under Holds back other statements, Checking email for NULL holds back reads and writes to customers while it reads ~100K rows, 8.7 MB on disk](https://schemity.com/images/blog/transactional-ddl-postgres-mysql-cockroachdb/impact-set-not-null.webp)

The second finding is the PostgreSQL cost from earlier in the post: the `NULL` check holds back reads and writes to `customers` while it scans the table, so the drawer shows both whether statement 3 can fail and what it blocks while it runs. On CockroachDB the same change shows only the first finding: it runs the `NULL` check as a background job, and while it checked 2,000,000 rows for 17 seconds, a `SELECT`, an `INSERT` and an `UPDATE` on the table each came back in about a quarter of a second.

The migration dialog then states what a failure will leave behind, worded for the database:

| Connected to | The dialog says |
| --- | --- |
| PostgreSQL, SQL Server, SQLite | "All SQL migrations are executed within a transaction, ensuring data integrity and safety. If any errors occur, changes will be automatically rolled back." |
| MySQL | "MySQL commits each DDL statement immediately. If a later statement fails, earlier statements remain applied and cannot be rolled back. Review the SQL carefully before applying." |
| CockroachDB | "CockroachDB commits each schema change immediately. If a later statement fails, earlier statements remain applied and cannot be rolled back. Review the SQL carefully before applying." |

![Schemity's Migration confirm dialog on MySQL 8.4: the finding Sets customers.email NOT NULL, fails if any of ~99.9K rows is NULL, the planned ALTER TABLE customers MODIFY COLUMN email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, and the warning MySQL commits each DDL statement immediately, earlier statements remain applied and cannot be rolled back](https://schemity.com/images/blog/transactional-ddl-postgres-mysql-cockroachdb/migration-dialog-mysql-warning.webp)

![Schemity's Migration confirm dialog on CockroachDB 26.3.4: the finding Sets customers.email NOT NULL, fails if any of ~100K rows is NULL, the planned ALTER TABLE customers ALTER COLUMN email SET NOT NULL, the warning CockroachDB commits each schema change immediately, earlier statements remain applied and cannot be rolled back, and the footer CockroachDB: 26.3.4](https://schemity.com/images/blog/transactional-ddl-postgres-mysql-cockroachdb/migration-dialog-cockroachdb-warning.webp)

CockroachDB has its own database type in the connection dialog. A diagram created as PostgreSQL before that still gets the CockroachDB warning, because Schemity also checks the server's version string; without that check, a CockroachDB connection would get PostgreSQL's promise of a rollback. The setup is in [connecting to CockroachDB](https://schemity.com/doc/connect-postgresql/). If a statement does fail, the error names the statement's number and the database's message, and a notification repeats it, so a long migration does not hide which statement failed. The [migration SQL](https://schemity.com/doc/migration-sql-diff/) is there to read in full before you press Apply.

Schemity does not reorder your statements or split a migration into smaller ones, and it does not change `autocommit_before_ddl`. It tells you which statement will fail and what the database will keep. The order is still yours.

## Transactional DDL by database, in one list

Only some databases undo a failed migration, and on MySQL and CockroachDB a `BEGIN` around it does not change that.

- PostgreSQL, SQL Server and SQLite roll back the whole migration. On PostgreSQL every lock is held until `COMMIT`, and `CREATE INDEX CONCURRENTLY` must run outside a transaction.
- MySQL commits before and after every DDL statement. Atomic DDL makes each statement all-or-nothing, not the migration, and data changes made before the DDL commit with it.
- CockroachDB commits before each schema change by default. With `autocommit_before_ddl` off, locked tables refuse the `ALTER`, and unlocked ones can keep the data changes when a schema change fails after commit.
- On MySQL and CockroachDB, check the data first, run the statement that can fail first, keep data changes in their own migration and write the reverse statements before applying.

Where the failure can be caught before it runs, it usually is a constraint meeting old rows, as with the keys in [PostgreSQL deferrable foreign keys](https://schemity.com/blog/postgres-deferrable-foreign-keys/). Knowing which schema actually reached production after a half-applied migration is what [tracking schema changes in PostgreSQL](https://schemity.com/blog/postgres-track-schema-changes/) and [comparing staging and production schemas](https://schemity.com/blog/comparing-staging-and-production-database-schemas/) cover.

## Frequently asked questions

### Can you roll back an `ALTER TABLE` in MySQL?

No. MySQL ends any open transaction before it runs an `ALTER TABLE`, `CREATE INDEX` or `CREATE TABLE`, as if you had typed `COMMIT`, so a later `ROLLBACK` has nothing left to undo. Atomic DDL, added in MySQL 8.0, makes each single DDL statement all-or-nothing, not the migration it belongs to.

### Does CockroachDB support transactional DDL?

Not by default. The `autocommit_before_ddl` session setting is on, so CockroachDB commits before each schema change, and a failed migration leaves its earlier statements applied. With the setting off, an `ALTER` inside a transaction is refused on tables created with `schema_locked = true`, which CockroachDB 26.3 does by default, and on unlocked tables a schema change that fails at commit can still leave the transaction's data changes committed.

### Should a migration run in one transaction on PostgreSQL?

Usually yes, because a failure then leaves nothing behind, but every lock a statement takes is held until `COMMIT`. An `ADD COLUMN` takes `ACCESS EXCLUSIVE` on the table, so a long migration transaction blocks reads and writes for its whole run. `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block at all, so it goes in a migration of its own.