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

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

Deadlock found when trying to get lock; try restarting transaction

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

2. 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)；这一行报死锁错误

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

5. 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.getTicketCount());
           }
           ctRe