# MySQL ALTER TABLE: Which Changes Lock the Table, and How to Make One Fail Instead of Copying

> On MySQL 8.4, adding, dropping or renaming a column and changing a default are metadata-only, and an index or `NOT NULL` builds while writes continue. Other type changes, including a `VARCHAR` that crosses 255 bytes, a `CHECK` and a foreign key with checks on copy the table and hold back writes until the copy ends. Every `ALTER TABLE`, instant ones included, first waits for an exclusive metadata lock, and new queries on the table queue behind it.

Source: https://schemity.com/blog/mysql-alter-table-lock-instant-inplace-copy/

On MySQL 8.4, most `ALTER TABLE` statements do not hold the table for their whole run. Adding, dropping or renaming a column and changing a default only edit metadata, and adding an index or a `NOT NULL` builds while reads and writes continue. Among the common changes, the ones that copy the table, and hold back writes until the copy ends, are a data type change (including a `VARCHAR` that grows past 255 bytes), a new `CHECK` and a foreign key added with checks on. And every one of them, instant ones too, first needs an exclusive metadata lock, which is how a 0.1-second `ADD COLUMN` can stop reads on the table for 17 seconds.

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop, against a `users` table of 1,000,000 rows (`id INT AUTO_INCREMENT`, `email VARCHAR(50) NOT NULL`, `name VARCHAR(100) NULL`, `status VARCHAR(20)`, `created_at DATETIME`, all `utf8mb4`) and an `orders` table of 1,000,000 rows pointing at it. Each change in the table below ran on a fresh copy of `users` with no foreign key on it, so every row starts from the same table. Read the timings as ratios between kinds of change, not as what your server will do.

## Does MySQL ALTER TABLE lock the table?

Briefly, always; for the whole operation, only when MySQL decides to copy the table.

