The project reported a deadlock exception

org.apache.ibatis.exceptions.PersistenceException:
### Error flushing statements. Cause: org.apache.ibatis.executor.BatchExecutorException: com.baturu.wms.business.outbound.dao.OutboundNoticeHeaderDao.updateById (batch index #1) failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
### Cause: org.apache.ibatis.executor.BatchExecutorException: com.baturu.wms.business.outbound.dao.OutboundNoticeHeaderDao.updateById (batch index #1) failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
...
Caused by: org.apache.ibatis.executor.BatchExecutorException: com.baturu.wms.business.outbound.dao.OutboundNoticeHeaderDao.updateById (batch index #1) failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:148)
...
at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:253)
... 44 more
Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
...
at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:122)
... 52 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
...

The first time I checked the deadlock exception, I didn't know how to start at first. With the strength of innodb recently, Baidu first asked how to read the deadlock log in mysql

View mysql deadlock log

Command:

mysql> show engine innodb status; 

There will be a lot of data. Don't panic. Just look at the deadlock part we need
Deadlocks are generally caused by two transactions waiting for each other's locks. Therefore, the purpose of looking at the log is to find out what locks the two transactions are waiting for each other

*** (1) TRANSACTION:
TRANSACTION 468429219, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 17
MySQL thread id 31567782, OS thread handle 140580182210304, query id 786068511 172.16.18.5 ops updating
UPDATE wms_outbound_notice_header SET status=10,wave_code='19089',wave_error_msg='',updater=null,update_date='2021-11-10 17:01:09.377'  WHERE id=1458342344177786881

Transaction 1 generates a deadlock when executing [UPDATE wms_outbound_notice_header...]

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9028 page no 90 n bits 136 index PRIMARY of table `btr_wms`.`wms_outbound_notice_header` trx id 468429219 lock_mode X locks rec but not gap waiting

Transaction 1 waiting for lock:
Transaction id [468429219] X lock (i.e. exclusive lock) on the waiting table [btr_wms.wms_outbound_notice_header]
Therefore, the id of transaction 1 is 468429219

*** (2) TRANSACTION:
TRANSACTION 468429218, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
248 lock struct(s), heap size 41168, 40534 row lock(s), undo log entries 1662
MySQL thread id 31568472, OS thread handle 140582284355328, query id 786068544 172.16.18.5 ops updating
UPDATE wms_wave_detail  SET active=0,updater=null,update_date='2021-11-10 17:01:09.441' WHERE outbound_notice_id IN (1452477021064904706)

Transaction 2 waits for lock when executing [UPDATE wms_wave_detail...]

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9028 page no 90 n bits 136 index PRIMARY of table `btr_wms`.`wms_outbound_notice_header` trx id 468429218 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8978 page no 395 n bits 88 index PRIMARY of table `btr_wms`.`wms_wave_detail` trx id 468429218 lock_mode X waiting

Transaction 2 has locks:
Transaction id [468429218] holding table BTR_ wms.wms_ outbound_ notice_ X lock of header
Transaction 2 wait lock:
Transaction id [468429218] waiting table BTR_ wms.wms_ wave_ X lock of detail

*** WE ROLL BACK TRANSACTION (1)

Finally, transaction 1 is rolled back to resolve the deadlock

From the above logs, we can conclude that locks wait for each other:

• Holding table WMS_ outbound_ notice_ The lock of the header is waiting for the table wms_wave_detail lock
• Holding table WMS_ wave_ The lock of detail is waiting for the table wms_outbound_notice_header lock

code analysis

From the exception log, you can find the code location where transaction 1 executes

@Override
@Transactional(rollbackFor = Exception.class)
public Long createWave(Xxx xxx) {  
	...
	waveDetailService.insertBatch(saveWaveDetailList);
	//Wait for lock while performing the following update
	outboundNoticeHeaderService.updateBatchById(updateOutboundNoticeHeaderList);
	...
}

Next, find out where to update the outboundNoticeHeader first and then the waveDetail
Find out where to update the waveDetail according to the sql executed by transaction 2

@Override
@Transactional(rollbackFor = Exception.class)
public void saveWaveErrorMsg(Xxx xxx) {
    ...
    waveDetailService.update(WaveDetailEntity.builder().active(BooleanStatus.FREEZE).build(), detailQueryWrapper);
    ...
    outboundNoticeHeaderService.update(
            OutboundNoticeHeaderEntity.builder()
                    .status(OutboundNoticeStatusEnum.CREATE.getType())
                    .waveErrorMsg(errorMsg)
                    .build(),
            outboundNoticeHeaderEntityQueryWrapper);
}

It looks ok. Update the waveDetail first and then the outboundNoticeHeader
But look at the outer code and find the big problem

@Transactional(rollbackFor = Exception.class)
public void execute(Xxx xxx){
	...
	for (OutboundNoticeHeaderDTO outboundNoticeHeaderDTO : outboundNoticeHeaderDTOS) {
		...
		waveHeaderService.saveWaveErrorMsg(errorMsg, Lists.newArrayList(outboundNoticeHeaderDTO));
		...
	}
	...
}

The method [saveWaveErrorMsg] is nested with a for loop, and the method [execute] where the for loop is located starts the transaction
When [saveWaveErrorMsg] is executed for the first time, the lock of the outboundNoticeHeader will be obtained. Then, when [waveDetailService.update] is executed for the second time, you need to wait for the lock of waveDetail
It is in the reverse order of the method [createWave] to obtain locks, so a deadlock occurs
Finally, through analysis, it is found that it is wrong to start a transaction on [execute], so @ Transactional is removed to solve the problem