記一次 MySQL select for update 死鎖問題

東征發表於2020-04-03
MySql

背景

生產環境出現MySQL死鎖異常，MySQL版本5.6，隔離級別 RC。

[CommandConsumer-pool-thread-1] Process error : 
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in class path resource [mybatis/mapper/sequence.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT current_seq FROM sequence WHERE type = ? AND `date` = ? FOR UPDATE
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
複製程式碼

程式碼分析

根據日誌記錄，導致死鎖的關鍵程式碼如下

    /**
     * 根據傳入引數，生成一個序列號。
     *
     * @param type 序列號型別
     * @param date 時間
     * @return 一個新的序列號，第一次呼叫返回1，後續根據呼叫次數遞增。
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
    public int getSequence(String type, LocalDate date) {

        // select * from sequence where type = #{type} and date = #{date} for update
        Sequence seq = mapper.selectForUpdate(type, date);

        // seq 還未初始化，select for update 就沒鎖住
        if (seq == null) {
            // insert ignore into sequence(type, date, current_seq) values(#{type}, #{date}, #{currentSeq})
            if (mapper.insertIgnore(type, date, 1)) {
                return 1;
            }
            // insert ignore 競爭失敗，重試
            return getSequence(type, date);
        }

        // update sequence set current_seq = current_seq + 1 where id = #{id}
        mapper.forwardSeq(seq.getId(), 1);

        return seq.getCurrentSeq() + 1;
    }

   CREATE TABLE `sequence` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
      `type` varchar(32) NOT NULL COMMENT '型別',
      `date` date NOT NULL COMMENT '時間',
      `current_seq` int(11) NOT NULL COMMENT '當前最大序號',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_seq` (`date`,`type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='序列號'
複製程式碼

功能簡述 這段程式碼主要實現了一個序列號的獲取功能，這種功能常用於生成單據號。 舉個例子：我們需要給每個付款單生成一個付款單號，格式為：A-20200101，表示A公司在20200101這一天的付款單。 但是A公司每天不止一個付款單，為了保證付款單號的唯一性，我們還需要加一個自增的序列號。例如：A-20200101-1，表示A在2020-01-01這天的第一個付款單，以此類推，第二個、第三個付款單號即 A-20200101-2，A-20200101-3...

程式碼實現 為了保證在併發環境下，序列號不會重複，程式碼裡先通過 select 唯一索引 for update 鎖住某一行資料，然後更新該行資料的current_seq = current_seq + 1，返回current_seq。

但有一個邊界條件需要特殊處理，那就是第一次呼叫該函式時，資料還不存在， select 唯一索引 for update 返回 null ，需要 insert 一個序列號為1的初始資料，為了防止 for update 返回 null 沒鎖住導致多次 insert ，程式碼裡用了 insert ignore，當 insert ignore 失敗時重新呼叫（遞迴） getSequence 獲取下一個序列號。

看完程式碼，並沒有發現明顯異常，我們嘗試在本地復現一下死鎖。

本地復現死鎖：

手動復現：

  • 準備條件
    • MySQL 5.6
    • 事務隔離級別 RC
    • 準備兩個資料庫連線 A、B
  • 通過觀察SQL日誌，並經過多次實驗，發現以下兩種操作可以復現死鎖
  • 操作步驟1
    • A begin; insert (ignore) xxx; 執行失敗，因為xxx已存在。
    • B begin; select xxx for update; 阻塞，因為A insert 已持有鎖
    • A select xxx for update; 成功
    • B 阻塞結束，提示死鎖
  • 操作步驟2
    • A begin; select xxx for update; 成功執行，持有排他鎖
    • B begin; select xxx for update; 阻塞，等待A釋放排他鎖
    • A insert (ignore) xxx; 成功執行
    • B 阻塞結束，提示死鎖
  • 觸發死鎖操作的共性
    • 都是某資料已存在，某事務內通過 insert 拿到鎖再去操作 select for update，或通過 select for update 拿到鎖再去操作 insert，就會造成其他 for update 等待鎖的事務提示死鎖。
  • 死鎖原理
    • 尚不明確（路過的朋友有知道的還請賜教）

單元測試復現：

    @Autowired
    private ISequenceService sequenceService;

    @Test
    public void test() throws InterruptedException {
        ExecutorService executorService = Executors.newFixedThreadPool(10);

        List<Runnable> runnableList = Lists.newLinkedList();

        for (int i = 0; i < 100; i++) {
            runnableList.add(() -> sequenceService.getSequence("TX", LocalDate.now()));
        }

        runnableList.forEach(executorService::execute);

        executorService.shutdown();
        executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
    }
複製程式碼

解決方案

  • 通過本地手動復現死鎖，我們發現當在一個事務中 insert ignore 失敗後 select for update ，才會出現死鎖，那麼避免兩個操作在同一個事務出現即可。
  • 更改後程式碼
    /**
     * 根據傳入引數，生成一個序列號。
     *
     * @param type 序列號型別
     * @param date 時間
     * @return 一個新的序列號，第一次呼叫返回1，後續根據呼叫次數遞增。
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
    public int getSequence(String type, LocalDate date) {

        // select * from sequence where type = #{type} and date = #{date} for update
        Sequence seq = mapper.selectForUpdate(type, date);

        // seq 還未初始化，select for update 就沒鎖住
        if (seq == null) {
            // insert ignore into sequence(type, date, current_seq) values(#{type}, #{date}, #{currentSeq})
            if (mapper.insertIgnore(type, date, 1)) {
                return 1;
            }
            // insert ignore 競爭失敗，在一個新事務中重試，從而避免死鎖
            return applicationContext.getBean(ISequenceService.class).getSequence(type, date);
        }

        // update sequence set current_seq = current_seq + 1 where id = #{id}
        mapper.forwardSeq(seq.getId(), 1);

        return seq.getCurrentSeq() + 1;
    }
複製程式碼
  • 經過單元測試驗證，成功解決死鎖問題。

總結

  • 在帶多個鎖的方法中使用遞迴時，要特別注意，容易造成加多個鎖順序不一致的問題，從而引發死鎖（跟本例無關，純屬感想）。
  • 在一個事務中 select for update 鎖住某行資料後，再 insert (ignore) 這行資料，出現死鎖，這個可以理解，因為一般不會有邏輯 select 查出來資料了，還 insert 。但是我們無意中會寫出 insert ignore 失敗後 select for update 這種程式碼，而它跟 select for update 後 insert 在加鎖的原理上是基本一致的，會造成死鎖，所以日常寫程式碼要注意這一點。

相關文章