3

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?

2

What version of MySQL are you using?

Have you considered ALGORITHM=inplace and LOCK=none in your ALTER TABLE statement?

See also Using the LOCK clause to control concurrency

  • I am using MySQL version 5.7. I looked at the document you provided, is this saying that insert requests can be taken during a alter process if you choose to make the lock clause equal to none? – jose tanaka Jun 17 '17 at 5:00
  • @josetanaka That is my interpretation--in other words, yes. – KXNV-89.1FM Jun 17 '17 at 5:50
1

There are tools that can help you with this, they essentially copy the structure of the table perform the change and then do an insert into the new table.

The Percona toolkit is an example that comes to mind.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.