Does changing a column type lock the table in MySQL?

Short answer

Yes. On MySQL 8.4, changing a column’s data type (decimal(10,2) to decimal(12,2), int to bigint, varchar to text, shrinking a varchar) is only supported with ALGORITHM=COPY. MySQL creates a new table with the new definition, copies every row into it, then swaps it in. While it copies, reads continue but writes wait: every INSERT, UPDATE and DELETE on the table queues until the copy is done.

On our 1,000,000-row test table that took about 1.3 seconds. On a table of hundreds of gigabytes it’s hours, with writes stopped and twice the disk space needed.

A tell-tale sign afterwards: a copy reports every row as affected.

Query OK, 1000000 rows affected (1.34 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

What it locks

For the whole copy, the ALTER holds a SHARED_NO_WRITE metadata lock: other sessions may read but not write. Reading performance_schema.metadata_locks during the copy, with an UPDATE sent from another session:

+-------------+-----------------+-------------+----------------------------------------------------+
| OBJECT_NAME | LOCK_TYPE       | LOCK_STATUS | SQL_TEXT                                           |
+-------------+-----------------+-------------+----------------------------------------------------+
| orders      | SHARED_NO_WRITE | GRANTED     | ALTER TABLE orders MODIFY total decimal(17,2) NOT  |
| orders      | SHARED_WRITE    | PENDING     | UPDATE orders SET status = 'new' WHERE id = 11     |
+-------------+-----------------+-------------+----------------------------------------------------+

SHOW PROCESSLIST shows the ALTER as copy to tmp table and the writer as Waiting for table metadata lock:

+-------+---------+------+---------------------------------+------------------------------------------------------------------------+
| ID    | COMMAND | TIME | STATE                           | INFO                                                                   |
+-------+---------+------+---------------------------------+------------------------------------------------------------------------+
| 29379 | Query   |    1 | copy to tmp table               | ALTER TABLE orders MODIFY total decimal(14,2) NOT NULL                 |
| 29381 | Query   |    1 | Waiting for table metadata lock | INSERT INTO orders (customer_id, total, note) VALUES (42, 19.99, 'new  |
+-------+---------+------+---------------------------------+------------------------------------------------------------------------+

The one-row INSERT took 0.90 seconds, the time left on the copy. A SELECT sent during another copy returned in 0.08 seconds. At the very end the lock becomes exclusive for a moment to swap the tables, and like any DDL the ALTER first waits for open transactions on the table.

You can’t ask for better: LOCK=NONE is refused.

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

Does it rewrite the table?

Yes, the whole table, including every index. MySQL refuses the faster algorithms for a type change:

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

The exception is making a VARCHAR longer without changing how many bytes store its length. A VARCHAR of up to 255 bytes uses one length byte, longer ones use two, and the byte count depends on the character set (up to 4 bytes per character in utf8mb4). On MySQL 8.4 with utf8mb4:

Changing a column from NULL to NOT NULL isn’t a type change: it ran in place with LOCK=NONE, writes allowed, but rebuilt the table (0.55 seconds).

Run it safely

First, see whether you can avoid the copy. Ask for the online algorithm and let MySQL say no:

ALTER TABLE orders MODIFY note varchar(200) NULL, ALGORITHM=INPLACE, LOCK=NONE;

If it’s refused and the table is big, the options are:

1. Run the copy in a quiet window, with a short lock_wait_timeout so it doesn’t queue the table behind an open transaction while it waits to start:

   SET SESSION lock_wait_timeout = 5;
   ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL, ALGORITHM=COPY, LOCK=SHARED;
   

2. Add a new column and move to it in steps: add the new column (instant), write to both from the application, backfill old rows in small batches, switch reads, then drop the old column. No long lock, more work.

3. Use an online schema change tool that copies into a shadow table while capturing writes, then swaps tables.

Whatever you choose, MODIFY and CHANGE replace the whole column definition: repeat NOT NULL, DEFAULT, COMMENT and the character set, or they’re dropped.

MariaDB

MariaDB 11.4 also refuses INSTANT and INPLACE for a real type change (Cannot change column type. Try ALGORITHM=COPY), but its copy can run online. Since MariaDB 11.2, ALGORITHM=COPY accepts LOCK=NONE, and in our test that’s what it did by default: during ALTER TABLE orders MODIFY total decimal(14,2) NOT NULL with no clauses, an INSERT returned in 0.000 seconds, and the copy reported 1,000,001 rows, including the row inserted mid-copy. With LOCK=SHARED written out, the INSERT waited (0.745 seconds) as on MySQL.

It was also more relaxed about VARCHAR: varchar(20) → varchar(100) in utf8mb4, across the 255-byte boundary, ran in place in 0.001 seconds, and varchar(100) → varchar(200) was accepted with ALGORITHM=INSTANT. Shrinking a VARCHAR and int → bigint still needed a copy.

How we checked

A 1,000,000-row orders table (see ADD COLUMN for the definition) on MySQL 8.4.11 and MariaDB 11.4.13:

ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL, ALGORITHM=COPY, LOCK=NONE;
ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL, ALGORITHM=COPY, LOCK=SHARED;
ALTER TABLE orders MODIFY customer_id bigint NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY note varchar(200) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders MODIFY note varchar(200) NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY status varchar(100) NOT NULL DEFAULT 'new', ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY status varchar(60) NOT NULL DEFAULT 'new', ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY note varchar(50) NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY note varchar(200) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Then, with fresh tables, we ran a type change in one session and an INSERT, UPDATE and SELECT from another 0.4 seconds later, and read SHOW PROCESSLIST and performance_schema.metadata_locks meanwhile.

In Inlet

Inlet’s structure editor shows the exact ALTER TABLE before it runs and warns when a change rewrites or scans the table, so a type change on a large table doesn’t go out by surprise.