背景
我们有一些活动积分的DB存储，有一些增删改查的需求，在服务压测的过程中发现写QPS超过2w的时候就会出现超时和失败。
耗时明显升高，并且伴随大量失败。
问题分析
我们首先利用调用链分析超时的请求
这里明显看到是这一条insert语句耗时比较严重。
insert into encourage_point_user_balance_? (user_id, point_id, balance0, balance1, balance2, create_time, update_time) values(?, ?, ?, ?, ?, ?, ?)
on duplicate key update balance0 = balance0 + ?, balance1 = balance1 + ?, balance2 = balance2 + ?, update_time = ?
这里我们共使用了分库分表，而且并没有很复杂的数据操作场景，仅仅一些add和update的操作， 通常单库qps几千是没有问题的，那么问题出现在哪里呢？
我们找到压测的数据库监控：
结合Lock Waits 的数量，那么问题的原因大致比较明显了：数据库锁耗时较长，大量的锁等待导致查询超时
我们再回头快来看这条语句
insert into encourage_point_user_balance_? (user_id, point_id, balance0, balance1, balance2, create_time, update_time) values(?, ?, ?, ?, ?, ?, ?) on duplicate key update balance0 = balance0 + ?, balance1 = balance1 + ?, balance2 = balance2 + ?, update_time = ?
我们的分析目标确定为sql语句 insert xx on duplicate key update xx
INSERT ON DUPLICATE KEY UPDAE
官方对于这条语句的解释：
这里讲的比较简单含糊，意识大概是（百度翻译）：
“INSERT ... ON DUPLICATE KEY UPDATE与简单INSERT的不同之处在于，当发生重复键错误时，将排他锁而不是共享锁放在主键行上。对重复的主键值采用排它锁锁定索引记录行。重复的唯一键值则使用next-key lock。”
总之我们得到两个信息
（1）【INSERT ... ON DUPLICATE KEY UPDATE】 与简单的insert和update都不一样 （废话@@）
（2）【INSERT ... ON DUPLICATE KEY UPDATE】 在主健重复的情况下使用了 next-key lock
next-key lock
那什么是next-key lock呢
MySQL InnoDB支持三种行锁定方式：
l 行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。
l 间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
l Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。
默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。
当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
假设我们有一张表：
CREATE TABLE `test` (
`id` int(11) primary key auto_increment,
`xid` int, KEY `xid` (`xid`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test(xid) values (1), (3), (5), (8), (11);
注意，这里xid上是有索引的，因为该算法总是会去锁住索引记录。
现在，该索引可能被锁住的范围如下：
(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)
根据下面的方式开启事务执行SQL：
Session A执行后会锁住的范围：(5, 8], (8, 11]
除了锁住8所在的范围，还会锁住下一个范围，所谓Next-Key。
这样，Session B执行到第六步会阻塞，跳过第六步不执行，第七步也会阻塞，但是并不阻塞第八步，第九步也不阻塞。
该SQL语句锁定的范围是（5,8]，下个键值范围是（8,11]，所以插入5~11之间的值的时候都会被锁定，要求等待。即：插入5，6，7，8，9，10 会被锁住。插入非这个范围内的值都正常。
总结一下
可重复读级别不能解决幻读问题，MySQL的RR隔离级别通过结合MVCC+Next-key Locks解决幻读问题Next-key Locks不仅会锁住行自身，同时也会锁住前后的范围。
实验验证
我们注意上面有一句话：
当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
既然 “insert on duplicate key update” 会导致 Next-key Locks， 并且看起来失去了降级的能力，那么我们使用单纯的update是不是就可以了呢
做个简单的实验即可
CREATE TABLE user (
id BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT(3) UNSIGNED NOT NULL,
sex INT(1) UNSIGNED NOT NULL COMMENT '0:Female, 1:Male',
KEY `idx_age` (`age`),
KEY `idx_sex` (`sex`)
) ENGINE=InnoDB;
session1:
mysql> insert into user values (1, 'Jack', 23, 1) on duplicate key update age = 1;
Query OK, 1 row affected (0.00 sec)
session2:
mysql> mysql> into user values (2, 'Lucy' 'Jack', 23, 1) on duplicate key update age = 1;
这个时候session2 会被锁定，在等待session1的锁释放
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+----------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| 306570299:598782:3:1 | 306570299 | X | RECORD | `gifshow`.`user` | PRIMARY | 598782 | 3 | 1 | supremum pseudo-record |
| 306570294:598782:3:1 | 306570294 | X | RECORD | `gifshow`.`user` | PRIMARY | 598782 | 3 | 1 | supremum pseudo-record |
+----------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+----------------------+-----------------+----------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+----------------------+-----------------+----------------------+
| 306570299 | 306570299:598782:3:1 | 306570294 | 306570294:598782:3:1 |
+-------------------+----------------------+-----------------+----------------------+
1 row in set, 1 warning (0.00 sec)
seesion1拥有了独占锁，和session2等待session1的锁
应证了我们的想法
方案优化
我们将原来 [ insert on duplicate key update ] 方案
改为：
1 select:
1.1 存在 -> update -> 返回
1.2 不存在 -> insert
1.2.1 -> insert 成功 -> 返回
1.2.3 -> insert 不成功 -> 捕获异常 update -> 返回
本地测试：
mysql> update user set user.age = 1 where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (2, 'Lucky', 23, 1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
Empty set, 1 warning (0.00 sec)
不存在锁等待时间，加快了sql执行的速度。
我们再次对优化之后的代码进行压测
最大QPS由小于2w -> 8W
问题解决！
总结
（1） “insert on duplicate key update” 会使用Next-key Locks，不仅会锁住行本身，还会锁住前后范围内的数据行，导致锁等待。
（2）update语句在索引含有唯一主健的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
3）那么在数据表本身存在多个联合唯一键的时候，update语句还会降级吗？我们之后进行更多的探索和实验。
感谢
这是预热飞行棋项目和业务中台在配合的过程中碰到的问题，业务场景由@雷金燕同学发现，并且实际解决业务场景中的问题，此处特别感谢。
另外感谢@任飞子@刘正阳等同学提供指导和支持。