mysql5.7如何查询锁的情况

<think>好的，用户现在问的是MySQL5.7如何查询锁的情况。我需要详细回答这个问题，包括相关的SQL语句和解释，还要给出代码示例。之前用户已经问过关于InnoDB行锁的问题，现在可能是在实际使用中遇到了锁的问题，需要排查。首先，我需要回忆一下MySQL5.7中查看锁的方法。记得在5.7版本中，常用的系统表是information_schema下的INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS，不过可能在8.0之后这些表被performance_schema替代了，但用户问的是5.7，所以应该用这些表。另外，还可以使用SHOW ENGINEINNODBSTATUS命令来查看锁信息。 接下来，我需要分步骤说明如何查询锁的情况。可能需要包括查询当前事务、锁的详细信息、锁等待情况，以及如何分析死锁。同时，用户可能需要具体的SQL示例，所以需要写出具体的查询语句，并解释每个字段的含义。 此外，用户可能想知道如何监控锁等待和死锁，所以需要包括如何设置innodb_print_all_deadlocks参数，以及如何分析死锁日志。还要注意权限问题，因为某些系统表可能需要管理员权限才能访问。还要考虑用户的实际应用场景，比如他们可能在生产环境中遇到锁等待或死锁，需要快速定位问题。因此，提供的查询需要简洁有效，并且解释清楚每个步骤的作用。需要确保提供的代码示例正确，并且针对MySQL5.7版本。例如，在5.7中，INNODB_LOCKS和INNODB_LOCK_WAITS表是存在的，而在8.0中这些表被移除了，所以必须确认这一点。可能需要查阅文档确认，但根据记忆，5.7确实有这些表。 另外，用户可能想知道如何解读结果，所以需要解释每个查询返回的字段，比如lock_id、lock_mode、lock_type等，以及如何根据这些信息判断锁的类型和持有者。最后，要提出相关问题，比如如何解决死锁、不同锁模式的区别，如何监控锁等待时间等，这些相关问题需要和当前问题紧密相关，帮助用户深入理解。</think>在MySQL 5.7中查看锁状态主要通过以下系统表和执行引擎状态命令实现，以下是完整的查询方案： --- ### 一、核心查询方法 #### 1. 查询当前所有事务状态 ```sql SELECT * FROM information_schema.INNODB_TRX; ``` **关键字段说明**： - `trx_id`：事务ID - `trx_state`：事务状态（RUNNING/LOCK WAIT） - `trx_started`：事务开始时间 - `trx_query`：正在执行的SQL语句 - `trx_lock_structs`：持有的锁数量 #### 2. 查看详细锁信息 ```sql SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, l.lock_table, l.lock_index, l.lock_mode, l.lock_type FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_LOCKS l ON w.requesting_lock_id = l.lock_id INNER JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id INNER JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id; ``` #### 3. 查看锁等待关系 ```sql SELECT waiting_pid, blocking_pid, waiting_query, blocking_query FROM sys.innodb_lock_waits; -- 需要先安装sys库 ``` #### 4. 引擎状态查看（包含最新死锁信息） ```sql SHOW ENGINE INNODB STATUS\G ``` 在输出结果的 `LATEST DETECTED DEADLOCK` 部分查看死锁详情 --- ### 二、锁分析案例 #### 场景：分析锁等待问题 ```sql -- 步骤1：找到被阻塞的线程 SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT'; -- 步骤2：查看具体的锁争用关系 SELECT r.trx_id waiting_trx, b.trx_id blocking_trx, l.lock_table, l.lock_index, l.lock_mode FROM information_schema.INNODB_LOCK_WAITS w JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id JOIN information_schema.INNODB_LOCKS l ON w.requesting_lock_id = l.lock_id; -- 步骤3：终止阻塞线程 -- 根据步骤2查到的blocking_trx_id，找到对应的线程ID KILL [thread_id]; ``` --- ### 三、死锁日志分析示例 ```log LATEST DETECTED DEADLOCK ------------------------ 2024-01-01 12:00:00 0x7f8e3c0d6700 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1001, OS thread handle 123456, query id 67890 localhost root UPDATE table1 SET col1=1 WHERE id=5 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 1234 page no 5 n bits 72 index PRIMARY of table `test`.`table1` trx id 12345 lock_mode X locks rec but not gap *** (2) TRANSACTION: TRANSACTION 67890, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 1002, OS thread handle 654321, query id 12345 localhost root UPDATE table1 SET col1=2 WHERE id=6 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1234 page no 5 n bits 72 index PRIMARY of table `test`.`table1` trx id 67890 lock_mode X locks rec but not gap ``` --- ### 四、监控配置建议 1. 开启死锁日志记录 ```ini [mysqld] innodb_print_all_deadlocks = 1 -- 将所有死锁信息写入错误日志 ``` 2. 定期检查锁等待 ```sql -- 查看超过30秒的长事务 SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30; ``` ---