Create INDEX blocks writes to other tables

MySQL & MariaDB Other MySQL® Questions
1

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?

2

Looks strange,

Can you send output of processlist when it happens ?

I assume they stall forever while alter table is completed as otherwise it may be some kind of IO stall or anything.

Also it is possible for some other query to try to access both the table you’re altering and the one which you’re inserting into and as one of table can’t be locked it also stalls stalling everything else.