代码层面如何解决Lock wait timeout exceeded; try restarting transaction

Question

代码层面如何解决Lock wait timeout exceeded; try restarting transaction

代码如下：

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.alibaba.tboss.dal.mysql.workOrder.mapper.AssetBeanMapperExt.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update idc_asset_list      SET sn = ?,                                                                                                           MODIFIER = 'system',                       GMT_MODIFIED = current_timestamp,                                            remark = ?,                                                                        flow_state = ?      where asset_id = ?     and is_deleted = 'n'
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

Answer 1

这个主要还是得看你另外哪个事务太慢，把行锁一直拿着不释放。Spring的@Transactioal注解里面的timeout是可以加长时间的，但最好还是遵循默认的，找出慢的事务，通过各种优化方式解决，比如大事务拆小事务

Answer 2

建议把UPDATE功能拆分，改成先删除再插入，这样会好的多。