Does ALTER TABLE ADD COLUMN lock the table in MySQL?

Short answer

On MySQL 8.4, ALTER TABLE … ADD COLUMN runs with ALGORITHM=INSTANT: it records the new column in the data dictionary and doesn’t touch a single row. On our 1,000,000-row test table it took 0.01 seconds, with or without a default, and at any position (AFTER customer_id works too).

It still has to take an exclusive metadata lock on the table for that instant. If any open transaction has used the table, even with a plain SELECT, the ALTER waits for it to finish, and every query that arrives after the ALTER waits behind it, showing Waiting for table metadata lock. That queue, not the column itself, is what takes sites down.

Write the algorithm out:

ALTER TABLE orders ADD COLUMN source varchar(20), ALGORITHM=INSTANT;

If the change can’t be done instantly, MySQL then refuses with an error instead of quietly choosing a slower algorithm.

What it locks

MySQL protects table definitions with metadata locks. Every statement that uses a table takes a shared metadata lock on it and, inside a transaction, keeps it until the transaction commits or rolls back. ALTER TABLE needs the exclusive one, so it waits until no transaction holds a shared lock. While it waits, new requests for the table queue behind it.

We opened a transaction that read one row and left it idle, started the ALTER with lock_wait_timeout set to 5 seconds, then ran a SELECT from a third session. SHOW PROCESSLIST during the wait:

| Id    | User | Host            | db           | Command | Time | State                           | Info                                                                                  |
| 29524 | root | 127.0.0.1:51620 | seo_mysqlmig | Sleep   |    2 |                                 | NULL                                                                                  |
| 29526 | root | 127.0.0.1:51646 | seo_mysqlmig | Query   |    2 | Waiting for table metadata lock | ALTER TABLE orders ADD COLUMN gift_wrap tinyint NOT NULL DEFAULT 0, ALGORITHM=INSTANT |
| 29527 | root | 127.0.0.1:51648 | seo_mysqlmig | Query   |    1 | Waiting for table metadata lock | SELECT id, total FROM orders WHERE id = 2                                             |

The culprit is the connection in Sleep with no query: it’s idle, but its transaction is still open. After 5 seconds the ALTER gave up:

ERROR 1205 (HY000) at line 2: Lock wait timeout exceeded; try restarting transaction

and only then did the SELECT return, after waiting 4.29 seconds for a one-row lookup. The default lock_wait_timeout on MySQL is 31536000 seconds, one year, so without a lower setting the ALTER and everything behind it would keep waiting.

To find who is holding the table, MySQL’s sys schema joins the metadata locks for you:

SELECT waiting_pid, waiting_query, blocking_pid, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = '<database>' AND object_name = 'orders';

+-------------+-------------------------------------------------------------------+--------------+------------------------------+
| waiting_pid | waiting_query                                                     | blocking_pid | sql_kill_blocking_connection |
+-------------+-------------------------------------------------------------------+--------------+------------------------------+
|       29526 | ALTER TABLE orders ADD COLUMN  ... L DEFAULT 0, ALGORITHM=INSTANT |        29524 | KILL 29524                   |
|       29527 | SELECT id, total FROM orders WHERE id = 2                         |        29524 | KILL 29524                   |
|       29526 | ALTER TABLE orders ADD COLUMN  ... L DEFAULT 0, ALGORITHM=INSTANT |        29526 | KILL 29526                   |
|       29527 | SELECT id, total FROM orders WHERE id = 2                         |        29526 | KILL 29526                   |
+-------------+-------------------------------------------------------------------+--------------+------------------------------+

The rows that name the ALTER itself (29526) as a blocker are the queue; the one to deal with is 29524. information_schema.INNODB_TRX shows how long its transaction has been open:

SELECT trx_mysql_thread_id AS thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

+-----------+---------------------+--------------+-----------+
| thread_id | trx_started         | seconds_open | trx_query |
+-----------+---------------------+--------------+-----------+
|     29524 | 2026-10-09 10:21:38 |            2 | NULL      |
+-----------+---------------------+--------------+-----------+

Does it rewrite the table?

No, when INSTANT is used. MySQL 8.4 refused ALGORITHM=INSTANT in these cases:

• Combined with a change that isn’t instant, such as ADD COLUMN … , ADD INDEX … in one statement: ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. Run the two changes as separate statements.
• A stored generated column (… AS (total * 1.2) STORED): error 1845. A VIRTUAL one was instant.
• A ROW_FORMAT=COMPRESSED table: error 1845.
• A table with a FULLTEXT index: ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time.
• After 64 instant column changes. Each instant ADD or DROP COLUMN adds a row version. At 64, ALGORITHM=INSTANT fails with ERROR 4092 (HY000): Maximum row versions reached for table seo_mysqlmig/rv. No more columns can be added or dropped instantly. Please use COPY/INPLACE. Without the ALGORITHM clause, the same ADD COLUMN succeeded by rebuilding the table, and the count went back to 0.

