监控mysql锁定状态_MYSQL 表锁情况查看

亦凉 2023-01-09 14:11 206阅读 0赞

查看锁表情况

Table\_locks\_immediate 指的是能够立即获得表级锁的次数

Table\_locks\_waited 指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况

查看正在被锁定的的表

show OPEN TABLES where In\_use > 0;

mysql> show OPEN TABLES where In\_use > 0; +————–+—————+——–+————-+ | Database | Table | In\_use | Name\_locked | +————–+—————+——–+————-+ | music | class\_record | 1 | 0 | | vipswoole | chat\_message | 3 | 0 | | music | user\_account | 1 | 0 | | music | sales\_channel | 1 | 0 | | music | class\_room | 5 | 0 | | music | user | 1 | 0 | | music\_school | user | 1 | 0 | +————–+—————+——–+————-+ 7 rows in set (0.00 sec)

mysql>

如果查看到锁争用情况严重，可以再查看当前执行的SQL ：

mysql>show processlist (mysqladmin -uroot -p -P 3306 processlist)

mysqladmin命令有一个debug参数，可以分析当前MySQL服务的状态信息，同时也可以用来帮助我们定位当前锁的详细情况，这里我们通过该命令分析一下当前MySQL服务的详细状态，执行mysqladmin命令如下：

\[root@phpmysql02 data\]\# mysqladmin -ujss -p -S /data/3306/mysql.sock debug

Enter password:

debug会将状态信息生成到mysql的错误文件，一般锁的信息都会保存在最后几行，这里我们在操作系统层error log最后几行：

\[root@phpmysql02 data\]\# tail -10 phpmysql02.err

Thread database.table\_name Locked/Waiting Lock\_type 2 hdpic.t\_wiki\_zutu Waiting – write Highest priority write lock 123890 hdpic.t\_wiki\_zutu\_category Locked – read Low priority read lock 123890 hdpic.t\_wiki\_zutu\_photo Locked – read Low priority read lock 123890 hdpic.t\_wiki\_zutu Locked – read Low priority read lock 124906 hdpic.t\_wiki\_zutu Waiting – read Low priority read lock

从上述信息可以看出，123890持有的读锁阻塞了2的写入和124906的读操作，这个状态符合我们的推论，接下来处理就比较单纯了，如果现状不可接受，不能继续等待，将123890杀掉，释放资源即可：

mysql> kill 123890; Query OK, 0 rows affected (0.00 sec)

再次执行show processlist查看：

使用系统表进行锁查询：

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

涉及的3张表说明：

information\_shcema下的三张表(通过这三张表可以更新监控当前事物并且分析存在的锁问题)

innodb\_trx ( 打印innodb内核中的当前活跃(ACTIVE)事务)

innodb\_locks ( 打印当前状态产生的innodb锁 仅在有锁等待时打印)

innodb\_lock\_waits (打印当前状态产生的innodb锁等待 仅在有锁等待时打印)

1) innodb\_trx表结构说明 (摘取最能说明问题的8个字段)

字段名 说明 trx\_id innodb 存储引擎内部唯一的事物ID trx\_state 当前事物状态(running和lock wait两种状态) trx\_started 事物的开始时间 trx\_requested\_lock\_id 等待事物的锁ID，如trx\_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx\_state不是Lock wait 则该值为NULL trx\_wait\_started 事物等待的开始时间 trx\_weight 事物的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚 trx\_mysql\_thread\_id mysql中的线程id, 即show processlist显示的结果 trx\_query 事物运行的SQL语句

2)innodb\_locks表结构说明

字段名 说明 lock\_id 锁的ID lock\_trx\_id 事物的ID lock\_mode 锁的模式(S锁与X锁两种模式) lock\_type 锁的类型 表锁还是行锁(RECORD) lock\_table 要加锁的表 lock\_index 锁住的索引 lock\_space 锁住对象的space id lock\_page 事物锁定页的数量，若是表锁则该值为NULL lock\_rec 事物锁定行的数量，若是表锁则该值为NULL lock\_data 事物锁定记录主键值，若是表锁则该值为NULL(此选项不可信)

3)innodb\_lock\_waits表结构说明

字段名 说明 requesting\_trx\_id 申请锁资源的事物ID requested\_lock\_id 申请的锁的ID blocking\_trx\_id 阻塞其他事物的事物ID blocking\_lock\_id 阻塞其他锁的锁ID

可以根据这三张表进行联合查询，得到更直观更清晰的结果，可以参考如下SQL(可根据自己的分析习惯适进行调整)

原文出处：waitig -> https://www.waitig.com/mysql-%E8%A1%A8%E9%94%81%E6%83%85%E5%86%B5%E6%9F%A5%E7%9C%8B.html