记一次项目上Mysql死锁问题的定位

最新推荐文章于 2025-09-11 17:39:03 发布

原创

最新推荐文章于 2025-09-11 17:39:03 发布 · 650 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #锁

最近项目上接了一个需求，处理系统中出现的慢SQL，排查过程中通过druid的监控看到了系统中不少死锁的报错提示，即

Deadlock found when trying to get lock; try restarting transaction

首先根据报错信息行数定位到具体代码报错位置，固定到service层，可以看到如下代码

CTReportDaily dbCTReportDaily = ctReportDailyDao.getByCtAndShopAndDay(bloc, project,
        collectionTerminalNo, "-", tradeBalanceTime);
    if (dbCTReportDaily == null) {
      CTReportDaily ctReportDaily = new CTReportDaily();
      ctReportDaily.setUuid(UUID.randomUUID().toString());
      ctReportDaily.setBloc(bloc);
      ctReportDaily.setProject(project);
      ...
      ctReportDailyDao.insert(ctReportDaily);
      LogUtil.info(logger,
          MainEvent.fetchMainEvent(bloc, project, collectionTerminalNo, EventStage.T41));
    } else {
      dbCTReportDaily.setInitTradeCount(initTradeCount);
      ctReportDailyDao.updateyTicketData(dbCTReportDaily);
      LogUtil.info(logger,
          MainEvent.fetchMainEvent(bloc, project, collectionTerminalNo, EventStage.T41));
    }

简单理解为按条件查询 CTReportDaily表记录，查询不到新增，查询到则更新。此处getByCtAndShopAndDay()查询已走唯一键索引，具体报错在 ctReportDaily.insert(ctReportDaily)；这一行报死锁错误

开始简单的以为查询没有锁住行，导致并发更新该行而死锁，于是在 getByCtAndShopAndDay()查询中增加 for update，使其由快照读变成当前读，即加上一个排它锁，其他事务不允许读不允许写。但是加上后经过压测发现死锁问题并没有解决。。。
仔细研究日志后发现，另外一处报死锁的日志，定位到代码如下

CTReportDaily dbCTReportDaily = ctReportDailyDao.getByCtAndShopAndDay(bloc, project,
          collectionTerminalNo, "-", printDate);
      if (dbCTReportDaily == null) {
        CTReportDaily ctReportDaily = new CTReportDaily();
        ctReportDaily.setUuid(UUID.randomUUID().toString());
        ctReportDaily.setBloc(bloc);
        ctReportDaily.setProject(project);
        ...
        ctReportDailyDao.insert(ctReportDaily);
        LogUtil.info(logger,
            MainEvent.fetchMainEvent(bloc, project, collectionTerminalNo, EventStage.T40));
      } else {
        if (cTReportTicket.getTicketCount() != null) {
          dbCTReportDaily.setTicketCount(cTReportTicket.getTicketCou