The MySQL manual adds temporary tables, tables in the data dictionary tablespace, and additions that would take the row past the maximum row size.

ALGORITHM=INPLACE, LOCK=NONE always works for adding a column, but it rebuilds the table: 0.67 seconds on our million rows instead of 0.01, with reads and writes allowed during the rebuild. On a large table that’s minutes or hours of extra I/O, though not an outage.

Run it safely

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN source varchar(20), ALGORITHM=INSTANT;

1. Set lock_wait_timeout for the session, a few seconds. If the ALTER can’t get its lock in time it fails with error 1205, and the queue behind it clears. Retry a little later.

2. Ask for ALGORITHM=INSTANT. If MySQL refuses, you find out before anything happens, and can choose ALGORITHM=INPLACE, LOCK=NONE knowingly, ideally at a quiet time.

3. Look for long transactions first. Before running the ALTER, check information_schema.INNODB_TRX for anything that has been open for minutes; an idle connection from a forgotten client or a long report will hold the table.

4. Watch the row-version count if you add and drop columns often:

   SELECT NAME, TOTAL_ROW_VERSIONS
   FROM INFORMATION_SCHEMA.INNODB_TABLES
   WHERE NAME = '<database>/orders';
   

   A rebuild (OPTIMIZE TABLE, or any ALTER that rebuilds) resets it to 0.

Don’t add LOCK=NONE to an instant change on MySQL: ALGORITHM=INSTANT, LOCK=NONE fails with ERROR 1221 (HY000): Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE.

MariaDB

MariaDB 11.4 also adds columns instantly, at any position, with the same metadata lock: our test showed the same Waiting for table metadata lock queue and the same error 1205. The differences we saw:

• ALGORITHM=INSTANT, LOCK=NONE is accepted.
• It refused INSTANT for the same cases (ADD COLUMN with ADD INDEX, a stored generated column, a compressed table, a table with a FULLTEXT index), with a slightly different message: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE.
• We ran 80 instant ADD/DROP COLUMN operations on one table without hitting a limit.
• ALGORITHM=INPLACE for ADD COLUMN was instant too (0.001 seconds), not a rebuild.
• ALTER TABLE orders WAIT 5 ADD COLUMN … (or NOWAIT) sets the lock wait for that one statement. MySQL rejects this syntax.
• The default lock_wait_timeout is 86400 seconds (one day), and the Performance Schema is off by default, so sys.schema_table_lock_waits has nothing to show; SHOW PROCESSLIST and information_schema.INNODB_TRX work.

How we checked

A test table on MySQL 8.4.11 and MariaDB 11.4.13:

CREATE TABLE orders (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int NOT NULL,
  total decimal(10,2) NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'new',
  note varchar(100) NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- filled with 1,000,000 rows

ALTER TABLE orders ADD COLUMN source varchar(20), ALGORITHM=INSTANT;
ALTER TABLE orders ADD COLUMN channel varchar(20) NOT NULL DEFAULT 'web' AFTER customer_id, ALGORITHM=INSTANT;
ALTER TABLE orders ADD COLUMN x int, ALGORITHM=INSTANT, LOCK=NONE;
ALTER TABLE orders ADD COLUMN y int, ADD INDEX idx_y (y), ALGORITHM=INSTANT;
ALTER TABLE orders ADD COLUMN z int, ALGORITHM=INPLACE, LOCK=NONE;

MySQL 8.4.11:

ALTER TABLE orders ADD COLUMN source varchar(20), ALGORITHM=INSTANT
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE orders ADD COLUMN channel varchar(20) NOT NULL DEFAULT 'web' AFTER customer_id, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE orders ADD COLUMN x int, ALGORITHM=INSTANT, LOCK=NONE
ERROR 1221 (HY000) at line 3: Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE

ALTER TABLE orders ADD COLUMN y int, ADD INDEX idx_y (y), ALGORITHM=INSTANT
ERROR 1845 (0A000) at line 4: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

ALTER TABLE orders ADD COLUMN z int, ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

In Inlet

Inlet’s structure editor shows the exact ALTER TABLE before it runs and warns when a change rewrites or scans the table. The Activity monitor for MySQL and MariaDB lists sessions, so a connection sitting in an open transaction is easy to find before you run the migration.