一次线上Mysql Deadlock

后台日志报错如下

### SQL: UPDATE t_shop_mp_liveroom     SET liveStatus = ? , coverImg = ?,shareImg = ?     where shopId =? and appId = ? and roomId =?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction] with root cause
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

定位报错java代码

 while (true){
 		/*
 		 省略部分代码
 		 */     
       jsonObjects.stream().parallel().map(jsonObject -> {
               MpLiveRoom mpLiveRoom = getMpLiveRoom(jsonObject);
               mpLiveRoom.setAppId(appId);
               mpLiveRoom.setShopId(shopId);
               return mpLiveRoom;
           }).forEach( liveRoom -> {
               insertOrUpdate(liveRoom);
           });
        }

经过网上查阅 然后分析是应该为行级锁导致的
我们的xml是这样的

  <update id="updateLiveStatus" parameterType="com.leyingkeji.shop.entity.mp.MpLiveRoom">
    UPDATE t_shop_mp_liveroom
    SET liveStatus = #{liveStatus} , coverImg = #{coverImg,jdbcType=VARCHAR},shareImg = #{shareImg,jdbcType=VARCHAR}
    where shopId =#{shopId} and appId = #{appId} and roomId =#{roomId}
  </update>

数据库的索引如下：

  KEY `sa_id` (`shopId`,`appId`) USING BTREE,
  KEY `roomId` (`roomId`) USING BTREE,

问题的缘由也是犹豫这个索引的创建 由于第一个索引的存在 mysql在更新的时候会锁定 所有 shopId appId 相同的列 然后另外的并发操作进来的时候 也会试图锁定这些表

解决的办法就是 创建一个 shopId appId roomId的三个联合索引 让mysql的更新的时候只会锁定其中的一行数据！ 这样就不会发生冲突！

  KEY `uniId` (`shopId`,`appId`,`roomId`)