Does RENAME COLUMN lock the table in MySQL?

Short answer

On MySQL 8.4, renaming a column is instant: only the table’s metadata changes, and on our 1,000,000-row table it took 0.00 seconds.

ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT;

Like every ALTER TABLE, it needs an exclusive metadata lock for that moment. If a transaction that used the table is still open, the rename waits, and queries that arrive after it wait behind it. And the moment it commits, any query, view, trigger or procedure that still says note fails.

What it locks

The rename takes the table’s exclusive metadata lock, briefly. Getting it is the risky part. We left a transaction open after a one-row SELECT, ran the rename with lock_wait_timeout set to 5 seconds, and sent another SELECT from a third session. SHOW PROCESSLIST:

| Id    | User            | Host            | db           | Command | Time  | State                           | Info                                                                             |
| 30789 | root            | 127.0.0.1:47434 | seo_mysqlmig | Sleep   |     2 |                                 | NULL                                                                             |
| 30791 | root            | 127.0.0.1:47464 | seo_mysqlmig | Query   |     2 | Waiting for table metadata lock | ALTER TABLE seo_mysqlmig.orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT |
| 30793 | root            | 127.0.0.1:47492 | seo_mysqlmig | Query   |     1 | Waiting for table metadata lock | SELECT id, total FROM seo_mysqlmig.orders WHERE id = 2                           |

The ALTER failed after 5 seconds with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, and the one-row SELECT behind it took 4.28 seconds. The blocker is the Sleep connection: idle, but with an open transaction. sys.schema_table_lock_waits names it and gives the KILL statement:

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 |
|       30791 | ALTER TABLE seo_mysqlmig.order ...  TO comment, ALGORITHM=INSTANT |        30789 | KILL 30789                   |
|       30793 | SELECT id, total FROM seo_mysqlmig.orders WHERE id = 2            |        30789 | KILL 30789                   |
|       30791 | ALTER TABLE seo_mysqlmig.order ...  TO comment, ALGORITHM=INSTANT |        30791 | KILL 30791                   |
|       30793 | SELECT id, total FROM seo_mysqlmig.orders WHERE id = 2            |        30791 | KILL 30791                   |

The rows that list the waiting ALTER (30791) as a blocker show the queue behind it; the connection holding things up is 30789. MySQL’s default lock_wait_timeout is one year, so set your own before any DDL.

Does it rewrite the table?

No. A rename that keeps the data type and NULL/NOT NULL the same is a metadata change, with RENAME COLUMN or with CHANGE:

ALTER TABLE orders CHANGE comment note varchar(100) NULL, ALGORITHM=INSTANT;

MySQL 8.4 refused ALGORITHM=INSTANT when:

• The type or nullability changes in the same statement. CHANGE note comment varchar(150) NULL and CHANGE note comment varchar(100) NOT NULL both failed with ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. Rename on its own, then change the type separately and knowingly (see changing a column type).
• Another table’s foreign key references the column. Renaming customers.id, referenced by orders.customer_id: ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. With ALGORITHM=INPLACE, LOCK=NONE it went through in 0.00 seconds, still without a copy. Renaming the referencing column, orders.customer_id, was instant.

CHANGE restates the whole column definition, so if you use it to rename, repeat the type, NOT NULL, DEFAULT and the rest exactly. RENAME COLUMN can’t change anything else by mistake.

Run it safely

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT;

1. Set a short lock_wait_timeout and retry on error 1205, rather than letting the rename queue the table behind a long transaction.
2. Find what uses the old name first. Views aren’t updated: after renaming note, a view that selected it failed with ERROR 1356 (HY000): View 'seo_mysqlmig.recent_notes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. Search information_schema.VIEWS, ROUTINES and TRIGGERS for the column name.
3. Deploy in steps when the application uses the column. Running code still sends the old name the moment the rename commits. The usual sequence is: add the new column, write to both, backfill, move reads, then drop the old one. Or make the application tolerate both names before renaming.

MariaDB

MariaDB 11.4 also renamed instantly (0.002 seconds), with the same metadata lock wait: the rename waited for the open transaction, failed with error 1205 after 5 seconds, and the SELECT behind it took 4.29 seconds. The differences we saw:

• It renamed a column referenced by another table’s foreign key with ALGORITHM=INSTANT (0.004 seconds), where MySQL requires INPLACE.
• It renamed and widened a column in one instant statement: CHANGE note comment varchar(150) NULL, ALGORITHM=INSTANT succeeded.
• ALGORITHM=INSTANT, LOCK=NONE is accepted, and ALTER TABLE orders WAIT 5 RENAME COLUMN … sets the lock wait for one statement.
• The view broke in the same way, with the same error 1356.

How we checked

A 1,000,000-row orders table (see ADD COLUMN for the definition) and a customers table referenced by a foreign key, on MySQL 8.4.11 and MariaDB 11.4.13:

ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT;
ALTER TABLE orders CHANGE comment note varchar(100) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders CHANGE note comment varchar(150) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders CHANGE note comment varchar(100) NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE customers RENAME COLUMN id TO customer_id, ALGORITHM=INSTANT;
ALTER TABLE customers RENAME COLUMN id TO customer_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders RENAME COLUMN customer_id TO cust_id, ALGORITHM=INSTANT;
CREATE VIEW recent_notes AS SELECT id, note FROM orders WHERE id > 999990;
ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT;
SELECT * FROM recent_notes LIMIT 1;

MySQL 8.4.11, trimmed:

ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE orders CHANGE note comment varchar(150) NULL, ALGORITHM=INSTANT
ERROR 1845 (0A000) at line 3: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

ALTER TABLE customers RENAME COLUMN id TO customer_id, ALGORITHM=INSTANT
ERROR 1846 (0A000) at line 8: ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.

ALTER TABLE customers RENAME COLUMN id TO customer_id, ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
…
SELECT * FROM recent_notes LIMIT 1
ERROR 1356 (HY000) at line 13: View 'seo_mysqlmig.recent_notes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

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 you can find an open transaction before you rename.