事件还原

10.22业务端反馈部分业务失败，从程序段检测到部分数据未能添加成功；通过RDS的慢查询，发现部分insert语句的lock持续长达800秒之久

很明显的这部分插入失败了

10.29当天，由于某测试库的原因，导致mysqldump在早晨9点55左右执行，因此线上库再次出现了update等待问题

涉及知识面

表面上看，这2次事件仅仅涉及到lock，然而实际上此次事件还涉及到latch

分析

首先是10.29的mysqldump阻塞生产库事件

该测试库在每晚的凌晨4点会执行对生产库的mysqldump，该库的log冗余天数为10天，由于数据量暴涨，该库的空间并不足以应付10天的log量，在空间爆满后，该库无论是data还是log都无法正常写入，因此mysqldump被挂起，直到第二天早晨的9点55分，研发发现数据库因为空间满了无法登陆。删除binlog之后，mysqldump开始执行，于是悲剧发生了。

mysqldump为了产生全表一致性快照，因此对RDS对应实例加了一个FTWRL锁，该锁的粒度为表级，因此对应实例下所有表均被锁住，生产库的所有DML操作均要等待这个FTWRL锁释放之后才能获取资源，从而造成锁等待，update全部挂起。

其次是10.22的insert等待

由于information_schema（后续简称IS库）内的3张与锁信息有关的表均为临时表，数据不会永久保存，所以到底是什么阻碍了10.22的那堆insert无法确定。从正常的lock层面来看，insert毫无疑问会寻求x锁，而x锁与s\x\is\ix锁均为互斥关系，所以之前无论是什么级别的行级锁都会造成insert的等待，但是...

从lock的时间来看，这个时间等待从200秒到800秒不等，而lock_wait_timeout参数设定为50，也就是说如果lock超过50秒还不能获取的话，语句就会被判定为失败，所以...

很明显这里造成800秒等待的并不是单纯的行级锁！

后续通过设置定时任务将IS库里的3张表保存下来也并没有发现行级锁之间阻塞insert的情况

通过后台可以发现大量的慢SQL，以及大量的死链。在10.23当天我甚至观测到过1000的死链（但后续查看当天最高400+，难道我眼花了？）

大量的死链会抬高CPU占用率，自然也会有latch的争用

在MySQL中lock针对表、页、行；而latch针对线程；lock存在等待、排队，latch不存在，与之相关的是spin，sleep

10.22的情况很可能就是下面我描述的这样

假设a线程需要执行一个垃圾SQL，全表扫描的那种；b线程需要执行一个insert或者update

a在b之前先行到达，于是a获取了这个latch；b随后到达，他觉得a会很快执行完，于是他开始spin（spin：自旋，空占着cpu，执行一段空代码，等待一个随机的时间；如果不spin，会被踢出去），当b执行完spin之后回来找a，发现a还在执行那个垃圾SQL，于是他继续spin，又过了一会他回来了，但是他发现这时候后面的c已经在他spin的时间获得了这个latch，于是他继续spin...在spin了无数次之后，他还是无法获得这个latch，于是他sleep，退出cpu了。

我们在show processlist中看到的大量sleep的行，就是这么来的，每一个sleep占用着一个死链；那天我看到的1000多个死链，也就是1000多个sleep，在此之前他们都不断的spin过。

所以：阻碍获得x锁的，未必是lock层面的锁，他完全也可能是被一个单纯的快照读从latch层面上阻碍了！

解决方案

对于10.29的事件，只要mysqldump不在忙时产生就没事，也就是确保其在半夜执行完成就OK；

临时方案，已经将该测试库的log冗余天数缩减到3天

后续方案，另外找一台服务器作为中间库，只单纯部署数据库，由运维部门监控该中间库

更优方案，开启金融云RDS的读写分离，xxx部门只能访问只读库

对于10.22事件

1.修改wait_timeout和interactive_timeout时间，原wait_timeout时间为86400秒，也就是1天才会清理死链，这个值设置的太过巨大，已经将其修改为300秒，目前来看死链基本控制在130以下

2.持续的慢SQL优化；最近金融云RDS的慢SQL多半是由一张特定的表查询产生的，需要对这个表进行索引的优化

-------------------------------------------------------------------------------------

整合一下IS库那3张临时表写入永久表的建表语句、存储过程以及event

--2018.10.31

金融云RDS 2018.10.22出现大面积锁等待事件

由于IS里面的3张表都是临时表，因此需要将其存到永久表中进行分析

创建ISPS库用于性能分析

创建3张表结构（不加主键）

CREATE TABLE trx (

`trx_id` varchar(18) NOT NULL DEFAULT '',

`trx_state` varchar(13) NOT NULL DEFAULT '',

`trx_started` datetime NOT NULL,

`trx_requested_lock_id` varchar(81) DEFAULT NULL,

`trx_wait_started` datetime DEFAULT NULL,

`trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_query` varchar(1024) DEFAULT NULL,

`trx_operation_state` varchar(64) DEFAULT NULL,

`trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_isolation_level` varchar(16) NOT NULL DEFAULT '',

`trx_unique_checks` int(1) NOT NULL DEFAULT '0',

`trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',

`trx_last_foreign_key_error` varchar(256) DEFAULT NULL,

`trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',

`trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',

`trx_is_read_only` int(1) NOT NULL DEFAULT '0',

`trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'

) ENGINE=MEMORY DEFAULT CHARSET=utf8;

CREATE TABLE `locks` (

`lock_id` varchar(81) NOT NULL,

`lock_trx_id` varchar(18) NOT NULL DEFAULT '',

`lock_mode` varchar(32) NOT NULL DEFAULT '',

`lock_type` varchar(32) NOT NULL DEFAULT '',

`lock_table` varchar(1024) NOT NULL DEFAULT '',

`lock_index` varchar(1024) DEFAULT NULL,

`lock_space` bigint(21) unsigned DEFAULT NULL,

`lock_page` bigint(21) unsigned DEFAULT NULL,

`lock_rec` bigint(21) unsigned DEFAULT NULL,

`lock_data` varchar(8192) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE LOCK_WAITS (

`requesting_trx_id` varchar(18) NOT NULL DEFAULT '',

`requested_lock_id` varchar(81) NOT NULL DEFAULT '',

`blocking_trx_id` varchar(18) NOT NULL DEFAULT '',

`blocking_lock_id` varchar(81) NOT NULL DEFAULT '',

`requesting_thd_id` varchar(18) NOT NULL DEFAULT '',

`blocking_thd_id` varchar(18) NOT NULL DEFAULT ''

) ENGINE=MEMORY DEFAULT CHARSET=utf8

创建存储过程

delimiter //

create procedure insert_lock_information ()

begin

insert into isps.trx select * from information_schema.innodb_trx;

insert into isps.locks select * from information_schema.innodb_locks;

insert into isps.lock_waits select * from information_schema.innodb_lock_waits;

commit;

end

//

创建event

create event if not exists insert_lock_information

on schedule every 30 second

on completion preserve

do call insert_lock_information ()

RDS锁等待超时的参数为50秒，超过这个时间，事务会被中断后重新执行

event本来计划为每秒执行1次，那样会产生相当多的数据，将其修改为30秒以观效果；如果锁问题出现较多，会将event更改为每秒执行。

该event会增加系统的IO负担，因此在完成观察之后，需要关闭该event