org.springframework.dao.CannotAcquireLockException异常分析

错误信息如下：

2017-09-27 16:27:16.153 - 【com.ldyun.base.service.impl.BaseRetailOrderServiceImpl】 - 新增零售商品订单~org.springframework.dao.CannotAcquireLockException: ### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction### The error may involve com.ldyun.retail.mapper.RetailGoodsMapper.updateBySql-Inline### The error occurred while setting parameters### SQL: update retail_goods  SET stocks = stocks - CASE id  WHEN 83 THEN 1  END,saleCount = saleCount + CASE id  WHEN 83 THEN 1  END WHERE id IN (83)### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction~

经过网上资料查询，原因为：Spring 事务嵌套造成死锁。

经核实代码的确是service里面调用service，且两个service都配置了事务。顶层service名称为addRetailOrder，内层service名称为updateBysql。

事务配置为：

<tx:advice id="txAdvice" transaction-manager="transactionManager"><tx:attributes><tx:method name="add*" propagation="REQUIRED" read-only="false" rollback-for="java.lang.Exception" /><tx:method name="del*" propagation="REQUIRED" read-only="false" rollback-for="java.lang.Exception" /><tx:method name="update*" propagation="REQUIRED" read-only="false" rollback-for="java.lang.Exception" /><tx:method name="insert*" propagation="REQUIRED" read-only="false" rollback-for="java.lang.Exception" /><tx:method name="try*" propagation="REQUIRED" read-only="false" rollback-for="com.wm.base.exception.TransactionRollbackException" /></tx:attributes></tx:advice>

解决方法为：

1、内层service实现方法配置@Transactional(propagation=Propagation.SUPPORTS)

@Override@Transactional(propagation=Propagation.SUPPORTS)public int updateBySql(String sql) {// TODO Auto-generated method stubreturn retailGoodsDao.updateBySql(sql);}

2、外层service不配置事务，即修改方法名称。