Does ALTER TABLE - ADD COLUMN lock the table?

Yes, it locks the table. From the docs on MySQL 8,

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

And from the docs you linked, it's pretty explicit

With instant ADD COLUMN, you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.

As you've stated, you're on 10.2. So it looks like adding a column will require rebuilding the whole table.

As to what happens when you can't receive a lock,

I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Yes, that's generally what happens during a lock, but be aware that's not always what happens. Sometimes statements and transactions give up waiting. Sometimes backends and pools get reaped when they're stuck waiting. It's always safer to do this during downtime, to have timeouts, and to catch errors from libraries when the timeouts expire. So long as you're using transactions, things rollback if something is triggered and can't get it's lock before timeout -- all will be kosher.


it actually depends on the specific alter table, but in any case I would suggest looking into online schema change tools like ghost (from GitHub) or pt-online-schema-change (by percona).

Both tools do the change on a separate table and switch with the original at the end - you can even keep the old one for fast rollback.

It’s a much safer route especially for heavy loaded tables