mysql工程死锁问题

业务场景

员工表，有两个字段organ_id和area_id
以下两个sql语句同时执行
update emp_employee set deaprtment_id=12 where deaprtment_id=4 and area_id=4;
update emp_employee set deaprtment_id=12 where deaprtment_id=44 and area_id=4;

上面两条语句分别被事务a和事务b执行，在建立索引时分别单独建立了索引

死锁流程分析

image.png
从图上分析死锁的加锁流程：

  1. a通过deaprtment_id索引锁住了deaprtment_id为4的索引项
  2. b通过deaprtment_id索引锁住了deaprtment_id为44的索引项
  3. a回表锁住聚簇索引中id为1的索引项
  4. b回表锁住聚簇索引中id为2的索引项
  5. a通过area_id索引树锁住了area_id为4的索引项
  6. b通过area_id索引树找到了area_id为4的索引项，但是没法上锁，因为当前已经被a上锁了
  7. a通过回表试图锁住area_id为4对应的聚簇索引中id为1和2的索引项，但此时id为1的索引项已经被b锁住了，a需要b释放锁， 但序号6中已经提到b已经因为和a争抢area_id为4的索引项而被阻塞了，这时会产生死锁。

    mysql如何处理死锁：

    MySQL检测到死锁后，会自动回滚代价更低的那个事务，如上边的时序图中，事务b持有的锁比事务a少，则MySQL就将事务b进行了回滚。

应当如何解决:

对deaprtment_id和area_id建立复合索引，该情境下建立联合索引，a和b都不会对无关行上锁。
建议：索引不应该建立在重复比较多的字段上也是有这个原因，因为使用索引时锁定的数据行会比较多

回表

回表发生在非聚簇索引上，拿到非聚簇索引中的主键值再到主键索引树上查找完整的记录数据。

索引覆盖

只要在一棵b+树上就可以找到要sql的值，不需要回表

索引下推

使用联合索引的情况
比如我们建立一个联合索引(name,age)有如下sql：

  1. select * from user where name = "李四" and age=20

不使用索引下推的情况：
查找到name=”李四”,获取主键值，再进行回表查询，再判断age是不是等于20
使用索引下推：
查找到name=”李四”,不需要回表，因为可以获取到年龄是不是等于20,可以直接在索引树找到

索引合并

一条SQL中使用两个或多个索引,查出来的数据集取交集或并集。

ps：mysql各种锁链接