MySQL 锁等待分析

前言：好久没有处理MySQL锁等待，不免感觉少了点什么，索性就看了下核心实例的innodb 锁等待的次数趋势图，我勒个去大有万马奔腾之势。

分析：

1：捕获innodb锁等待详细信息

     MySQL目前只存储innodb锁等待实时信息，不存储历史信息。感觉这熊孩纸甚是孤傲。嘿嘿，木事阿拉跟他干到底。

     大招：定时5秒一次捕获等待事件插入表中

     查看锁等待信息： 

     information_schema库中 INNODB_LOCKS,INNODB_LOCK_WAITS,INNODB_TRX  3表关联即可

     sys库中视图  innodb_lock_waits 引用以上3张表

   定时任务

set global event_scheduler=on;
use test;
create table innodb_lock_waits as sys.innodb_lock_waits;
CREATE EVENT if not exists get_lock_waits 
ON SCHEDULE every 5 second ON COMPLETION  PRESERVE  ENABLE comment '捕获锁等待信息' 
DO 
insert into get_lock_waits
select * from   sys.innodb_lock_waits;

2：优先处理等待次数高的语句类型

     定时任务跑了一段时间后，执行以下sql 获取等待次数最多的top 5 sql语句类型

select left(waiting_query,50) jiequ,waiting_query,blocking_query,count(*) 
from test.innodb_lock_waits group by jiequ  order by count(*) desc limit 5 \G

3：加锁分析

     捕获到2条sql类型 等待的次数最多如下：

SELECT * FROM `a` WHERE `origin_id` = 1134677 AND `warehouse` = 1 AND `status` = 1  FOR UPDATE LIMIT 1;
SELECT * FROM `b` WHERE `purchase_no` = 1806240793 LIMIT 1   FOR UPDATE;

分析：分析加锁的前提条件

1. 事务隔离级别 ： rc 隔离级别是 记录锁 保证不能读取到未提交的事务；rr 隔离级别是 记录锁+间隙锁 保证不会出现幻读
   
2. 执行计划：全表扫描会在表中的所有记录加锁，索引查找会在二级索引，主键索引上加锁。

    数据库隔离级别是:rc

    第1个sql 加锁分析：

    执行计划是走status 索引扫描9321行即会对 9321行status二级索引记录加排他锁，还会对对应的主键

    索引9321行记录加行级排他锁，最后只对满足条件的记录加锁；

    第2个sql 加索分析：

    执行计划是全表扫描301461 行，即会对聚集索引id上所有的行加行级排他锁，最后只对满足条件的记录加锁。

    

优化意见

2个sql 需要的锁资源都特别多，在高并发实例中很容易被其他session 阻塞或者阻塞其他的session 导致大量的锁等待，降低数据库的吞吐量

第1个sql 优化意见：给选择性很高的字段origin_id添加索引，由于origin_id 字段类型是字符型，为了避免隐式转换，条件改为 `origin_id` = '1134677' 。

第2个sql优化意见：给选择性很高的字段purchase_no添加索引，由于purchase_no字段类型是字符型，为了避免隐式转换，条件改为 `purchase_no` = '1806240793'

4：总结

1.  一定要确保事务中的加锁量尽可能少，即保证sql 通过索引过滤并且索引的选择性要高。从而避免大量的阻塞，要不然会降低数据库相应时间以及吞吐量，甚至造成雪崩效率导致数据库不可用，如超过最大连接，频繁的 锁等待超时，死锁等。
2. 加锁类慢sql,不仅本身执行慢，加锁过程也会被其他sql阻塞从而执行时间会更长，还有导致阻塞其他并发的事务。即害人害己。
3. 慎用select .......  for update 除非特殊场景需要。
   

参考文献：http://hedengcheng.com/?p=771