optimize table 锁表吗

Question

mysql版本是5.6 用的阿里云的rds 之前看网上大神说 optimize table 会锁表 今天自己测试了一下 在optimize table期间 执行 update delete操作都是可以的
请问这是为什么

另外问一句 如何知道一条语句 /命令 是否会锁表 有什么能查被锁定的表

Answer 1

show OPEN TABLES where In_use > 0; 这个SQL语句可以查看当前哪些表被锁了
SHOW PROCESSLIST; 可以查看当前正在执行的sql语句 和执行状态

Answer 2

OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

innodb只会在prepare和commit阶段锁表 MyIASM会一直锁表 两个引擎的操作方法不一样