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?

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
2
  • 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, 2017 at 5:00
  • @josetanaka That is my interpretation--in other words, yes.
    – KXNV-89.1FM
    Jun 17, 2017 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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