MySQL为什么经常卡死？可能是锁表在作怪！快速排查方法

欢迎来到8455线路检测中心技术小课堂，每天分享一个技术小知识。

  MySQL 查询锁表完全指南

     一、什么是锁表？

     锁表就像厕所门锁：一个人在使用时，其他人需要等待，避免"数据冲突"。

     二、锁的类型速查

     | 锁类型   | 级别   | 影响范围    | 使用场景  |

     | 表级锁   | 整个表  | 影响所有行  | MyISAM 引擎| 

     | 行级锁   | 单行   | 只影响当前行 | InnoDB 引擎| 

      | 元数据锁  | 表结构  | 影响DDL操作 | 所有引擎  |

     三、查看锁表情况

查看当前所有锁

     -- 查看正在执行的查询和锁信息 

     SHOW PROCESSLIST; 

     -- 查看详细的InnoDB锁信息 

     SELECT * FROM information_schema.INNODB_LOCKS; 

     SELECT * FROM information_schema.INNODB_LOCK_WAITS; 

     -- 查看当前会话信息 

     SELECT * FROM performance_schema.data_locks; 

     SELECT * FROM performance_schema.data_lock_waits;

查看阻塞的查询

     -- 查找正在等待锁的查询 

     SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

     四、常见锁表场景

     场景1：长时间未提交的事务

     -- 会话1：开启事务但不提交 

     START TRANSACTION; UPDATE users SET balance = balance - 100 WHERE id = 1; 

     -- 不执行 COMMIT 或 ROLLBACK 

     -- 会话2：会被阻塞 

     UPDATE users SET balance = balance + 100 WHERE id = 1;

     场景2：大表DDL操作

      -- 添加索引（会锁表） 

     ALTER TABLE large_table ADD INDEX idx_name (name); 

     -- 在此期间，所有写操作都会被阻塞

     场景3：不合理的查询

     -- 没有索引的更新（可能升级为表锁） 

     UPDATE products SET status = 'inactive' WHERE category = 'electronics';

     五、锁表排查实战

     步骤1：快速定位问题

     -- 查看当前活跃事务和锁 

     SELECT p.ID as process_id, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME as execution_time, p.STATE, p.INFO as query_text, l.LOCK_TYPE, l.LOCK_MODE, l.LOCK_TABLE FROM information_schema.PROCESSLIST p LEFT JOIN information_schema.INNODB_LOCKS l ON p.ID = l.LOCK_TRX_ID WHERE p.COMMAND != 'Sleep' ORDER BY p.TIME DESC;

     步骤2：分析锁等待链

     -- 查看锁等待关系 

     SELECT r.trx_id AS waiting_trx_id, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_query AS blocking_query, TIMEDIFF(NOW(), r.trx_started) AS wait_duration FROM information_schema.INNODB_LOCK_WAITS w JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

     六、解决锁表问题

     方法1：终止阻塞进程

     -- 查找需要终止的进程ID 

     SHOW PROCESSLIST; 

     -- 终止特定进程 

     KILL [process_id]; 

     -- 示例：终止ID为123的进程 

     KILL 123;

     方法2：优化事务

     -- ❌ 错误做法：长时间事务 

     START TRANSACTION; UPDATE large_table SET ...; -- 耗时操作 -- ... 其他业务逻辑 COMMIT; 

     -- ✅ 正确做法：短事务 

     UPDATE large_table SET ...; -- 自动提交

     方法3：使用在线DDL

     -- ❌ 传统DDL（锁表） 

     ALTER TABLE users ADD INDEX idx_email (email); 

     -- ✅ 在线DDL（MySQL 5.6+） 

     ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;

     七、预防锁表的最佳实践

事务设计原则

     -- ✅ 保持事务简短 

     START TRANSACTION; -- 只包含必要的数据库操作 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 立即提交 

     -- ❌ 避免在事务中包含业务逻辑 

     START TRANSACTION; UPDATE accounts ...; -- 这里执行复杂的业务计算... -- 调用外部API... COMMIT;

