MySQL 当中锁的争用（中级篇）

锁是计算机协调多个进程或线程并发访问某一资源的机制！
不管是MyISAM还是InnoDB 都会出现锁争用的情况发生，在MySQL 当中的各种锁（中级篇）
这篇文章当中我们已经讲过表锁和行锁争用情况的查看方法

=======================================================================

1.表锁定

SHOW  STATUS  LIKE  'table%';

通过检查table_locks_waited 和 table_locks_immediate 状态变量来分析表锁定
说明：
Table_locks_immediate：能够立即获得表级锁的锁请求次数
Table_locks_waited：不能立即获取表级锁而需要等待的锁请求次数
分析：
如果table_locks_waited值较高，且存在性能问题，则说明存在着较严重的表级锁争用情况。这时，需要对应用做进一步的检查，来确定问题所在，应首先优化查询，然后拆分表或复制表，如果表锁的争抢很严重的话则需要考虑更换引擎为InnoDB，另外开启sql的慢查询看具体哪些sql查询较慢针对性的做sql优化。

================================================================================

2.行级锁

SHOW STATUS LIKE ‘Innodb_row_lock%’;

说明：
Innodb_row_lock_current_waits：当前锁等待的数量
Innodb_row_lock_time：自系统启动到现在，锁定的总时间，单位：毫秒 ms。
Innodb_row_lock_time_avg：平均锁定的时间，单位：毫秒 ms。
Innodb_row_lock_time_max：最大锁定时间，单位：毫秒 ms。
Innodb_row_lock_waits：自系统启动到现在，锁等待次数，即锁定的总次数。

分析：
针对如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，说明可能存在锁争用的情况，针对 Innodb 类型的表，可以通过设置InnoDB Monitors来进一步观察发生锁争用的表、数据行等，并分析锁争用的原因，如下：
注：可通过语句SHOW CREATE TABLE table_name;查看表table_name使用的引擎(查询输出结果中找到ENGINE=xxxx，这里xxxx即为使用的引擎);
先设置InnoDB Monitor

CREATE  TABLE  innodb_monitor(a  INT)  ENGINE=INNODB;

查看

SHOW ENGINE INNODB STATUS;

说明：
1.输出结果包含了详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等等。打开监视器以后，默认情况下每 15 秒会向日志中记录监控的内容，如果长时间打开会导致.err 文件变得非常的巨大，所以我们在确认问题原因之后，要记得删除监控表(DROP TABLE innodb_monitor;)以关闭监视器。
2.输出结果为基于一段时间的数据采样，得出的每秒平均值，这里的时间取自系统启动到当前时间的时间间隔或者上次输出到当前时间的时间间隔
3.找到TRANSACTIONS部分的内容，可以查看事务死锁争用的相关情况
更多关于MySQL中show engine innodb status的详解请参考下方链接地址：
https://www.cnblogs.com/olinux/p/5497176.h...

==================================================================

3.查看当前或者当前锁定或者当前等待的事务的命令：

查看当前的事务：

SELECT` `* ``FROM` `information_schema.innodb_trx \G

查看当前锁定的事务：

SELECT` `* ``FROM` `INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务：

SELECT` `* ``FROM` `INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

通过上边的三句代码 你可以知道现在正在执行的事务 锁定了的事务 以及 等待的事务
如果锁争用情况激烈 数值较大 正常业务当中也很难通过查看当前事务准确定位到锁争用严重的表

本作品采用《CC 协议》，转载必须注明作者和本文链接