I noticed a problem that I cannot explain. I hope that you can help me on this.
When I do “ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY …” on a vary large MYISAM table, I noticed that updates/inserts to other MYISAM tables are ‘blocked’. Once the “ALTER” statement is done, all the updates statements are processed in a few seconds.
Those updates/inserts are very simple and quick primary key based operations on totally DIFFERENT tables. Without this large “ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY …” running concurrently, this updates/inserts can be down in fraction of seconds.
It is not table lock problem since the ops are on different tables. Is there some critical resources, (Key buffer, sort buffer, tmp space), used by “Index Creation” that can cause this problem?