索引优化

     -- 为WHERE条件字段添加索引 

     CREATE INDEX idx_user_status ON users(status); CREATE INDEX idx_order_date ON orders(created_date); -- 避免全表扫描导致的锁升级

查询优化

     -- ❌ 糟糕的查询（可能锁全表） 

     UPDATE products SET price = price * 0.9 WHERE category LIKE '%electronic%'; 

     -- ✅ 优化的查询 

     UPDATE products SET price = price * 0.9 WHERE category_id IN (SELECT id FROM categories WHERE name LIKE '%electronic%');

     八、监控和告警

     创建锁监控视图

     CREATE VIEW lock_monitor AS SELECT NOW() as check_time, p.ID as process_id, p.USER, p.DB, p.TIME as execution_seconds, p.STATE, LEFT(p.INFO, 100) as query_preview, l.LOCK_TABLE, l.LOCK_TYPE, l.LOCK_MODE FROM information_schema.PROCESSLIST p LEFT JOIN information_schema.INNODB_LOCKS l ON p.ID = l.LOCK_TRX_ID WHERE p.COMMAND != 'Sleep' AND p.TIME > 60 -- 执行超过60秒 ORDER BY p.TIME DESC;

     自动化监控脚本

     !/bin/bash lock_monitor.sh 

     检查长时间运行的查询 

     mysql -e " SELECT p.ID as process_id, p.USER, p.TIME as execution_seconds, p.INFO as query_text FROM information_schema.PROCESSLIST p WHERE p.COMMAND != 'Sleep' AND p.TIME > 300 -- 5分钟以上 ORDER BY p.TIME DESC;" > long_queries.txt 如果有长时间查询，发送告警 if [ -s long_queries.txt ]; then mail -s "MySQL长时间运行查询告警" admin@company.com < long_queries.txt fi

     九、紧急处理流程

     锁表紧急处理清单：

识别问题

     SHOW PROCESSLIST; SELECT * FROM information_schema.INNODB_LOCKS;

定位阻塞源

     -- 查找阻塞其他查询的事务 SELECT * FROM information_schema.INNODB_LOCK_WAITS;

选择性终止

     -- 先终止阻塞者，而不是被阻塞者 

KILL [blocking_process_id];

验证解决

     -- 确认锁已释放 

SHOW PROCESSLIST;

     十、性能优化配置

     my.cnf 优化设置

     [mysqld] InnoDB设置 

innodb_lock_wait_timeout = 50 锁等待超时（秒） 

innodb_rollback_on_timeout = 1 超时自动回滚 

transaction_isolation = READ-COMMITTED 事务隔离级别 连接设置

max_connections = 200 最大连接数 

wait_timeout = 600 非交互连接超时 

interactive_timeout = 600 交互连接超时

     实用命令速查

   | 场景 | 命令 | 说明 |

   |快速查看|SHOW PROCESSLIST;|查看当前连接和查询| 

   |详细锁信息|SELECT * FROM information_schema.INNODB_LOCKS;|查看InnoDB锁| 

   |锁等待|SELECT * FROM information_schema.INNODB_LOCK_WAITS;|查看锁等待关系| 

   |终止进程|KILL [id];|终止特定连接| 

   |事务信息|SELECT * FROM information_schema.INNODB_TRX;|查看当前事务|

     总结

     记住关键点：

预防优于治疗：合理设计事务和索引

快速定位：使用 SHOW PROCESSLIST和 INNODB_LOCKS

精准解决：终止阻塞进程，而不是被阻塞进程

持续监控：建立锁表告警机制

     紧急情况处理：

     1. 查看问题 mysql -e "SHOW PROCESSLIST;" 

     2. 定位阻塞源 mysql -e "SELECT * FROM information_schema.INNODB_LOCK_WAITS;" 

     3. 终止问题进程 mysql -e "KILL [problem_process_id];"

8455线路检测中心官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，8455线路检测中心整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，8455线路检测中心期待与你一起探索。