Reference MySQL 5.7
https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html
show open tables
用于查看现在打开了哪些表(不包括临时表)。主要用来在flush table回硬盘后,再看看这个命令的输出就会发现哪些表是很活跃的,哪些表是非活跃的。判断活跃性后,可进行相应表的flush和备份。另外结合show processlist也可以看到具体那个用户和线程锁定了某个具体的表。
show open tables from xxx-db where in_use > 0;
help show open tables \\G;
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
help lock tables \\G
A table lock protects only against inappropriate reads or writes by other sessions. A session holding a WRITE lock can perform table-level operations such as DROP TABLE or TRUNCATE TABLE. For sessions holding a READ lock, DROP TABLE and TRUNCATE TABLE operations are not permitted.
-- session1
lock tables tweets write;
show open tables where in_use > 0;
unlock tables;
-- session2
lock tables tweets read;
-- wait session1 unlock
unlock tables;