When a statement names no algorithm, the [`ALTER TABLE` documentation](https://dev.mysql.com/doc/refman/8.4/en/alter-table.html) says MySQL "uses `ALGORITHM=INSTANT`" where it is supported, "otherwise, `ALGORITHM=INPLACE` is used. If `ALGORITHM=INPLACE` is not supported, `ALGORITHM=COPY` is used." `INSTANT` edits only the data dictionary. `INPLACE` builds without copying rows into a new table and usually lets writes continue. `COPY` builds a new table row by row, and while it does, reads go through, except for a moment at the final swap, and writes wait. Which one a change gets is decided by the change, not by you. The algorithm column below is the one MySQL accepted when the statement named it, since MySQL does not report which one it picked:

| Change on 1,000,000 rows | Algorithm it runs as | Writes during it | Time |
| --- | --- | --- | --- |
| `ADD COLUMN phone VARCHAR(30) NULL AFTER email` | `INSTANT` | Allowed | 0.12 s |
| `DROP COLUMN name` | `INSTANT` | Allowed | 0.06 s |
| `RENAME COLUMN name TO full_name` | `INSTANT` | Allowed | 0.03 s |
| `ALTER COLUMN status SET DEFAULT 'pending'` | `INSTANT` | Allowed | 0.02 s |
| `MODIFY COLUMN name VARCHAR(200) NULL` (was 100) | `INPLACE`, no rebuild | Allowed | 0.02 s |
| `MODIFY COLUMN name VARCHAR(100) NOT NULL` | `INPLACE`, rebuilds the table | Allowed | 1.20 s |
| `ADD INDEX ix_name (name)` | `INPLACE` | Allowed | 1.18 s |
| `MODIFY COLUMN email VARCHAR(100) NOT NULL` (was 50) | `COPY` | Held back | 1.92 s |
| `MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT` | `COPY` | Held back | 1.64 s |
| `MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT` | `COPY` | Held back | 1.68 s |
| `ADD CONSTRAINT ... CHECK (status IN (...))` | `COPY` | Held back | 1.74 s |
| `ADD CONSTRAINT ... FOREIGN KEY` on `orders` | `COPY` | Held back | 4.07 s |

The "writes during it" column was measured, not read off the documentation. On the same table grown to 4,000,000 rows, a single-row `INSERT` sent 1.5 seconds into the run waited 6.3 seconds for the `VARCHAR(50)` to `VARCHAR(100)` copy and 5.9 seconds for the `CHECK`, while a `SELECT` by primary key sent at the same moment came back in about 0.1 second. During the `NOT NULL` rebuild and the index build, the same `INSERT` came back in 0.12 and 0.22 seconds. The `NOT NULL` row needs one more caveat: it is in-place, but with the default strict SQL mode it still fails with `ERROR 1138 (22004): Invalid use of NULL value` if any existing row holds a `NULL`.

The foreign key row is the one most often missed. The [online DDL reference](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html) says adding one supports `INPLACE` only "when `foreign_key_checks` is disabled. Otherwise, only the `COPY` algorithm is supported." With checks off, the same statement took 0.01 seconds, and it validated nothing: on a small test table, a key added that way was accepted over a child row pointing at a parent that does not exist.

## Why does widening a VARCHAR sometimes copy the table?

Because the copy is triggered by the column's length prefix changing from one byte to two, and MySQL counts that at the character set's widest character, 4 bytes for `utf8mb4`.

A `VARCHAR` value carries a prefix saying how long it is. Up to 255 bytes it is one byte; from 256 bytes it is two. The documentation states the consequence: in-place `ALTER TABLE` "does not support increasing the size of a `VARCHAR` column from less than 256 bytes to a size equal to or greater than 256 bytes." At 4 bytes per character, `utf8mb4` `VARCHAR(63)` is 252 bytes and `VARCHAR(64)` is 256. So two changes that look the same in a migration file behave differently:

```sql
-- name was VARCHAR(100): 400 bytes, already a two-byte prefix
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- Query OK

-- email was VARCHAR(50): 200 bytes, one-byte prefix; 100 characters is 400 bytes
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
-- Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```

The boundary held exactly in the container: `utf8mb4` `VARCHAR(60)` to `VARCHAR(63)` ran in place and `VARCHAR(63)` to `VARCHAR(64)` was refused; `latin1` `VARCHAR(100)` to `VARCHAR(255)` ran in place and `VARCHAR(255)` to `VARCHAR(256)` was refused; `VARBINARY(255)` to `VARBINARY(256)` was refused too. Shrinking a `VARCHAR`, growing a `CHAR`, and `VARCHAR` to `TEXT` always copied. So the cost depends on where a column started. A `utf8mb4` `VARCHAR(50)` copies the table the first time it grows past 63 characters, and grows in place after that; the `VARCHAR(191)` many `utf8mb4` schemas use is already 764 bytes with a two-byte prefix, so it grows in place from the start.

## How do you make MySQL refuse an ALTER instead of copying?

Name the algorithm and lock you expect: with `ALGORITHM=INSTANT`, or `ALGORITHM=INPLACE, LOCK=NONE`, MySQL refuses a change it cannot do that way instead of falling back to a copy.

The documentation is explicit that "specifying an `ALGORITHM` clause requires the operation to use the specified algorithm ... or fail with an error otherwise", and `LOCK=NONE` adds "if supported, permit concurrent reads and writes. Otherwise, an error occurs." That turns a silent decision into a reviewed one. A migration that says

```sql
ALTER TABLE users ADD CONSTRAINT users_status_check
  CHECK (status IN ('active', 'pending', 'closed')),
  ALGORITHM=INPLACE, LOCK=NONE;
```

fails in staging with `ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.` instead of holding back writes in production. Two limits on the clause: `INSTANT` takes no `LOCK` other than the default, and the clause has to be in the SQL that runs, so check whether the migration your ORM generates carries it or needs a raw-SQL step.

The clause also catches the case where an `INSTANT` change stops being instant without telling you. Each instant add or drop of a column creates a new row version, and the online DDL reference says "the maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0)". On a fresh table, the 65th `ADD COLUMN ..., ALGORITHM=INSTANT` failed with `ERROR 4092 (HY000): Maximum row versions reached for table t/rv. No more columns can be added or dropped instantly. Please use COPY/INPLACE.` The same `ADD COLUMN` without the clause succeeded with no warning, and `information_schema.INNODB_TABLES` showed why: `TOTAL_ROW_VERSIONS` went from 64 to 0, because MySQL had quietly rebuilt the whole table.

## Why can an instant ALTER TABLE still stop reads?

Because every `ALTER TABLE` first takes an exclusive metadata lock, it cannot get one while any open transaction has touched the table, and new queries queue behind it while it waits.

The [metadata locking page](https://dev.mysql.com/doc/refman/8.4/en/metadata-locking.html) explains both halves. A transaction keeps its metadata locks "until the transaction ends", so "a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends". It also says "write lock requests have higher priority than read lock requests", and in the container a waiting `ALTER` held back every query that arrived after it:

1. Session A: `BEGIN; SELECT id FROM users WHERE id = 1;` and then nothing for 20 seconds, the shape of a slow report or a forgotten console.
2. Session B, one second later: `ALTER TABLE users ADD COLUMN nickname VARCHAR(30) NULL, ALGORITHM=INSTANT;`. The process list showed it as `Waiting for table metadata lock`.
3. Session C, two seconds after that: `SELECT email FROM users WHERE id = 2;`. It waited **17.0 seconds**, until A committed and B ran.

Nothing about the change was slow; the queue was. And `SELECT @@lock_wait_timeout` in the same server returned `31536000`, one year, so by default B waits as long as A stays open. The fix is to give the migration session a short timeout and retry:

```sql
SET SESSION lock_wait_timeout = 3;
ALTER TABLE users ADD COLUMN nickname VARCHAR(30) NULL, ALGORITHM=INSTANT;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

With the same open transaction in A, B gave up after 3 seconds, a query that arrived while B was waiting took 1.6 seconds instead of 17, and the next one took 0.1 seconds. The wait is not only at the start: per the `ALTER TABLE` documentation, an `INPLACE` change may take the exclusive lock again during execution and a `COPY` needs it to swap the tables at the end, so a long build can stall the same way at its finish. Before any migration on a busy table, `performance_schema.metadata_locks` shows which sessions hold a lock on the table, and `information_schema.INNODB_TRX` how long their transactions have been open.

## How Schemity shows which MySQL changes copy the table

Schemity's impact analysis marks the MySQL changes that rewrite the table and hold back writes before the migration runs.

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. After you [connect to MySQL](https://schemity.com/doc/connect-mysql/), a change you draw on the diagram, or one an AI agent proposes, becomes a planned migration, and [impact analysis](https://schemity.com/doc/impact-analysis/) reports what each statement does to the rows before anything runs.

![The users and orders tables read from a live MySQL 8.4 database in Schemity: users.email VARCHAR(50), users.name VARCHAR(100), and the foreign key from orders.user_id to users.id](https://schemity.com/images/blog/mysql-alter-table-lock-instant-inplace-copy/canvas-users-orders.webp)

Widen `users.email` from `VARCHAR(50)` to `VARCHAR(100)` and the findings include "Rewrites users to change email: ~995.6K rows, 70 MB on disk" and, under what holds back other statements, "Changing the type of email holds back writes to users while it reads ~995.6K rows, 70 MB on disk".

![Schemity's Impact drawer after users.email is widened from VARCHAR(50) to VARCHAR(100) on MySQL 8.4: Rewrites the table, Rewrites users to change email, ~995.6K rows, 70 MB on disk, and Holds back other statements, Changing the type of email holds back writes to users](https://schemity.com/images/blog/mysql-alter-table-lock-instant-inplace-copy/impact-widen-email-copy.webp)

![The Findings panel for the same change: the planned migration is ALTER TABLE users MODIFY COLUMN email VARCHAR(100) with no ALGORITHM clause, one planned change, two lint notes and the two impact findings](https://schemity.com/images/blog/mysql-alter-table-lock-instant-inplace-copy/planned-migration-widen-email.webp)

Widen `users.name` from `VARCHAR(100)` to `VARCHAR(200)` instead and neither finding appears, because MySQL grows that column in place.

![Schemity's Impact drawer after users.name is widened from VARCHAR(100) to VARCHAR(200) on MySQL 8.4: Impact (0), No findings, because MySQL grows the column in place](https://schemity.com/images/blog/mysql-alter-table-lock-instant-inplace-copy/impact-widen-name-in-place.webp)

![The Findings panel for the name change: the planned migration is the same kind of ALTER TABLE users MODIFY COLUMN statement, now VARCHAR(200), and Impact shows 0 findings](https://schemity.com/images/blog/mysql-alter-table-lock-instant-inplace-copy/planned-migration-widen-name.webp)

Add a `CHECK` on `users` and the finding is "Checking the new constraint holds back writes to users while it reads" the same row estimate; add a foreign key on `orders` and it names `orders`. That is the point of being able to see the impact of every change: the two `VARCHAR` widenings read the same in a migration file and cost different things.

The planner also writes the statements MySQL insists on. Changing the type or sign of a column at either end of a foreign key drops the key, changes the columns and adds the key back, because MySQL refuses a type change under a foreign key with error 3780, even with `foreign_key_checks` off. Foreign key checks are turned back on before the keys return, so the re-added key checks the rows that are already there, which is also why it copies the referencing table. And the migration dialog warns that MySQL commits each DDL statement immediately, so if a later statement fails, the earlier ones stay applied.

What Schemity does not do is take the metadata lock for you. The queue in the previous section depends on what other sessions have open when the migration starts, which no diagram can see, so set `lock_wait_timeout` in the session that runs it.

## MySQL ALTER TABLE locks, in one list

Only a copy holds back writes for the whole run, but every `ALTER TABLE` can queue reads behind a metadata lock.

- Adding, dropping or renaming a column and changing a default are usually `INSTANT`, until the table reaches 64 row versions on MySQL 8.4, and renaming a column a foreign key uses is `INPLACE`.
- Indexes and `NOT NULL` run `INPLACE`; writes continue.
- Other data type changes, including a `VARCHAR` crossing 255 bytes, a `CHECK` and a foreign key with checks on copy the table and hold back writes until the copy ends.
- Add `ALGORITHM=INPLACE, LOCK=NONE` or `ALGORITHM=INSTANT` to make MySQL refuse instead of copying.
- Set `lock_wait_timeout` to a few seconds before any `ALTER TABLE` on a busy table, because even an instant one waits for open transactions and blocks every query behind it.

PostgreSQL draws the same lines in different places: its `SET NOT NULL` blocks reads and writes while it scans every row, as measured in [adding a NOT NULL column to a large table](https://schemity.com/blog/postgres-add-not-null-column-large-table/), and it treats `VARCHAR` growth as free at any length, compared with MySQL in [VARCHAR vs TEXT](https://schemity.com/blog/postgres-varchar-vs-text/). SQL Server refuses a type change under a key outright, as in [SQL Server INT to BIGINT](https://schemity.com/blog/sqlserver-change-int-to-bigint-primary-key/), and what a MySQL key type change costs across a whole foreign key graph is measured in [MySQL UNSIGNED primary keys](https://schemity.com/blog/mysql-unsigned-vs-signed-primary-key/).

## Frequently asked questions

### Does MySQL `ALTER TABLE` lock the table?

Briefly, always; for the whole operation, only when MySQL has to copy the table. `ADD COLUMN`, `DROP COLUMN`, `RENAME COLUMN` and `SET DEFAULT` are usually `INSTANT`, and `ADD INDEX` and `NOT NULL` run `INPLACE` with reads and writes allowed. A data type change other than a short `VARCHAR` growing, an added `CHECK`, a foreign key added with `foreign_key_checks` on and a `VARCHAR` whose length prefix grows from one byte to two are `ALGORITHM=COPY`, which holds back every write until the copy finishes.

### How do I check whether a MySQL `ALTER TABLE` will copy the table?

Add the algorithm you expect to the statement: `ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NULL, ALGORITHM=INPLACE, LOCK=NONE`. If MySQL would need a copy, it refuses with error 1846 and the reason `Cannot change column type INPLACE. Try ALGORITHM=COPY.`, and nothing changes. Without the clause, MySQL silently picks the fastest algorithm it can, which may be a copy.

### Why is my `ALTER TABLE` stuck on Waiting for table metadata lock?

Usually another session has an open transaction that read or wrote the table, and MySQL holds that transaction's metadata lock until it commits or rolls back. The `ALTER TABLE` waits for it, and because a pending exclusive lock has priority, every new query on the table waits behind the `ALTER`. The default `lock_wait_timeout` is 31,536,000 seconds, one year, so set it to a few seconds in the migration session and retry on error 1205.