MYSQL VS POLARDB 唯一索引死锁与应用设计

#issue 68021 MySQL unique check 问题 - 知乎 (zhihu.com)

事情的开始是这样的，最近和阿里云密切联系，也成为他们的大客户，（我们当然是大客户，BIG BIG BIG potato）,也就和他们的核心的研发的同学有了密切的接触，这篇文字就是他们的顶尖人物写的关于MYSQL 唯一索引的问题的文字，也感谢他推送给我。

实际上在DBA 这个行业里面关于唯一索引的问题，一直是争论不休，但是对于开发本身，DBA 是缺乏说服力的，我们只能说，你这样做性能会受到损失， 当然这样说和 bull shit 也没有什么两样。

我们截取陈老师文字中关于伪代码的部分

 find the B-tree page in the secondary index you want to insert the value to
     assert the B-tree page is latched
     equal-range = the range of records in the secondary index which conflict with your value 
     if(equal-range is not empty){
       release the latches on the B-tree and start a new mini-transaction
       for each record in equal-range
         lock gap before it, and the record itself (this is what LOCK_S does)
       also lock the gap after the last(equal-range)
       also (before Bug #32617942 was fixed) lock the record after last(equal-range)
       once you are done with all of the above, find the B-tree page again and latch it again
     }
     insert the record into the page and release the latch on the B-tree page.

我们直入主题，唯一索引在插入的时候

1 索引是有顺序的

2 唯一索引是有唯一性的

虽然我们使用的隔离级别是RC ，无论在MYSQL 还是POLARDB 是不会出现GAY LOCK，但是，但是 ，但是

你是唯一索引

我们需要确认的是

1 唯一索引在插入的时候会判断目前索引中是否有同样的值

2 插入时要避免同时有其他同样的值插入

3 插入时还要判断顺序，并且还要考虑插入时周围值的变动

基于这些考虑，锁必然是必不可少的。

我们来看这段伪代码

find the B-tree page in the secondary index you want to insert the value to
     assert the B-tree page is latched

第一步，找到你要插入数据的索引页面，并且给这个页面上 latch 锁

equal-range = the range of records in the secondary index which conflict with your value 
     if(equal-range is not empty){
       release the latches on the B-tree and start a new mini-transaction
       for each record in equal-range
         lock gap before it, and the record itself (this is what LOCK_S does)
       also lock the gap after the last(equal-range)
       also (before Bug #32617942 was fixed) lock the record after last(equal-range)
       once you are done with all of the above, find the B-tree page again and latch it again
     }

第二部是一个具有原子性的操作，

1 获取与你插入值有冲突的范围，（尤其对那些多个键值是唯一索引的情况）

2 如果你插入的键值的冲突范围并不为空

3 将撤销原有的栓锁，将锁的粒度变小

4 开始针对可能产生冲突的键值进行check (期间产生间隙锁) 2 如果没有冲突，数据插入到页面

在整个的操作过程中，是需要对插入的整体过程加GAP 锁，保证在判断后你插入数据前，不会有其他的数据插入到这个位置。

之所以是说后续，这里考虑的问题是多键值的唯一索引与单键值的索引,为什么要考虑这个问题，主要还是目前使用的 next-key lock 在位置索引中可能产生的范围的大小问题。

举例

在表设计时，我们可以对多列产生唯一索引，而根据上文中的描述，唯一的键值要插入到索引的情况下，是需要针对一段范围的数据进行锁定的，那么你的值越多，产生的范围会越大，导致出现死锁的概率就越大。

上文中从源代码的方面进行了阐述，如何对数据库的源代码进行改造，但使用者怎么办，如何去最大化的规避问题。

我们需要确认几点

1 数据插入索引是有序的

2 数据插入到唯一索引时尽量少进行上面范围的GAP LOCK

这里就基于范围的问题进行讨论，如何能在程序设计的基础上最大化的减小产生死锁的几率和范围。

如果有多个键值要进行唯一索引的处理时，需要通过HASH 算法（根据算法尽量避免由于HASH 算法导致的虽然值不同，但HASH 后值相同的可能性），通过将多个字段通过算法转换为 一个HASH 值，并且对这个HASH值的字段建立唯一索引。

这样的好处显而易见

1 降低上文中提到在MYSQL 或POLARDB 中唯一索引出现死锁的可能性

2 降低一个表上出现较大唯一索引的情况（一个表中包含索引的整体字节数是有限制的）

3 对应用友好和灵活，例如我今天是3个字段，明天可能是四个字段，那么实际上，产生这样的情况下，表的索引不必改动，改动的是应用程序中关于插入HASH 字段的算法中包含的字段的数量即可。否则你又会建立一个新的唯一索引。

其实数据库本身作为三大系统之一的系统，包容性应该是很高的，但基于数据库的对于数据的严谨性和逻辑性的问题，要求很高，导致使用数据库的过程中，需要对数据库本身的原理进行理解，尽量避免一些数据库本身无法在短时间满足的 性能 --- 功能 之间的矛盾，所以在设计应用程序时也应该有的放矢的进行有效设计。