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

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: 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.

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 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:

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 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:

-- 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

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 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:

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, a change you draw on the diagram, or one an AI agent proposes, becomes a planned migration, and impact analysis reports what each statement does to the rows before anything runs.

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".

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

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, and it treats VARCHAR growth as free at any length, compared with MySQL in VARCHAR vs TEXT. SQL Server refuses a type change under a key outright, as in SQL Server INT to BIGINT, and what a MySQL key type change costs across a whole foreign key graph is measured in MySQL UNSIGNED primary keys.