要用唯一索引,结果被 DBA 残忍拒绝！- 大数跨境

首页

要用唯一索引,结果被 DBA 残忍拒绝！

树哥聊编程

2021-01-20

导读：我要用唯一索引结果惨遭 DBA 拒绝！这是一个很多人开发人员没注意到的一个坑！耐心看完你一定会有收获

我要用唯一索引结果惨遭 DBA 拒绝！这是一个很多人开发人员没注意到的一个坑！耐心看完你一定会有收获

1、事情背景

一个助力拿奖品的营销的活动 , 一个用户只能参加一次活动 , 要保证唯一性于是我理所应当的将 uid 和 activityId 设置成唯一索引设计好表结构之后 , 找DBA 帮忙 review , 结果惨遭拒绝！

2、对话流程

DBA : 你这个表的使用场景是什么？

我 : 用来做活动的参与记录

DBA : qps 多少有预估么？

我 : 产品说活动会有好几个渠道投放 , 会有高并发的场景, 峰值大概在

qps 5000 - 6000

DBA : 这个场景不建议你用唯一索引 , 会出问题

我 : 不就一个唯一索引么？能有啥问题啊？

DBA : 索引不能乱用 , 要分场景 , 你这个场景在高并发的情况下，会有死锁风险

我 : 啊？用个唯一索引还会死锁啊？怕了怕了

3、普及一下会涉及的锁及发生的必要条件

条件字段建有唯一索引 && 隔离级别为：READ-COMMITTED 及 REPEATABLE-READ

S 锁

共享锁

X 锁

排他锁

记录锁（Record Locks）

记录锁是最简单的行锁 , 没有什么好说的 , 都懂的对吧

// 给第五行记录上行锁
mysql> UPDATE user SET age = 18 WHERE id = 5;

间隙锁（Gap Locks）

简单理解 : RR 级别为了解决幻读产生的锁因为通常锁住一个范围又被称为范围锁

深入理解 : 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

// 假设 age 是索引 , 这条 sql 间隙锁的范围是 : (1,18) 哪怕没有 id = 12 , 13 的数据这个位置也会被上 Gap 锁
mysql> Select * from  user where id < 18 for update;

还不懂的朋友看下这篇文章:https://juejin.im/post/6844903668571963406#heading-6

Next-Key Locks

Next-key 锁 : 记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 10、11、13 和 20 这几个值，可能的 Next-key 锁如下：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁

插入意向锁（Insert Intention Locks）

插入意向锁 : 一种特殊的间隙锁 (地方把它简写成 II GAP) , 这个锁表示插入的意向 , 只有在 insert 的时候才会有这个锁

需要注意的是 , 这个锁虽然也叫意向锁 , 但是和表级意向锁是两个完全不同的概念

	Record	Gap	Next-Key	II GAP
Record	√		√
Gap				√
Next-Key	√		√
II GAP		√	√

其中行表示已有的锁 , 列表示要加的锁 , √ 表示这两种锁之间会发生阻塞。这个图还不够严谨 , 仅用来分析这个案例

重要的几个结论 :

1、插入意向锁只会和间隙锁(Gap)或 Next-key 锁冲突

2、间隙锁只和插入意向锁冲突

3、记录锁和记录锁冲突，Next-key 锁和 Next-key 锁冲突，记录锁和 Next-key 锁冲突

4、表结构

表结构如下


CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB

insert into user values(1,16,16),(2,17,17),(3,25,25);

五、加锁情况及死锁原因分析

事务并发的状况分析表 :

sessionA	sessionB	sessionC
begin;update user set c2 = 20 where c1 = 25;
	begin;insert into user(c1,c2) values(25,25);	begin;insert into user(c1,c2) values(25,25);

session A 在执行完 sql 后进行 commit 的瞬间 , sessionB 和 sessionC 其中一个就会报死锁 , 产生的原因 :

1.sessionA 在执行 update 时会在索引 c1 的 c1 = 25 这一记录上加上 X lock 日志记录为 : X lock but not gap ( gap 范围锁也是行锁的一种)

2.sessionB 和 sessionC 在执行 insert 操作时 , 由于唯一索引约束检测发生唯一冲突 , 会加上 Next-Key Lock

因为要对 (1,25] 这个区间加锁包括没有数据的这些 "间隙" , 并且当前被 sessionA 的 X lock 进行阻塞 , 进入等待

3.sessionA 在执行完 update 的 sql 准备 commit , 就会释放掉 X lock 与此同时 sessionB 和 sessionC 会马上获取 S Next-Key Lock

4.sessionB 和 sessionC 继续执行插入操作 , 在插入的时候 唯一索引 需要获取 INSERT INTENTION LOCK（插入意向锁）

并且由于插入意向锁会被 Next-Key Lock 和 gap 锁阻塞 ( 第二步加了 Next-Key Lock )

所以 sessionB 和 sessionC 会互相等待，从而造成死锁。

六、死锁的解决办法

第一种 :（需要权限）

首先查询是否锁表

show OPEN TABLES where In_use > 0;

查询进程id

// 第一列 id 就是进程 id
show processlist

杀死进程id

kill id

第二种 :（需要权限）

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

kill 进程ID

七、普通索引 PK 唯一索引

在考虑选择哪种索引之前先来看看 change buffer 这个概念 ( 引自 mysql 45 讲 )

change buffer ：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。需要说明的是，虽然名字叫作 change buffer , 实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

普通索引 : 能够利用 change buffer 直接从内存里操作数据，当查询来的时候再写入 db 进行查询

唯一索引 :直接从磁盘查到内存 , 在把 change buffer 比作缓存的情况，如果没利用上这一层缓存

将数据从磁盘读入内存涉及随机 IO 的访问是数据库里面成本最高的操作之一

change buffer 因为减少了随机磁盘访问 , 所以对更新性能的提升会很明显

可以看到使用唯一索引性能上 pk 不过普通索引还有发生死锁的风险

在高并发 , 大数据量的情况下 , 不让使用唯一索引应该是大部分 DBA 同学的心声

八、小结

唯一索引发生死锁的情况

1.查询条件字段建有唯一索引

2.隔离级别为：READ-COMMITTED 及 REPEATABLE-READ

READ-COMMITTED 因为对于唯一索引 , 检测唯一性时会去加 S next key lock

REPEATABLE-READ 同上也会有这个问题

建议：对于并发量高的场景下不要使用唯一索引 , 唯一属性尽量在业务及代码层面进行规避

比如使用 redis 的分布式锁 , 根据活动的持续时间来设置过期时间

不过又得考虑 redis 内存使用的问题了 , 分布式场景就是这样 , 解决一个问题的同时又会引入一个新问题