【MySQL】锁——查看当前数据库锁请求的三种方法

来源：互联网发布：电脑群发短信的软件编辑：程序博客网时间：2024/06/02 05:32

MySQL提供了查看当前数据库锁请求的三种方法：

1. show full processlist命令
观察state和info列

2. show engine innodb status\G 命令
查看 TRANSACTIONS 部分和 LATEST DETECTED DEADLOCK 两个部分

3. information_shcema下的三张表（通过这三张表可以更新监控当前事物并且分析存在的锁问题）
—— innodb_trx （

打印innodb内核中的当前活跃（ACTIVE）事务）

—— innodb_locks

（打印当前状态产生的innodb锁仅在有锁等待时打印）

—— innodb_lock_waits

（打印当前状态产生的innodb锁等待仅在有锁等待时打印）

1) innodb_trx表结构说明（摘取最能说明问题的8个字段）

字段名说明trx_idinnodb存储引擎内部唯一的事物IDtrx_state当前事物状态（running和lock wait两种状态）trx_started 事物的开始时间trx_requested_lock_id等待事物的锁ID，如trx_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx_state不是Lock wait 则该值为NULLtrx_wait_started事物等待的开始时间trx_weight事物的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚trx_mysql_thread_idmysql中的线程id, 即show processlist显示的结果trx_query 事物运行的SQL语句

2）innodb_locks表结构说明

字段名说明lock_id 锁的IDlock_trx_id事物的IDlock_mode锁的模式（S锁与X锁两种模式）lock_type锁的类型表锁还是行锁（RECORD）lock_table要加锁的表lock_index锁住的索引lock_space锁住对象的space idlock_page 事物锁定页的数量，若是表锁则该值为NULLlock_rec事物锁定行的数量，若是表锁则该值为NULLlock_data事物锁定记录主键值，若是表锁则该值为NULL（此选项不可信）

3）innodb_lock_waits表结构说明

字段名说明requesting_trx_id申请锁资源的事物IDrequested_lock_id申请的锁的IDblocking_trx_id阻塞其他事物的事物IDblocking_lock_id阻塞其他锁的锁ID

可以根据这三张表进行联合查询，得到更直观更清晰的结果，可以参考如下SQL（可根据自己的分析习惯适进行调整）
select
r.trx_isolation_level,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_trx_thread,
r.trx_state waiting_trx_state,
lr.lock_mode waiting_trx_lock_mode,
lr.lock_type waiting_trx_lock_type,
lr.lock_table waiting_trx_lock_table,
lr.lock_index waiting_trx_lock_index,
r.trx_query waiting_trx_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_trx_thread,
b.trx_state blocking_trx_state,
lb.lock_mode blocking_trx_lock_mode,
lb.lock_type blocking_trx_lock_type,
lb.lock_table blocking_trx_lock_table,
lb.lock_index blocking_trx_lock_index,
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
inner join   information_schema.innodb_locks lb
on lb.lock_trx_id=w.blocking_trx_id
inner join   information_schema.innodb_locks lr
on lr.lock_trx_id=w.requesting_trx_id\G;
*************************** 1. row ***************************
    trx_isolation_level: REPEATABLE READ
         waiting_trx_id: 2900247
     waiting_trx_thread: 1070
      waiting_trx_state: LOCK WAIT
waiting_trx_lock_mode: S
waiting_trx_lock_type: RECORD
waiting_trx_lock_table: `jiang_test`.`test`
waiting_trx_lock_index: PRIMARY
      waiting_trx_query: select * from test where id=3 lock in share mode
        blocking_trx_id: 2900241
    blocking_trx_thread: 1137
     blocking_trx_state: RUNNING
blocking_trx_lock_mode: X
blocking_trx_lock_type: RECORD
blocking_trx_lock_table: `jiang_test`.`test`
blocking_trx_lock_index: PRIMARY
         blocking_query: NULL
1 row in set (0.01 sec)

阅读全文

0 0