MySQL执行特定UPDATE查询触发System Lock状态问题排查

问题本质 #

无正在执行的DML/DDL时仍出现System Lock状态，通常两类原因：一是存在残留元数据锁(MDL)、存储引擎层锁未释放；二是SQL本身执行效率极差，全表扫描+排序过程中长时间持有锁，在processlist中被标记为锁状态。

第一步：先做锁残留排查 #

• 先确认表存储引擎：执行SHOW CREATE TABLE jobs_feed;，如果是MyISAM引擎，其表级锁机制是无并发下也出现锁等待的常见原因，直接将引擎改为InnoDB即可。
• 排查残留MDL锁：执行以下SQL查看是否有未释放的元数据锁：

  SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, OWNER_THREAD_ID 
  FROM performance_schema.metadata_locks 
  WHERE OBJECT_NAME='jobs_feed';
  

  如果存在非当前UPDATE会话持有的锁，通过KILL 对应会话ID;杀掉残留会话即可，残留会话通常来自未提交的旧事务、未关闭的预编译请求、中断的主从复制线程。

第二步：SQL本身性能优化 #

你提供的UPDATE存在大量无法命中索引的逻辑，默认会走全表扫描+文件排序，大表场景下执行时间可达几十分钟到数小时，是问题的核心诱因，优化点如下：

1. 修正错误的正则表达式
   你写的正则"[+|^||||*|<|>|||^|%|!|–]|[ ]{2,}"存在语法问题：字符类[]内部不需要用|做或逻辑，多余的|会被当成待匹配的普通字符，且重复写了多个^，既会导致匹配结果不符合预期，也会拖慢正则执行效率，需要按实际业务匹配规则修正正则写法。
2. 建立适配的联合索引
   把所有等值判断、排序字段按最左匹配原则建联合索引，直接消除全表扫描和文件排序：

   CREATE INDEX idx_jobsfeed_gfj_update ON jobs_feed(
       employerId, 
       locExpand, 
       posted_to_gfj, 
       reposted_to_gfj, 
       is_deleted, 
       deleted_from_gfj, 
       cpa DESC
   );
   

   建完后执行EXPLAIN看执行计划，确认type不是ALL（全表扫描），key命中上述索引，扫描行数降到万级以内。
3. 替换无法走索引的计算逻辑
   条件中ROUND ((LENGTH(location)- LENGTH( REPLACE ( location, ",", ""))) / LENGTH(","))=2是每行都要计算的函数，无法命中索引，建议给表新增location_comma_cnt TINYINT字段，插入/更新数据时提前计算好location里的逗号数量存入该字段，给字段加索引，更新时直接用location_comma_cnt=2做判断即可。正则判断、!=类判断放在索引条件之后做过滤，不要放在最外层扫全表。
4. 拆分大事务
   不要一次性LIMIT 14390更新一万多行，单次更新持锁时间长、产生的日志量大，改成每次LIMIT 200-500行，循环执行直到没有符合条件的行，大幅缩短单次事务的持锁时间。

验证方式 #

优化后执行UPDATE，再通过show processlist查看状态，正常情况下不会长时间停留在System Lock，执行时间会从小时级降到秒级。

内容的提问来源于stack exchange，提问作者Chowdary