一个只在凌晨出现的表锁
去年冬天接手的一个图片站，每天凌晨两点零几秒开始，网站会白屏三到五分钟，之后自己恢复。白天怎么压都压不出来，日志里也没有 PHP 错误。真正留下痕迹的只有 MySQL 慢查询日志里一条记录：SELECT ... FROM posts WHERE status = 'publish' ORDER BY created DESC LIMIT 10，耗时 214 秒。这条 SQL 本身走的是索引，白天执行 3 毫秒。
往上翻时间线才对上：凌晨两点整，站上有个统计脚本会跑一次「重建归档表」，做法是 TRUNCATE TABLE archive_tmp、INSERT ... SELECT 灌数据、然后 RENAME TABLE 换名。问题出在那条 INSERT 上——它跑了四分钟，而在这四分钟里，所有读 archive_tmp 的请求都被堵住了。不是 InnoDB 行锁，而是表级锁。
这篇把这个坑拆开讲清楚：MySQL 里有哪几层锁、表锁是怎么被「看不见地」加上的、怎么在线上确认锁到底在谁手上、以及统计报表这类需求应该怎么做才不锁表。行锁与死锁我在另一篇里单独写过，这里只谈表级这一层，两者排查手段完全不同，混在一起查会走很多弯路。
MySQL的四层锁，先分清再排查
很多人一说到锁就只想到行锁，实际上 MySQL 里的锁至少分四层，触发条件、持有时间、观测手段都不一样：
- 全局锁：
FLUSH TABLES WITH READ LOCK，整库只读，备份工具会用。持有期间所有写操作排队。 - 表级锁：
LOCK TABLES ... READ/WRITE显式加的，以及 MyISAM 自动加的。粒度是整个表。 - 元数据锁（MDL）：任何 DDL 或访问表的语句都会自动加，用来防止「有人在改表结构时有人在读」。这是 8.0 里最容易造成雪崩的一层。
- 行锁：InnoDB 在执行 UPDATE/DELETE 时加在索引记录上，加上间隙锁、临键锁。死锁多发在这一层。
关键点在于：MDL 和表锁都是表级粒度，但它们是两套完全不同的机制。MDL 由数据库自动管理、看不见 DDL 之外的来源；表锁则有明确的加锁语句。排查时如果只查 SHOW OPEN TABLES 或者只查 data_locks，很可能两边都看不到真相。
一句话记住区别：行锁是「这一行我在改，别人别动」，表锁是「这张表我在整块占用」，MDL 是「这张表的结构我在用，谁也别想改结构」。
表锁被谁加上的：五种常见来源
先说结论：在没有 MyISAM 表的现代站点里，显式 LOCK TABLES 反而是少数。更多的是下面这些来源，它们在监控里全都表现为「某张表被锁住」：
显式LOCK TABLES与UNLOCK的遗留
老代码、老插件、老备份脚本里常有 LOCK TABLES wp_options WRITE 这种写法，尤其是从 phpMyAdmin 导出的批量导入脚本。危险之处在于锁是不会自动释放的，它绑定在连接上。如果脚本中途 fatal error 退出，连接被 PHP 回收或断掉，锁才释放；但如果是长连接（pconnect）或者脚本还在跑，锁就一直挂着。
-- 查谁在用表锁（8.0）
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,
LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
-- 从线程反查是谁
SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE THREAD_ID = 12345;这里有一点很容易被忽略：metadata_locks 表里既有显式的表锁，也有 MDL。LOCK_TYPE 为 SHARED_READ / SHARED_WRITE 的是 MDL，为 SHARED_NO_READ_WRITE 或 EXCLUSIVE 的多半是表锁或 DDL。
LOCK_STATUS 列在这张表里最有用：GRANTED 是已经拿到，PENDING 是在等。看到一个 PENDING，就说明它后面排着一串被阻塞的语句，这往往是事故正在扩大的瞬间。
- 备份脚本：
mysqldump不加--single-transaction时会用LOCK TABLES ... READ，对大表来说这个读锁能挂几十秒。 - MyISAM 表：这类表根本没有行锁，一条 UPDATE 就锁整张表。有些老主题的统计表、会话表还是 MyISAM，混在 InnoDB 库里极难发现。
- 统计报表：
INSERT INTO ... SELECT、CREATE TABLE AS SELECT、OPTIMIZE TABLE、ALTER TABLE都会在源表上加锁。 - DDL 引发的 MDL 队列：这是最阴的一种，下面单独讲。
判断一张表到底是不是MyISAM
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'zz1984' AND ENGINE <> 'InnoDB';返回值不为空就说明库里有非 InnoDB 表。这类表在「表锁」问题里是重灾区，因为它们没有行级并发，一条慢 UPDATE 就等于整表不可写。
顺带说个判断技巧：MyISAM 表的表锁在 SHOW PROCESSLIST 里表现为 State 列出现 Waiting for table level lock，而 InnoDB 的 MDL 等待是 Waiting for table metadata lock。两个字符串差三个单词，含义完全不同——前者要去找谁在写这张表，后者要去找谁在改这张表的结构。
MDL队列雪崩：一次DDL堵死整个站
这是我遇到最多的表级事故形态，也是最容易被误判的。过程是这样的：
- 有人在业务高峰给一张大表加索引，DDL 开始，拿到 MDL 的排他锁；
- 此时一个长事务（比如某个报表查询）还没结束，它持有 MDL 的共享读锁；
- DDL 排在长事务后面等；
- 而 DDL 之后的所有 SELECT，都要排在 DDL 后面等；
- 几秒钟之内，这张表上所有请求全部排队，站点白屏。
注意第 4 步：DDL 自己没锁住任何人，是它「排队」这件事本身锁住了后续所有人。这就是 MDL 队列雪崩，跟行锁死锁完全是两回事。
确认方法很简单，看 SHOW PROCESSLIST 里是否有大量 Waiting for table metadata lock，并且其中一条线程的 State 是 Waiting for table metadata lock 而 Command 是 Query 且 Info 是 ALTER TABLE 或 CREATE INDEX。找到最前面那个持有共享 MDL 的长事务，KILL 掉它（或者 KILL DDL），队列立刻疏散。
-- 找到持有 MDL 且事务还没提交的会话
SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_TIME,
t.PROCESSLIST_INFO, trx.trx_started, trx.trx_rows_modified
FROM performance_schema.threads t
JOIN information_schema.innodb_trx trx
ON trx.trx_mysql_thread_id = t.PROCESSLIST_ID
ORDER BY trx.trx_started;预防手段有三条，都很便宜：
- DDL 执行前设
SET SESSION lock_wait_timeout = 10;，让它 10 秒拿不到锁就报错退出，而不是无限排队拖死全站。 - DDL 前检查
innodb_trx里有没有运行超过 60 秒的事务，有就先处理。 - 给 DDL 显式加
ALGORITHM=INPLACE, LOCK=NONE，不支持时报错而不是悄悄降级成拷贝表。
用四个视图把锁的现场还原出来
出事故时不要凭感觉猜，按下面的顺序问四句话：谁在等、等什么、谁挡着它、挡了多久。
-- 1) 谁在等锁（8.0，一眼看出阻塞链）
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
wait_age, locked_table
FROM sys.innodb_lock_waits;
-- 2) 表级与 MDL 的等待
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';
-- 3) 长事务（多数锁的根都在这里）
SELECT trx_id, trx_mysql_thread_id, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
trx_rows_locked, trx_state
FROM information_schema.innodb_trx
HAVING age_sec > 30;
-- 4) 表当前是否被打开/占用
SHOW OPEN TABLES WHERE In_use > 0;sys.innodb_lock_waits 是 5.7 以后自带的视图，把 data_locks 和 data_lock_waits 拼成了人能读的一张表，比手工 JOIN 强太多。blocking_pid 就是元凶，直接 KILL 掉（注意：KILL 一个大事务会回滚，可能比等更慢，见下）。
SHOW OPEN TABLES 的 In_use 列在表锁场景下意义不大——它统计的是「表被打开的实例数」，不是锁。把它当成锁的判据是常见误判。
杀掉阻塞者之前，先算一下代价
这条经验值很多次深夜电话：KILL 一个跑了一半的大事务，回滚时间往往比它继续跑更长。一个已经写了 500 万行的事务，KILL 之后 InnoDB 要从后往前撤销这些改动，期间还占用 undo 空间和 IO，可能拖十几分钟。而如果你不管它，也许再过两分钟就结束了。
判断方法：看 innodb_trx 的 trx_rows_modified。如果是几万行以内，KILL 划算；如果是百万级以上，先看 undo 大小和主从延迟，再决定是等还是杀。同时要给业务侧一个明确动作：把维护页打开，比让用户盯着白屏好。
还有一个更稳妥的做法：先摘流再处理。把这张表相关的入口（比如归档页）在 Nginx 上临时 return 503，让等待的请求根本不进 PHP，然后再从容处理数据库。这样即使事务回滚十分钟，用户看到的也只是「维护中」，而不是超时。
统计报表不要锁表：三种替代做法
回到开头那个凌晨事故。需求本身是合理的（每天要有一份归档快照），错的是做法。三种改法，按成本从低到高：
做法一：从库上跑，主库只服务业务
如果有从库，统计脚本全部连从库。从库被锁住不影响前台。这是最省事的方案，代价是要接受主从延迟（通常不到 1 秒，报表场景完全可接受）。连接串里把 host 换成从库地址即可，但要注意从库只读，INSERT INTO archive_tmp 这种写法在从库上会失败，所以归档表要么建在另一个实例，要么改成「从库读 + 主库写小批量」。
做法二：分批写入，把大事务切成小事务
-- 每批 2000 行，批间 sleep，锁持有时间从分钟级降到毫秒级
INSERT INTO archive_tmp (id, post_id, views, day)
SELECT id, post_id, views, day FROM post_stats
WHERE id > @last_id AND day = CURDATE() - INTERVAL 1 DAY
ORDER BY id LIMIT 2000;关键点：每批单独提交，并且用主键范围推进（id > @last_id），不要用 LIMIT offset，因为 offset 越大扫描越慢，越到后面越慢，等于把锁的时间又拉长了。批间 SELECT SLEEP(0.2) 给其他事务让路。
做法三：改需求——预聚合表
如果报表只是「每天看一次访问量排行」，根本不需要全量重算。加一张 daily_counter 表，业务写入时用 INSERT ... ON DUPLICATE KEY UPDATE count = count + 1 就地累加，报表直接读这张小表。这是最彻底的解法，因为大事务从一开始就不存在。
三种做法的选择标准：有从库就用一，几十万行以内用二，长期天天跑就用三。不要用「半夜没人所以可以锁」作为理由——现在的站点都有海外访问和爬虫，半夜的流量并不为零，而且事故一旦发生在半夜，发现和恢复都更慢。
一张检查单
最后把这套流程压成一张单子，出事时按顺序走，不要跳步：
- 第一步，看
SHOW PROCESSLIST的 State，区分Waiting for table level lock与Waiting for table metadata lock，两条路完全不同。 - 第二步，查
performance_schema.metadata_locks里LOCK_STATUS = 'PENDING'的记录，找到最前面那个。 - 第三步，查
information_schema.innodb_trx，把age_sec > 30的会话列出来，多数元凶在这里。 - 第四步，查库里的非 InnoDB 表，MyISAM 是表锁的高发区。
- 第五步，KILL 之前先看
trx_rows_modified，百万级先摘流再决定。 - 第六步，事后把统计脚本改成从库、分批或预聚合，并且给 DDL 加
lock_wait_timeout。
表锁不可怕，可怕的是把它当成行锁去查——你在 data_locks 里翻半天什么也看不到，而真正的元凶在 metadata_locks 里挂着。分清层次，一次就能定位。