先聊个真实的场景。有一天凌晨两点运维群里突然有人喊“线上订单查询全都卡死了”我上去一看一个再普通不过的SELECT * FROM orders WHERE order_id ...在被锁等待折磨了六十多秒。当时第一反应是查询怎么会锁表第二条SQL连上来还是卡住第三条、第四条全部排队。整个业务像被按了暂停键但CPU、IO、内存全都正常MySQL的线程状态里几乎全是Waiting for table metadata lock。这就是我写这篇文章的起因。很多人对“数据库查询是否锁表”这个问题理解停留在“SELECT又不写数据怎么可能锁表”的直觉上。但真实的生产环境不是这么简单。你跑一个慢查询它确实不锁表但一条慢查询拖住了DDLDDL反过来卡死所有后续查询这种事情在线上太常见了。今天这篇文章我就把MySQL查询与锁机制的关系彻底拆开从原理到锁类型从高频事故场景到完整排查链路一次性讲透。1. 一条查询卡死整个库先搞清楚锁从哪来1.1 MVCC为什么普通查询天生不锁表要回答“MySQL的查询到底锁不锁表”第一步必须先理解InnoDB引以为傲的MVCCMulti-Version Concurrency Control多版本并发控制机制。MVCC的核心思路非常聪明一份数据在数据库里并不是只有一份“最新值”而是会保留多个历史版本每个版本都带有一个事务ID时间戳。当你执行一条普通的SELECT查询时InnoDB会基于当前事务的Read View可见性视图顺着记录的版本链往回找找到一条“在当前时刻对你可见”的版本返回给你。这个过程中查询压根不需要去碰什么锁。它只是在读一份有历史版本的数据快照就好像你透过窗户看房间里的东西——你只是在看不需要敲门锁。这就是为什么所有教材都会告诉你普通SELECT不加任何锁它走的一致性读consistent read也叫快照读。在Repeatable Read可重复读隔离级别下同一个事务内的多次查询拿到的永远是同一个快照哪怕其他事务中途把数据改了提交了你也看不到。MVCC的出现就是为了让“读”和“写”互不阻塞。底线是如果没有MVCC读必须等写事务提交才能进行或者读会锁住整张表让写事务排队那并发性能就彻底没法看了。所以“普通查询不锁表”这句话从原理上是成立的但是注意它有严格的前提——事务没有长到不可控、查询走的是纯SELECT、没有加上任何锁读子句。真实世界的坑恰恰就出在“但是”之后。1.2 查询的两条路径快照读与当前读很多后端开发干了几年可能都没意识到MySQL里表面看起来都是SELECT实际走的却是两条完全不同的执行路径。快照读就是上面说的普通SELECT读历史版本不加锁。当前读SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE以及所有写操作UPDATE、DELETE、INSERT内部必须先执行的那次“读”。当前读读到的一定是最新已提交版本并且读完之后立即对记录加锁防止其他事务并发修改。这第二条路径才是“查询锁表”问题的真正战场。你执行UPDATE table SET ... WHERE ...InnoDB在执行计划阶段会先执行一个隐式的当前读把所有需要修改的行找出来并加上排他锁。如果这个查询的过滤条件命中不了索引MySQL只能走全表扫描那么理论上它扫描过的每一行都要加锁加到最后就相当于锁了整张表。所以我现在再回答一次“MySQL数据库查询是否锁表”纯粹的SELECT不锁表但查询的变体——写操作内部的那个“读”、带锁子句的读、以及因为糟糕SQL引发的范围扩大——全部都可能锁表甚至锁全表。这也是整篇文章的核心。2. InnoDB的锁家族行锁、表锁、元数据锁的分工2.1 行锁锁的是索引记录不是存数据的行如果你看过InnoDB的官方文档会看到一句非常重要的话InnoDB的行锁实际上是加在索引记录index record上的锁而不是直接锁在某一行物理数据上。这意味着如果你的表没有合适的索引可供MySQL使用InnoDB就只能退回到全表扫描最终的效果是——在表的所有记录上都加上了行锁。这也是面试和线上排查中最容易混淆的一点。很多人以为“行锁锁的是某一行数据”但实际执行计划里没有索引时InnoDB会对聚簇索引的全部记录挨个加锁。加上间隙锁gap lock的存在锁定范围还会扩展到记录之间的间隙。表现出来就是你明明只更新了一个WHERE name zhangsan结果整个表都写不了了。这已经不是“行锁”能解释的现象从业务视角来看它就是锁表。所以这里必须先给InnoDB的锁家族排一个谱锁类型作用对象典型来源是否影响普通SELECT行锁Record Lock索引记录UPDATE/DELETE/当前读不影响快照读间隙锁Gap Lock索引记录之间的间隙范围条件当前读不影响快照读临键锁Next-Key Lock记录间隙RR级别默认范围查询/走索引的UPDATE不影响快照读表锁Table Lock整张表LOCK TABLES、DDL等影响元数据锁MDL表的元数据DDL、DML影响间接2.2 表锁与MDL锁结构变更才是查询卡顿的常见元凶InnoDB下表锁并不像MyISAM那样高频出现真正让运维脑溢血的是MDL锁Metadata Lock元数据锁。MySQL 5.5之后引入了MDL机制用来保证在修改表结构ALTER TABLE、DROP TABLE等DDL操作期间不会有其他会话还在用旧表结构执行DML或查询否则数据会出错。MDL的设计很清晰普通查询、写入DML需要拿MDL的共享锁S锁DDL需要拿MDL的排他锁X锁。共享锁与共享锁兼容排他锁与所有锁互斥。也就是说如果有一个事务拿着MDL共享锁迟迟不提交后到的DDL就只能排队等待更麻烦的是由于锁等待队列是先进先出的排在DDL后面的所有新查询、新写入全都会被这个排队的DDL挡住。这个机制带来的连锁反应是我在线上遇到过的所有“查询被卡死”事故的根源。生产环境里经常是这样一个业务事务开启后执行了一条慢查询持有了MDL读锁没有释放这时候DBA在凌晨执行变更表结构的DDL命令它需要MDL写锁但发现拿不到于是进入等待在它等待期间所有新到的查询都需要MDL读锁可是写锁请求已经排在了队列最前面新的读锁请求全部被它压住。于是一个半小时前的那条慢查询让它之后的所有正常的、无辜的、微小的查询全部排队卡死。数据库CPU忙得很但所有业务全在等锁。这类事故本质上不是“查询锁表”而是“查询的慢”加上“DDL的急”共同制造的一场雪崩。2.3 当前读的关键锁gap lock 与 next-key lock如果只是锁住已存在的记录那InnoDB其实还防不住幻读。比如你用SELECT ... FOR UPDATE WHERE status pending锁住了两条pending记录这时另一个事务新插入了一条pending记录第一次查询的范围内就出现了一条“幽灵记录”幻读。为了应对这个问题InnoDB在Repeatable Read隔离级别下默认开启了间隙锁。间隙锁锁的不是哪条记录而是“记录之间那段空荡荡的间隙”。它保证在锁被释放之前任何事务都不能在这个间隙里插入新数据。而临键锁next-key lock则是“记录锁间隙锁”的合体既锁住记录本身又锁住记录前面的间隙。你执行一个范围条件查询InnoDB会把从第一个匹配记录到最后一个匹配记录之间的整个区间全部罩住。这就是为什么即使是走索引的UPDATE/DELETE只要条件范围稍微大一点它的加锁范围就可能大得超乎想象。你以为自己在改十条记录实际上下次插入新数据时可能被锁挡在门外。而普通SELECT依然毫发无伤因为它走快照读根本不关心这些行锁和间隙锁。3. 真实世界里“查询锁表”的五个典型场景3.1 长事务拖住undo连带阻塞DDL先说一个我踩过的坑。一个事务里面不仅有SQL还调了一个外部接口业务流程跑了快半小时才提交。在这半小时里它持有自己读过的那些记录的MVCC版本信息InnoDB的undo log里也保存着更早的版本数据。这时候数据库后台的purge线程发现有个很老的事务还活着版本链上有历史版本不能清理。于是undo log文件持续膨胀磁盘空间肉眼可见地往下掉。更直接的影响是这个长事务的事务ID非常老导致整个库里所有新产生的数据版本都要保留一条历史记录供它读取其他事务的读写压力都会上升。而如果你这时候恰好要执行一个DDL比如给某个重要表加索引MDL共享锁迟迟等不到释放在线DDL又不是真正的零锁——它需要在一个短时窗口内获取MDL写锁结果就是DDL被卡住接着引发连锁阻塞。所以“长事务”虽然不会主动锁表但它是锁问题的温床。代码评审时我很强调一点任何事务都要短平快严禁在事务里做远程调用、网络等待、消息发送这类IO操作。你让一个事务活太久不是它在锁表是它占着茅坑不拉屎后面的人全都等着。3.2 DDL排队一个慢查询引发的MDL雪崩把这次的连锁反应单独拉出来因为它在真实生产环境里出现过太多次。完整的时间线是这样的某个业务会话A执行了一条大表上的慢查询比如没走索引的SELECT COUNT(*)耗时正常需要一分钟。它持有了该表的MDL共享锁。DBA在这个时间点执行了ALTER TABLE t ADD INDEX ...。DDL需要MDL排他锁发现读锁没释放于是会话B进入“Waiting for table metadata lock”状态。注意这个等待没有超时时间DBA可能以为命令还在正常执行就一直等着。从MDL锁队列有等待者那一刻起MySQL对新的MDL读锁请求的处理规则就变了因为要让写锁先进入防止读锁不断插队导致写操作饿死。结果就是所有后续的普通SELECT全部排在DDL后面大家一起等。线上所有查询都变成Waiting for table metadata lock。在这个场景里查询本身没有加任何写锁是“一个从未提交的慢查询 一次DDL MySQL的MDL排队机制”三者的组合拳把整张表所有访问全部冰封住。事后复盘你会发现这三件事单拆开任何一件都不可怕但凑在一起就是雪崩。所以MySQL官方才推出了LOCK_WAIT_TIMEOUT参数来控制MDL锁等待超时可惜很多线上环境的默认配置并不合适后面我会细说。3.3 索引失效让UPDATE变成全表加锁数据库查询是否锁表第三个高频场景是由于索引问题导致的。假设表里有一个status字段分布非常不均匀你执行UPDATE orders SET status refunded WHERE status pending;如果优化器判断走status索引需要扫描的行数占比太高不如全表扫描划算它就会放弃这个索引转而扫描整个聚簇索引。在Repeatable Read隔离级别下InnoDB会在扫描过程中对每一行都加临键锁。哪怕你只是想改其中一小部分行但实际上所有被扫描过的行、以及行与行之间所有间隙全部被罩住了。业务后续的INSERT、UPDATE、DELETE全部阻塞表现和“锁表”一模一样。这里有个必须澄清的误区很多人说“行锁升级成表锁”这个说法在InnoDB里是不严谨的。InnoDB没有真正的“锁升级”机制它只是用大量行锁覆盖了全部行和间隙结果上等效于锁表。你通过performance_schema.data_locks去查会看到一条条锁记录但几乎覆盖了整张表。理解这一点很重要因为排查的时候你得意识到问题不是某个锁把表锁了而是索引选择失误导致一行接一行地被加锁。解决这类问题核心还是回到索引。一是确保WHERE条件上的字段有合适的索引二是注意函数、隐式类型转换、%like%这类会导致索引失效的写法三是如果优化器就是不走索引可以通过FORCE INDEX强制使用索引。这些都是在真实业务里挨过打之后的经验。3.4 FOR UPDATE 当前读引发的死锁如果你写过订单支付、库存扣减这类代码大概率用过SELECT ... FOR UPDATE。当前读是解决并发超卖的最直观手段但它也是死锁的重灾区。一个典型的双事务场景事务A先锁定了订单表order_id1然后再去锁库存表sku_id100事务B先锁定了库存表sku_id100再去锁订单表order_id1。如果两个事务刚好执行到中间步骤互相等待对方释放锁一个标准的死锁就出现了。MySQL默认开启了死锁检测innodb_deadlock_detectON它会维护一张等待图一旦检测到循环等待就会选择回滚其中一个代价较小的事务然后返回Deadlock found when trying to get lock。死锁这个东西很多团队第一次遇到时会被吓到觉得系统坏了。其实它是数据库的正常保护机制反而比“两个事务永远僵住不动”好得多。每次报错都说得很清楚哪个语句、哪个事务持有哪把锁。我建议线上保存死锁日志定期分析大部分死锁都和加锁顺序不一致有关。解决办法也很工程化所有事务内访问多个表时严格按同一顺序加锁。3.5 备份与大批量查询的隐性影响备份工具mysqldump在执行时会加全局读锁FLUSH TABLES WITH READ LOCK然后在备份开始后瞬间释放。如果不加--single-transaction它会用普通的SELECT去读数据这期间DDL会被挡在外面可能引发连锁。即便是用--single-transaction它也会开启一个REPEATABLE READ的一致性读事务长时间持有Read View导致重复清除跟不上。大批量的数据分析查询也是同样的道理。一条SELECT * FROM big_table跑半小时期间所有对这个表的DDL都得等它。它虽然没有锁任何行但它作为一条长时间的查询已经占用了MDL共享锁足以间接引发“查询卡死”的事故。所以我在团队里定的规矩是大查询必须评估影响面生产库上的分析请求要么走从库要么提前申请窗口绝不能直接一把梭。4. 锁等待排查的完整链路一个SQL一个SQL查4.1 先看当前有没有锁等待遇到查询卡死第一件事不是重启也不是盲杀会话而是先看当前有没有锁等待。我会先跑一条SQLSELECT * FROM sys.innodb_lock_waits\G这张视图会把当前正在等待锁的事务、它要的锁、谁拿着这把锁全部列出来。如果你用的MySQL版本比较老没有sys库就直接查performance_schema.data_locks和data_lock_waits或者走老牌工具SHOW ENGINE INNODB STATUS里面专门有一节是LATEST DETECTED DEADLOCK和TRANSACTIONS。还有一个经典排查入口是SHOW PROCESSLIST。看到大量Waiting for table metadata lock基本就能断定是MDL问题看到大量Waiting for next key lock、Waiting for row lock那就是行锁层面的竞争。4.2 找到持锁事务与阻塞源头定位到有锁等待之后下一个问题是谁在持有锁不放手。我常用这条SQL查当前运行中的事务及其状态SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;特别注意trx_started——如果一个事务已经跑了几十分钟那基本就是它在持续占锁。再结合sys.innodb_lock_waits给出的blocking_pid就能锁定真正的阻塞会话。SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;这时候你手里就有了两条信息谁被阻塞了通常是大量业务查询谁是阻塞源头通常是那个长事务或者大查询。如果blocking_query显示为ALTER TABLE且它是处于等待状态而非持有状态那要继续往上找谁持有MDL读锁——也就是最初的慢查询。这个“一层层回溯持锁者”的过程在事故复盘里非常关键。4.3 从慢日志还原时间线锁状态只能告诉你“当前谁在等谁”还原“这条锁链是怎么一步步形成的”还是得靠慢查询日志和binlog时间戳。我的做法是确认那个阻塞源事务后去看它的SQL是不是慢查询判断它大概什么时间点开始执行、预计什么时间点结束。再对照DDL的发起时间基本就能还原出“慢查询→DDL排队→全表阻塞”的完整链条。这一步排查习惯很重要因为如果你只杀掉阻塞DDL的会话下一次同类事故还是会发生只有分析出时间线才知道要改的是DBA的操作流程还是业务的SQL逻辑。5. 让查询远离锁问题的几个工程习惯5.1 索引是第一道防线所有锁问题的根源都可以沿着这条线往回倒锁范围扩大→全表扫描→没有合适的索引。所以把WHERE条件的索引建好是防空锁问题的第一道防线。一个走ref类型索引的UPDATE锁住的只是极少数记录和很窄的间隙一个走全表扫描的UPDATE锁住的就是整张表。不过索引也不是越多越好联合索引顺序、区分度都要评估。我的建议是对更新频繁的表优先保证高频更新条件走索引对大表可以上覆盖索引让查询不回表对生产环境的新增索引必须在低峰期用在线DDL工具操作避免在高峰期直接ALTER。5.2 事务要短锁要快放锁的持有时间取决于事务从开始到提交的完整时长。你可以在一条UPDATE里只加一个索引记录的行锁但如果这个事务在提交前去调外部HTTP接口、等人工审核、做一轮耗时计算那么这一小把锁就被握了十几秒甚至几分钟足以让并发高的业务爆发锁等待。所以我一直强调一个原则数据库事务里只做和数据库相关的操作。发消息、调API、写缓存、生成文件这些动作全部挪到事务提交之后做。如果一个业务流程确实复杂就改成领域事务拆分把长流程拆成多个短事务每段各自提交。这个习惯可以解决一大半说不清道不明的锁问题。5.3 把超时和并发控制在可失败的范围MySQL有两个重要参数值得关注。第一个是innodb_lock_wait_timeout默认50秒指的是普通行锁等待超过50秒后到的事务会报“Lock wait timeout exceeded”直接失败。这个默认值太长对业务来说等待50秒才失败体验已经崩了。我一般调成3~5秒让请求快速失败保护其他查询不被阻塞太久。第二个是MDL锁等待超时可以用lock_wait_timeout控制。MySQL 5.7之后支持在ALTER TABLE语句里指定等待时间ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHMINPLACE, LOCKNONE; SET SESSION lock_wait_timeout 5;配合max_execution_time限制单条SELECT的最大执行时间可以在源头阻止超长查询变成锁等待炸弹。线上宁可让一条慢查询报错退出也不能让它无限制地跑下去拖垮所有人。5.4 DDL需要工具化要不要上pt-osc/gh-ost如果是大表加索引、改列类型这类结构变更直接执行原生ALTER TABLE风险很大。它虽然号称在线DDL但执行过程中依然需要短暂获取MDL写锁而且在某些版本或某些特殊操作下会退化成锁表操作。更麻烦的是如果表数据量上万G原生DDL会复制整表数据期间持续占用大量IO资源对线上影响非常大。我在生产环境更倾向于用pt-osc或gh-ost这类工具。它们的核心思路是先创建一张新表、在旧表上创建触发器或利用binlog同步增量数据、把存量数据分批拷贝过去最后在极短时间内完成表切换。这个切换窗口只需拿一次很短的MDL写锁对业务的影响微乎其微。但注意工具不是万能的——有触发器冲突、外键约束、超大表空间等特殊情况时还是要回到评估和演练。6. 复盘一次典型的MDL锁雪崩事故6.1 事故现象与初步判断有一次我接手一个电商系统的排查现象是订单查询接口全部超时数据库监控面板上活跃会话数满屏。我当时第一句问值班同学“执行SHOW PROCESSLIST看State是不是Waiting for table metadata lock。”他回复“全是的”我心里基本就有了答案。这是一次非常教科书级别的MDL锁雪崩。接下来按顺序排查先查sys.innodb_lock_waits没有行锁等待记录再查information_schema.innodb_trx看到一个事务从凌晨两点开始trx_started已经快一个小时trx_query显示它是某个报表模块的SELECT还开着事务没提交。再从慢日志找到这个报表SQL的执行计划果然没有命中任何可用索引跑了整整二十分钟还没结束。6.2 根因链条把时间线拼出来整个过程是这样的凌晨两点某报表服务发起了大表查询由于没有索引SQL走了全表扫描执行时间很长持有订单表的MDL读锁凌晨两点十分DBA上线执行ALTER TABLE orders ADD INDEX ...需要MDL写锁但被读锁卡住进入等待队列从这一刻开始订单服务的所有新查询都因为这个排队的DDL而排队业务接口瞬间被打满。整个链条里没有一条SQL在主动写数据但整个订单表对外表现为“完全不可用”。这事给我们的教训是深刻的第一报表查询没有走从库直接打主库第二大查询没有设置max_execution_time让它无限执行第三DBA的变更窗口和业务高峰没有错开变更前也没有检查是否存在长时间运行的查询。这三条任何一条做到位这次事故都大概率不会发生。6.3 修复动作与事后改进修复的时候我没有急着去杀DBA的ALTER语句而是先处理根因把那个持有MDL读锁的长查询会话KILL掉。MDL读锁一释放排队的ALTER第一时间拿到了写锁很快就执行完了写锁释放后所有排队的查询立刻恢复。整个过程大概十秒业务就回归正常了。如果你反过来先杀DDL会释放写锁请求队列新查询倒是能恢复但表结构变更依然没做成属于治标不治本。事后我和团队一起做了几件事给报表查询涉及的所有过滤字段补上索引在报表服务侧强制走只读从库给核心表的DDL统一改用pt-osc并在非高峰执行把lock_wait_timeout设成5秒监控里把所有Waiting for table metadata lock状态当作P0告警。这些改动落地之后半年内没再发生过第二次同类事故。关于“数据库查询是否锁表”我在实际运维中最大的体会就是别把这句话当作一句静态的结论去背而是要理解它背后有条件。普通查询靠MVCC确实不锁表但查询的慢、事务的长、索引的缺、DDL的急这些因素组合在一起就会让表象变成“查询把表锁死了”。排查的思路永远是从锁等待出发一层层追到持锁者找到根因而不是看到查询卡住就重启数据库或者盲目杀会话。如果你能把本文提到的原理和排查链路消化掉以后再遇到“数据库查询是否锁表”这类问题我相信你第一反应不再是翻文档而是直接打开数据库看看到底是谁在排队、谁在持锁。