本文共 1149 字,大约阅读时间需要 3 分钟。
通过变量Innodb_row_lock分析系统上的行锁争夺情况。
mysql> show status like 'Innodb_row_lock%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time |152333180|| Innodb_row_lock_time_avg |12279 || Innodb_row_lock_time_max |51616 || Innodb_row_lock_waits |12405 |+-------------------------------+-------+5 rows in set (0.00 sec)
解释如下:
Innodb_row_lock_current_waits:当前等待锁的数量 Innodb_row_lock_time:系统启动到现在、锁定的总时间长度 Innodb_row_lock_time_avg:每次平均锁定的时间 Innodb_row_lock_time_max:最长一次锁定时间 Innodb_row_lock_waits:系统启动到现在、总共锁定次数通过查询infomation_schma数据库中的表了解锁等待情况
SELECT * FROM innodb_locks;SELECT * FROM INNODB_LOCK_WAITS;
如果发现锁争用比较严重,如Innodb_row_lock_waits、Innodb_row_lock_time_avg的值比较高,可以通过infomation_schema数据库中相关的表来查看锁情况,或者通过InnoDB monitors观察锁冲突情况。
mysql> use information_schemaDatabase changedmysql> select * from innodb_locks;Empty set (0.00 sec)mysql>
转载地址:http://ohqab.baihongyu.com/