锁表就像厕所门锁：一个人在使用时，其他人需要等待，避免"数据冲突"。
| 锁类型 | 级别 | 影响范围 | 使用场景 | |--||-|-| | 表级锁 | 整个表 | 影响所有行 | 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：开启事务但不提交 START TRANSACTION; UPDATE users SET balance = balance - 100 WHERE id = 1; -- 不执行 COMMIT 或 ROLLBACK -- 会话2：会被阻塞 UPDATE users SET balance = balance + 100 WHERE id = 1;
-- 添加索引（会锁表） ALTER TABLE large_table ADD INDEX idx_name (name); -- 在此期间，所有写操作都会被阻塞
-- 没有索引的更新（可能升级为表锁） UPDATE products SET status = 'inactive' WHERE category = 'electronics';
-- 查看当前活跃事务和锁 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;
-- 查看锁等待关系 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;
-- 查找需要终止的进程ID SHOW PROCESSLIST; -- 终止特定进程 KILL [process_id]; -- 示例：终止ID为123的进程 KILL 123;
-- ❌ 错误做法：长时间事务 START TRANSACTION; UPDATE large_table SET ...; -- 耗时操作 -- ... 其他业务逻辑 COMMIT; -- ✅ 正确做法：短事务 UPDATE large_table SET ...; -- 自动提交
-- ❌ 传统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;
[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];"
通过这套方法，你可以快速定位和解决MySQL锁表问题，保证数据库的稳定运行！ 另外搭配便捷的80kmMYSQL备份工具，可定时备份、异地备份，MYSQL导出导入。可本地连接LINUX里的MYSQL，简单便捷。可以大大地提高工作效率喔。
