数据库出问题的时候最能让人血压飙升的场景之一是表被锁住了。你在业务后台点了一下查询页面转圈半天不返回去命令行敲SQL等了几分钟才报错要么是“Lock wait timeout exceeded”要么是“Deadlock found when trying to get lock”。搞得团队群里一片哀嚎领导催你赶紧处理。别慌这篇文章就是干这个用的怎么快速查出MySQL里被锁住的表和锁的来源又怎么在不坑队友的情况下解锁顺便聊聊怎么从根上减少锁表发生的次数。适合后端开发、运维、以及所有被数据库锁问题折磨过的同学参考读完你至少能自己动手定位和解掉八成以上的锁问题。1. 先把锁这件事想明白你遇到的究竟是哪把锁不看清楚问题类型就急着清锁很容易帮倒忙。MySQL官方文档里用LOCK、LATENCY这些词来描述锁但真正干活的时候我们得先区分开“表级锁”和“行级锁”这两大类。1.1 表锁与行锁的区别以及各自的表现MySQL的存储引擎里MyISAM用的是表级锁写锁会阻塞同一张表的所有读写InnoDB则主要使用行级锁锁粒度更细并发能力也更强。当前绝大多数业务库都是InnoDB所以你遇到的锁问题八成以上是行锁冲突或者锁等待而不是整张表被锁死。但“锁表”这个说法在业务口里经常是泛指——用户说“表被锁了”实际情况可能是以下三种之一一个事务持有了部分行锁另一个事务要更新同一批数据一直在等表现为SQL卡住不返回有人执行了DDL操作比如ALTER TABLE、OPTIMIZE TABLE在拿到表的元数据锁MDL锁之前所有访问这张表的查询都在等待长时间未提交的事务持有了行锁或间隙锁导致新到的写请求全部排队。理解这点很重要你看到的“锁表”未必是LOCK TABLES语句造成的更多时候是事务并发冲突。查询和解锁的入口也因此不同下文会分开讲。1.2 谁在用锁锁存在哪里事务、线程、锁等待要理清锁问题得先把几个关键概念串起来。InnoDB里事务持有锁而事务最终由一个后台线程执行。MySQL为每个客户端连接分配一个线程你执行SQL时当前事务就和这个线程绑定。当事务A持有某行锁时事务B的更新请求会进入等待状态。这个等待关系被记录在系统表里MySQL提供了information_schema库下的INNODB_TRX事务表、INNODB_LOCKS锁信息表、INNODB_LOCK_WAITS锁等待关系表8.0版本对应的是performance_schema下的data_locks和data_lock_waits。查询被锁住的表本质就是查询这几张表找到锁的持有方和等待方然后决定是等待超时还是手动终止。作为参考我对你提到的“锁等待超时”场景做一个常见原因归纳后面排查时可以直接对着查现象可能原因涉及锁类型单条UPDATE卡住目标行被其他未提交事务修改行锁Record Lock批量UPDATE大面积阻塞多个事务更新了相邻范围数据间隙锁Gap Lock、临键锁Next-Key Lock查询也打不开有DDL正在执行或持有MDL锁元数据锁MDL Lock死锁报错两个事务互相持有对方需要的锁行锁/间隙锁组合2. 三步定位法找到到底谁锁住了表很多同学一上来就执行SHOW PROCESSLIST看半天看不出名堂因为线程列表里全是正常的SELECT真正的问题事务隐藏在“Sleep”状态里。下面分享我一直在用的三步定位法。2.1 第一步先用SHOW PROCESSLIST快速摸排第一步简单直接先看现场执行SHOW FULL PROCESSLIST;重点看Info列是否有长时间卡住的DMLINSERT/UPDATE/DELETE语句以及State字段是否为“Waiting for table metadata lock”或“Updating”。如果能看到一条UPDATE处于“Updating”状态持续很久它就是被锁等待的一方但注意它不一定是锁的持有者。锁的持有者往往是另一个会话里那个看起来“什么都没做”的Sleep连接——事务开着执行了UPDATE但没提交然后空闲在那。这种连接在PROCESSLIST里可能根本看不出问题因为它当前没有正在执行的SQL。所以要继续第二步查系统表。2.2 第二步查询INNODB_TRX与INNODB_LOCK_WAITS定位事务这一步需要用户有PROCESS或SELECT权限。执行下面的组合查询直接找出正在等待锁的事务以及它等待的是哪个事务持有的锁。先看当前有哪些正在运行的事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.INNODB_TRX;trx_state为“LOCK WAIT”的事务就是在等锁的trx_mysql_thread_id对应着可以KILL的线程ID。然后看锁等待关系SELECT waiting_trx_id, waiting_thread, blocking_trx_id, blocking_thread FROM sys.innodb_lock_waits;如果你使用的MySQL版本没有sys库比如部分精简过的实例可以用information_schema自己关联查询SELECT w.trx_id AS waiting_trx_id, w.trx_mysql_thread_id AS waiting_thread, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread FROM information_schema.INNODB_LOCK_WAITS lw JOIN information_schema.INNODB_TRX w ON lw.blocking_trx_id w.trx_id JOIN information_schema.INNODB_TRX b ON lw.blocking_trx_id b.trx_id;2.3 第三步查看锁对象与被锁的表锁等待关系确定了阻塞双方但还得知道锁具体落在哪张表、哪些行上这样才能判断能不能安全解锁。8.0以上版本执行SELECT * FROM performance_schema.data_locks;重点看OBJECT_NAME列表名、LOCK_TYPERECORD表示行锁TABLE表示表级锁、LOCK_MODEX、S、GAP等以及LOCK_DATA锁定范围的关键字比如具体的索引值。5.7版本的对应表是INNODB_LOCKS查询方式类似。实际工作中我习惯把这几步合并成一个总览SQL一步到位看到所有信息SELECT pl.id AS waiting_pid, ps.id AS blocking_pid, pl.time AS wait_age, pl.state AS wait_state, pl.info AS wait_query, ps.state AS blocker_state, ps.info AS blocker_query FROM performance_schema.threads pt JOIN information_schema.processlist pl ON pt.processlist_id pl.id JOIN information_schema.processlist ps ON pt.processlist_id ( SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX WHERE trx_id ( SELECT blocking_trx_id FROM information_schema.INNODB_LOCK_WAITS WHERE waiting_trx_id pl.id ) );这里需要解释一个细节information_schema.processlist里的ID是基于线程的和SHOW PROCESSLIST看到的ID一致。如果你用的客户端工具比如某款数据库管理工具内部复用了连接可能ID有偏移最稳妥的做法是直接在MySQL命令行执行别经过连接池中转。3. 解锁实操怎么安全地解开被锁住的表定位到了锁的持有者之后解锁操作本身并不复杂就是KILL掉那个阻塞线程。但KILL谁、怎么KILL、KILL之后会不会引发更大的问题这里面的门道值得细讲。3.1 用KILL终止阻塞事务拿到第二步查询结果中的blocking_thread也就是持有锁的线程ID执行KILL 12345;12345替换成实际的线程ID。这个操作会强制终止阻塞事务对应的连接事务回滚持有的锁随即释放等待中的SQL就能继续执行。补充说明一下KILL的两种用法KILL 12345终止整个连接如果这个连接正处在事务中事务会回滚KILL QUERY 12345只终止当前正在执行的SQL语句连接本身保留事务不结束锁可能仍然被持有。对于“事务开了没提交”这种情况必须用KILL即KILL CONNECTION哪怕那个连接当前是Sleep状态因为事务还悬着。这点常规操作中容易忽略只看PROCESSLIST里没有活跃SQL就以为没事结果锁一直解不开。提示KILL之前务必确认这个连接是不是业务连接池里的常驻连接。如果是KILL掉后连接池会自动重建通常影响可控但如果那个连接正在执行批量任务KILL会导致该任务回滚重放的逻辑得业务侧自己兜住。3.2 批量生成KILL语句的偷懒方案线上锁等待往往不只一条Scrum会议上连环卡住十几个会话的场景我见过太多次。一个个去查再一个个KILL效率太低了。可以用SQL生成SQLSELECT CONCAT(KILL , trx_mysql_thread_id, ;) FROM information_schema.INNODB_TRX WHERE trx_state LOCK WAIT;把查询结果复制出来逐条执行。更省事的方式是直接在命令行用管道处理但日常运维中我建议先复制、审查、再执行避免误杀。生成的结果大概像这样KILL 1122; KILL 3344;执行后再回到第一步确认没有新的LOCK WAIT事务出现并且阻塞线程ID消失即可。3.3 解锁后的验证与恢复解锁不是KILL完就万事大吉了。KILL掉阻塞事务后等待中的事务会尝试获取锁并继续执行此时观察两点一是事务回滚的时间。锁持有的事务如果已经修改了大量数据回滚也需要时间期间对应行仍然会处于“正在回滚”的内部状态新事务访问这些行可能短暂等待。可以通过SHOW ENGINE INNODB STATUS\G查看History list length和事务状态确认回滚完成。二是业务是否报错。KILL操作会让应用端收到一个“Connection was killed”之类的错误如果你的业务代码没有重试机制用户可能看到一条失败提示。所以解锁前最好和业务方沟通一下或者选在流量低峰操作。3.4 特殊场景元数据锁MDL的处理前面提到ALTER TABLE这类DDL会持有MDL锁导致后续所有查询都卡在“Waiting for table metadata lock”状态。这种情况的解锁思路不太一样——你去KILL那个等待中的查询没用因为DDL持有的锁根本不在事务表里。正确做法是找到正在执行DDL的会话或是那个“持有锁但处于空闲”的会话把它KILL掉。查询MDL锁的通用方式SELECT p.id, p.state, p.info, p.time FROM performance_schema.events_statements_current e LEFT JOIN performance_schema.threads t ON e.thread_id t.thread_id LEFT JOIN information_schema.processlist p ON t.processlist_id p.id WHERE p.state LIKE Waiting for%;MDL锁的排查依赖performance_schema的wait相关表日常操作中也可以直接结合PROCESSLIST里的State信息判断一堆会话都卡在“Waiting for table metadata lock”说明有一个未完成的DDL或者一个持有表级MDL锁的长事务存在顺着会话列表往前翻找到State是“Waiting for table metadata lock”之前执行过DDL且尚未断开的连接KILL掉它即可。4. 治本思路从源头上减少锁表发生工具和方法解决的是当下但锁表如果三番五次出现根子一定在某处代码或某个操作习惯上。解锁解一百次不如让这块地儿根本种不出雷来。以下都是我在项目中验证过、见效明确的手段。4.1 SQL与索引优化让锁影响的范围最小化锁的粒度是由索引决定的。如果没有走索引InnoDB只能锁全表记录实际上是对所有扫描到的行加锁表现上非常接近表锁并发一高直接雪崩。一个典型的坑是UPDATE语句的WHERE条件列没有索引UPDATE orders SET status 1 WHERE user_phone 13800138000;如果user_phone没有索引这条UPDATE会全表扫描并对所有扫描过的行加锁不仅仅是目标行。同一秒内另一个事务更新另一条记录也会被堵住。解决方式就是给高频查询条件加索引ALTER TABLE orders ADD INDEX idx_user_phone(user_phone);另外配合EXPLAIN分析执行计划确认type不是ALL或indexrows扫描量要控制在合理范围。这段经验经常被忽略但真实事故里锁表问题的第一名凶手就是“更新了大表但没用上索引”没有之一。4.2 事务设计纪律小事务、短事务、不闲等我在复盘过某个线上事故后总结出一条铁律事务里绝不包含远程调用、外部接口请求、长时间循环或者人为睡眠sleep。很多事务看上去很小就是一条UPDATE但代码里事务前面调了个第三方接口500毫秒超时事务还没提交锁就多占500毫秒如果平时接口再抖一下事务里累积个几秒锁等待就来了。比这更隐蔽的是事务里的一堆SELECT看似只读不需要锁但在可重复读隔离级别下这些SELECT可能会产生间隙锁。也就是说一个本应该很快读完的事务如果循环里做了大量查询和计算也可能把锁范围撑大。因此事务内只保留必要的UPDATE/DELETE/INSERT查询类逻辑尽量提到事务外事务耗时目标控制在100毫秒以内超过这个值就要警惕。另外程序里一定要确保事务的finally块里能关闭连接或者回滚避免异常情况下事务悬空。4.3 参数层面的调优超时与死锁检测MySQL两个参数对锁问题影响直接innodb_lock_wait_timeout控制等待锁的超时时间默认50秒我一般建议业务库调小到5到10秒。这样一旦发生锁等待业务侧能快速感知并报错而不是所有请求堆积五十秒最终引发雪崩式的阻塞。修改方式SET GLOBAL innodb_lock_wait_timeout 10; SET SESSION innodb_lock_wait_timeout 10;同时确认innodb_deadlock_detect处于开启状态。这个参数负责死锁的主动检测检测到死锁时会自动回滚其中一个事务并报错。我把这个参数和超时参数并列推荐是因为曾经遇到过一个把deadlock_detect关掉的实例结果死锁没人管双方互等直到超时系统整整卡了十几分钟。默认开启的状态下不要轻易关掉。4.4 大表DDL和批量更新的分阶段策略DDL操作建议避开业务高峰并且使用在线DDL工具。这里我直接推荐pt-online-schema-changePercona Toolkit里的工具但如果环境不便使用也可以考虑gh-ost这类工具通过临时表和数据同步模拟执行的DDL把对在线业务的影响降到最低。相比直接ALTER TABLE它们不会长时间持有MDL锁锁表时间从几十分钟压缩到几秒甚至更短。批量更新数据同样别一把梭子UPDATE整表或者整个分区分批提交UPDATE big_table SET status 2 WHERE status 1 AND id BETWEEN 1 AND 10000;每批更新1万到5万条批次之间间隔几秒让其他事务有插队执行的机会避免一个超大事务长时间霸占锁资源。实测下来这种分批更新方案比一条巨型UPDATE稳定得多尤其在数据量千万级以上的表上差距非常明显。5. 常见问题与排查技巧实录处理锁问题这几年遇到的坑五花八门但高频场景翻来覆去也就那么几个。整理一个速查表加上复盘心得满足日常90%以上的排查需求。5.1 冻结现场锁问题排查的标准动作锁问题出现时最忌讳的是手忙脚乱乱操作。我的标准动作是这样第一步全量抓现场SHOW FULL PROCESSLIST;别只截一段把全部会话拿到。第二步查锁等待关系SELECT * FROM sys.innodb_lock_waits;如果sys库不可用用前面information_schema的关联查询。第三步查事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.INNODB_TRX;第四步如果怀疑MDL锁打开performance_schema的等待事件表确认是否大量会话都在等待元数据锁。整个流程最多两分钟比等到应用超时再去猜高效太多。5.2 常见误区与避坑第一个坑查PROCESSLIST找到一条长SQL直接KILL结果锁没解开。原因通常是长SQL只是众多排队的后续请求不是锁源。真正的锁源可能在它的上一个生命周期里已经完成了SQL但事务没提交连接显示Sleep。这类“Sleep的锁持有者”是最难抓住的务必结合INNODB_TRX查而不是只看PROCESSLIST。第二个坑KILL了错误的对象。特别是有些管理工具会显示“会话”而不是“线程ID”如果你把工具里的会话ID误当成KILL参数终端会报错浪费时间。牢记KILL后面的数字是trx_mysql_thread_id不是trx_id也不是工具里的会话编号。第三个坑刚解锁完锁又出现了。这时候别慌着继续KILL要意识到是同一批SQL请求还在源源不断地打过来新的会话可能是同一个事务重试也可能是同一批任务并发启动。此时优先看是不是定时任务、批处理脚本并发跑必要时临时停掉相关任务再清一次。第四个坑MySQL实例有主从复制时在主库KILL会话从库的SQL线程可能还在执行相同的事务需要看复制是否有延迟。如果从库正在应用一个长时间事务主库清完后从库还要跑一会儿才能恢复。5.3 锁问题排查速查表场景核心查询/操作预期结果看到“Lock wait timeout exceeded”查INNODB_TRX找到LOCK WAIT事务定位阻塞者线程ID看到大量“Waiting for table metadata lock”查PROCESSLIST找DDL会话KILL对应DDL或空闲会话死锁报错查SHOW ENGINE INNODB STATUS中LATEST DETECTED DEADLOCK确认死锁双方SQL批量更新大面积阻塞EXPLAIN检查索引使用情况确认是否全表扫描导致锁范围过大KILL后锁仍不解确认死锁检测参数与事务回滚状态观察几秒钟或检查回滚进度5.4 一个典型的线上锁表案例复盘某次业务部门反馈后台订单导出功能卡死十几个运营同时点击导出结果全部超时。我登录数据库后第一步SHOW FULL PROCESSLIST发现十几条UPDATE语句全都卡在“Updating”状态等待的时间从几十秒到几分钟不等。继续查INNODB_TRX锁定了一个trx_state为“LOCK WAIT”的事务它的trx_query是一条UPDATEtrx_started时间已经超过十分钟。再看sys.innodb_lock_waits阻塞者的线程ID指向一个连接那个连接的Info显示为空——典型的事务悬空某段代码里开启了事务执行了UPDATE然后可能因为调用外部接口超时或者业务逻辑异常事务一直没提交也没回滚连接就那样挂着。处理方式是先KILL阻塞者线程锁等待立刻释放十几条排队UPDATE陆续执行完成。事后让开发排查那块的代码果然是事务里嵌了一次HTTP调用服务端超时导致事务一直挂起。整改方案很简单把HTTP调用从事务里挪出来事务只包裹真正的数据库更新。之后这类问题再没出现过。这个案例想说明的核心是锁表的问题表面在数据库很多时候根子却在应用层的事务边界设计上。排查锁问题如果不延伸到业务代码很容易陷入“解了又锁、锁了又解”的死循环。最后再分享一个个人习惯在线上的锁问题发生之后我会把当时的PROCESSLIST快照、INNODB_TRX数据、死锁日志全部存下来按时间归档。积累几个月这些记录就是排查同类问题最扎实的参考素材。你甚至可以定期用脚本扫描系统库把长时间未提交的事务主动列出来赶在它引发锁风暴之前处理掉这比被动救火舒服太多了。