最近做了一個定時器，然后在一個時間點對資料修改狀態，但是導致了資料庫死鎖
報錯資訊：
### SQL: UPDATE user_minute_data SET version=? WHERE (user_id = ? AND minute BETWEEN ? AND ? AND version = ?)
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
根據網上說的，要在update之間做 select ... for update 但是還是會報錯
代碼
@Transactional
public int sumMinuteDataByUserAndDay(String userId, String date) {
//先查詢當前條件下時候匯總
EntityWrapper<HourData> entityWrapper=new EntityWrapper<>();
HourData searchData=https://bbs.csdn.net/topics/new HourData();
searchData.setUserId(userId);
searchData.setHour(date);
entityWrapper.setEntity(searchData);
List<HourData> list=this.selectList(entityWrapper);
//每查找到資料，開始匯總
if(list.size()==0){
log.info(messageBlock+"未匯總，開始匯總");
int i=this.baseMapper.insertSumMinuteDataByUserAndDay(userId, date);
if(i==1){
//匯總完成后，標記低一級資料為 version=2
log.info(messageBlock+"完成，將已匯總資料標記版本");
MinuteData lowData = new MinuteData();
lowData.setVersion("2");
EntityWrapper<MinuteData> entityWrapper2=new EntityWrapper<>();
//查詢條件 按照索引順序來
entityWrapper2.eq("user_id",userId);
entityWrapper2.between("minute",date+":00",date+":59");
entityWrapper2.eq("version","1");
EntityWrapper<MinuteData> entityWrapper3=new EntityWrapper<>();
//查詢條件 按照索引順序來
entityWrapper3.eq("user_id",userId);
entityWrapper3.between("minute",date+":00",date+":59");
entityWrapper3.eq("version","1");
entityWrapper3.last(" for update");
lowService.selectList(entityWrapper3);
boolean update = lowService.update(lowData,entityWrapper2);
if(update){
log.info(messageBlock+"成功 修改狀態完成");
return 1;
}else{
log.error(messageBlock+"成功 修改狀態例外，回滾。注意排查問題");
throw new RuntimeException(messageBlock+"成功 修改狀態例外");
}
}else{
log.error(messageBlock+"出錯，回滾。注意排查問題");
throw new RuntimeException(messageBlock+"匯總出錯");
}
}else if(list.size()==1){
log.info(messageBlock+"已經匯總過");//，要匯總去實作覆寫匯總方法");
}else{
log.info(messageBlock+"匯總資料已重復，用戶id："+userId+" 時間："+date+" 去資料庫清理 ");
}
return 0;
}
資料庫show engine innodb status
[align=left]------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-22 16:03:00 18a0
*** (1) TRANSACTION:
TRANSACTION 403535, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 2936, 311 row lock(s), undo log entries 2
MySQL thread id 19, OS thread handle 0x271c, query id 72870 localhost 127.0.0.1 root updating
UPDATE user_minute_data SET version='2'
WHERE (user_id = '2' AND minute BETWEEN '2019-10-22 15:00' AND '2019-10-22 15:59' AND version = '1')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 6 n bits 248 index `PRIMARY` of table `demo`.`user_minute_data` trx id 403535 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 403537, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
14 lock struct(s), heap size 2936, 255 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 0x18a0, query id 72874 localhost 127.0.0.1 root updating
UPDATE user_minute_data SET version='2'
WHERE (user_id = '3' AND minute BETWEEN '2019-10-22 15:00' AND '2019-10-22 15:59' AND version = '1')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 6 n bits 248 index `PRIMARY` of table `demo`.`user_minute_data` trx id 403537 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 6 n bits 248 index `PRIMARY` of table `demo`.`user_minute_data` trx id 403537 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (2)[/align]
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/43469.html
標籤：MySQL