我目前在一个数据迁移项目，主要工作流程是，从Postgres中读取数据，然后清洗，解析，再通过RPC，调用业务服务端口，导入到业务服务数据库。项目最近要上线了，进入了调优阶段，踩了一些mysql相关的坑，我会陆陆续续的写出来，这次先分享两个案例：

  1. 发号器导致的并发度问题
  2. 外键导致的死锁
    本文章先分享第一个案例。

1. 背景

我们有一个业务服务，其中利用mysql实现了一个发号器。表结构如下：

  1. create table requisition_number_sequence
  2. (
  3.     id        bigint unsigned auto_increment
  4.         primary key,
  5.     user_id varchar(50) not null,
  6.     count     int(4)      not null,
  7.     date      date        not null,
  8.     type      varchar(50) not null,
  9. );
  10. create index idx_user_id
  11.     on requisition_number_sequence (user_id);

由于保密原则，我对其中的字段名做了简单的修改。我描述下业务服务这边的需求：

  1. 每个user_id + type是唯一的
  2. 每个type对应一项业务
  3. 每天会将count清零，但不会新建一行，只是更新原有行的count和date
    使用方式，其实是mysql生成的jpa语句，我这里只标出最终的sql：
  1. -- 先查询
  2. select * from requisition_number_sequence where user_id =:user_id and type = :type
  4. -- 然后更新
  5. update count =:count and date=:date where user_id =:user_id and type = :type

java 代码：

  1. publice interface RequisitionNumberSequenceRepository extends JpaRepository<RequisitionNumberSequence, Long> {
  4.     Optional<RequisitionNumber> findByUserIdAndType(String userId, String type);
  5. }
  7. public class RequisitionNumberSequenceService {
  8.     private RequisitionNumberSequenceRepository repository;
  10.     public String nextRequistionNumber(RequisitionType type, String userId) {
  11.         RequisitionNumberSequence printingRunningNumber = getPrintingRunningNumber(type, userId);
  12.         String printingNumber = printingRunningNumber.nextRequisitionNumber();
  13.         this.sequenceRepo.save(printingRunningNumber);
  14.         return printingNumber;
  15.    }
  17.    private RequisitionNumberSequence getPrintingRunningNumber(
  18.       RequisitionType type, String userId) {
  19.     return this.sequenceRepo
  20.         .findByUserIdAndType(userId, type)
  21.         .orElseGet(() -> RequisitionNumberSequence.newRequisitionNumber(userId, type));
  22.   }
  23. }
  25. public class RequisitionNumberSequence {
  26.    // ... 只列出相关代码
  27.    synchronized String nextRequisitionNumber() {
  28.     if (date.isBefore(LocalDate.now(ZoneId.of(LOCAL_ZONE_ID)))) {
  29.       reset();
  30.     }
  32.     return type.getPrefix() + DATE_TIME_FORMATTER.format(date) + String.format("%04d", ++ count);
  33.   }
  34. }

2. 第一个问题：JPA使用不当导致的发号器重复问题

我们再迁移数据时发现，该发号器生成的number有重复，导致插入业务表示，唯一索引冲突。有人可能已经看出上面代码的问题了，Service中的nextRequistionNumber是先查，后更新，导致同一时间可能有两个请求同时拿到了RequisitionNumberSequence，都做了++count, 导致最后使用number的时候其实是同一个。

解决思路，增加排他锁，JAVA代码：

  2. publice interface RequisitionNumberSequenceRepository extends JpaRepository<RequisitionNumberSequence, Long> {
  4.     @Lock(LockModeType.PESSIMISTIC_WRITE)
  5.     Optional<RequisitionNumber> findByUserIdAndType(String userId, String type);
  6. }

最终的sql：

  1. select * from requisition_number_sequence where user_id =:user_id and type = :type for update

上线测试后，发现号重复的问题没有了，但吞吐下降了，迁移时间增加了，并且偶尔出现以下错误：

  1. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

JPA会抛出LockTimeoutException错误。

3. 由于索引创建不当导致的锁超时问题

定位问题时，发现是第一步查询时获取排他锁超时，但我们当时的迁移任务，相同type类型的请求并发度只是1，怎么会出现锁等待呢？
不同的type请求并发度大概是10左右，难道是不同Type的请求相互block了？
这要从mysql的锁的位置谈起了，mysql的锁是加在索引上的，也就是说mysql会在索引上标记该数据当前的锁状态。我们来看一下当前表的索引：

  1. show indexs from requisition_number_sequence
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
requisition_number_sequence 0 PRIMARY 1 id A 629 BTREE
requisition_number_sequence 1 idx_user_id 1 user_id A 234 BTREE

发现当前只有user_id和主键索引，导致我们查询时，mysql优化器选择的是user_id的索引，如果此时第一个事务到达，会将该user_id锁住，其他查询该user_id的请求都会被block住。问题定位到，解决方案很简单，创建user_id和type的联合索引：

  1. create unique index requisition_number_sequence_user_id_type_uindex on requisition_number_sequence (user_id, type);

这样配置后，发现没有锁等待了，但尝试增大同一type的请求数量（迁移任务）后，发现又出现了锁等待的问题。

4.调整JPA事务优化锁超时

使用如下语句，查看数据库状态:

  1. show engine innodb status;
  3. select * from sys.innodb_lock_waits;

发现有些事务比较长，一直持有着requisition_number_sequence的锁，导致锁超时，篇幅有限，这里不列出语句执行结果了，请自行尝试。
我们来分析一下我们的java代码，其实我们这边针对不同的type，90%是插入操作，大部分业务逻辑是这个形态：

  1. 查询数据
  2. 生成requisition_number
  3. insert到数据库，会有一些级联插入
    两个相同Type的请求会在第二步，也就是生成requisition_number时互斥。我们知道锁只有在事务完成时才会释放，所以我们发现，我们的两个请求并没有完整的并行执行。
    解决思路，在调用RequisitionNumberSequenceService.nextRequistionNumber生成一个新的事务，将原有的大事务，拆分成两个事务，这样每次调用发号器发号时会串行，但外面的insert相关事务不会互斥。代码如下:
  1. public class RequisitionNumberSequenceService {
  2.     private RequisitionNumberSequenceRepository repository;
  4.     @Transactional(propagation = Propagation.REQUIRES_NEW)
  5.     public String nextRequistionNumber(RequisitionType type, String userId) {
  6.         RequisitionNumberSequence printingRunningNumber = getPrintingRunningNumber(type, userId);
  7.         String printingNumber = printingRunningNumber.nextRequisitionNumber();
  8.         this.sequenceRepo.save(printingRunningNumber);
  9.         return printingNumber;
  10.    }
  12. }

通过设置@Transactional(propagation = Propagation.REQUIRES_NEW)，将spring的事务传播级别设置为REQUIRES_NEW，这样不管外面调用该方法时，是否有事务存在，都会生成一个新的事务，方法结束时该事务也会提交。
但这种方式不是没有缺点，如果外面的事务回滚，该发号器生成号码的事务并不会回滚，可能会导致一些数据空洞。后面和业务服务的负责人沟通，他们表示能接受这种空洞。

至此，一切变得似乎很很美好，上线后，迁移数据时还是有锁超时的现象。预知后事如何，且听下回分解。

知识点

  1. for update是mysql中的排他锁，lock in share mode是共享锁
  2. mysql的锁是在索引上的
  3. 在长事务中尽量将带锁的操作往后放。
  4. 可以通过@Transactional(propagation = Propagation.REQUIRES_NEW)等方式，控制spring事务管理的传播级别，来间接控制一些锁的粒度。