Search code examples
mysqldatabaseserverinnodb

What happens to an insert when a mysql(innoDB) table is being altered(locked)?

I am trying to ALTER a database column by using:

ALTER TABLE [table name] MODIFY id bigint;

I searched and found out that the table gets locked during the ALTER process.

How do the inserts get affected during the ALTER process? Do they wait until the lock is released? Is there a timeout?

If the inserts wait for the lock to be released, is there a limit of how many inserts that could wait?

Solution

  • When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

    When the ALTER TABLE finish the convertion all pending DML will execute.

    The time that it'll take depends on the amount of data the table have.