Analysis of Org.springframework.dao.CannotAcquireLockException Anomaly

The error message is as follows:

2017-09-27 16:27:16.153-"Com.ldyun.base.service.impl.BaseRetailOrderServiceImpl"-New retail product order ~  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 THEN 1 E Nd,salecount = Salecount + case ID if THEN 1 end WHERE ID in () ### Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLT Ransactionrollbackexception: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~ 

After the online data query, the reason is: Spring transaction nesting caused deadlock.

Verified that the code is indeed the service inside the services, and two service are configured transactions. The top-level service name is called Addretailorder, and the inner service name is called Updatebysql.

The transaction is configured to:

	<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>

The solution is:

1, the Inner Layer Service implementation method configuration @transactional (Propagation=propagation.supports)

	@Override
	@Transactional (propagation=propagation.supports) public
	int updatebysql (String sql) {
		//TODO auto-generated method Stub return
		retailgoodsdao.updatebysql (SQL);
	

2, the outer service does not configure transactions, that is, modify the method name.