关于业务服务的SQL死锁分析

 

    背景：

      最近在做数据库迁移，为了评估新库的性能，我们找性能测试的同事压测了接口。压测完后发现一个接口频繁出现死锁的问题。这个接口是添加地址，一般的添加地址直接insert就好了，但是压测的场景是新添加的这个地址是默认的地址，这个逻辑会相对复杂。主要有两步，第一步更新默认地址为非默认地址，第二部新加默认地址。隔离级别Read Repeatable 存储引擎：innodb。SQL如下：

      

-- 更新为非默认地址
update contact set default_flag = 0 where user_id = xx

-- 插入默认地址
insert into contact values(xx,xxx)

CREATE TABLE `contact` (
  `id` bigint(20) NOT NULL COMMENT '用户信息表id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `name` varchar(64) DEFAULT NULL COMMENT '姓名',
  `use_num` bigint(10) DEFAULT NULL COMMENT '使用次数',
  `province_name` varchar(50) DEFAULT NULL COMMENT '省名称',
  `city_name` varchar(50) DEFAULT NULL COMMENT '市名称',
  `district_name` varchar(50) DEFAULT NULL COMMENT '区名称',
  `id_num_vflag` tinyint(4) DEFAULT '0' COMMENT '身份证号码是否已验证',
  `mobile_enc` varchar(128) DEFAULT NULL COMMENT '电话加密',
  PRIMARY KEY (`id`),
  KEY `idx_uid_unum_utime` (`user_id`,`use_num`,`update_time`),
  KEY `idx_utime_unum` (`update_time`,`use_num`)
) ENGINE=InnoDB ;

    请思考为啥会报死锁呢？

 

    背景技术：

      InnoDB的行锁实现类别

       1: 行锁（注意锁的是索引）

       2: gap 区间锁（这个是实现read repeatable的基础，有了区间锁就能保证不会有数据在区间插入删除，所以事务内每次范围查询的sql的值总是相等。read uncommited 会有脏读，读到没提交的数据。 read commited导致幻读，事务内两次读取的数据不一致。 read repeatable 又做了升级，事务内读取的数据一致，所以叫可重复读。）

       3: 行锁+ gap 锁

 

    死锁原因：

 
      

    

 

  

      事务一的SQL1: 执行过程中使用了gap锁和三个行锁

      事务二的SQL1: 执行过程中使用了gap锁和等待行锁

      事务一的SQL2: 需要等待事务二的gap锁释放

      事务二: SQL1需要等待事务一的行锁释放

 

    解决方式：

    1. 先查询出默认地址

    2. 根据第一步的默认地址的id来更新默认地址为非默认地址

    3. 做insert操作

    为何第2不没有gap锁，因为是根据主键进行更新，主键的范围就是一个值，中间根本可能插入任何数据，唯一键也是这个效果。

 

    SQL:如下

-- 查询默认地址
select xx from contact where default_flag = 1 and user_id =xx

-- 更新为非默认地址
update contact set default_flag = 0 where id = xx

-- 插入默认地址
insert into contact values(xx,xxx)