概述
本文主要介绍如何查看MySQL数据库的锁信息。
详细信息
阿里云提醒您：
如果您对实例或数据有修改、变更等风险操作，务必注意实例的容灾、容错能力，确保数据安全。
如果您对实例（包括但不限于ECS、RDS）等进行配置与数据修改，建议提前创建快照或开启RDS日志备份等功能。
如果您在阿里云平台授权或者提交过登录账号、密码等安全信息，建议您及时修改。
以下是各个版本的MySQL引擎查询锁信息的SQL语句：
5.6/5.7版本
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, left(r.trx_query, 20) waiting_query, concat( concat(lw.lock_type, ' '), lw.lock_mode ) waiting_for_lock, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, left(b.trx_query, 20) blocking_query, concat( concat(lb.lock_type, ' '), lb.lock_mode ) blocking_lock 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 INNER JOIN information_schema.innodb_locks lw ON lw.lock_trx_id = r.trx_id INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = b.trx_id;
返回的字段信息如下。
说明：
waiting_trx_id：等待事务的ID。
waiting_thread：MySQL中的线程ID，即show processlist显示的结果。
waiting_query：等待锁的语句。
blocking_trx_id：正在阻止等待锁的事务的ID。
8.0版本
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 PERFORMANCE_SCHEMA.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;
返回的字段信息如下。
8.0版本进阶查询锁信息
使用此方法查出的数据较多，在行锁很多的情况下，返回的结果集会很大，请谨慎使用。
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, LEFT(r.trx_query, 20) AS waiting_query , concat(concat(lw.lock_type, ' '), lw.lock_mode) AS waiting_for_lock , b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, LEFT(b.trx_query, 20) AS blocking_query , concat(concat(lb.lock_type, ' '), lb.lock_mode) AS blocking_lockFROM performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID INNER JOIN performance_schema.data_locks lw ON lw.ENGINE_TRANSACTION_ID = r.trx_id INNER JOIN performance_schema.data_locks lb ON lb.ENGINE_TRANSACTION_ID = b.trx_id;
返回的字段信息如下。
相关文档
MySQL 8.0版本查看锁定信息
适用于
云数据库RDS MySQL版