It's normal that table-metadata-lock shows up for a short time when
ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, and I'm sure there is no long sql statement running before adding index.
What's more confused is that I saw the ALTER statement copying to tmp table in processlist. According to official docs innodb-create-index-overview,
INPLACE method should be taken by default, right? ( I just add one secondary index and delete one).
Also I checked
information_schema.innodb_trx to see whether uncommitted transactions exist. No.
Can someone could explain why this happens for me?
By the way, my (alter) table t1 has triggers on it. And there's trigger on other table t2 to update t1. Thought I know it's ugly ·_·