MySQL reports an error lock wait timeout exceeded; Try restarting transaction problem solving method

Spring transaction nesting causes deadlock

In the case of high concurrency, spring transactions cause database deadlock, and subsequent operations timeout and throw exceptions.  
MySQL database adopts InnoDB mode. The default parameter is InnoDB_ lock_ wait_ Timeout set the lock waiting time to 50s. Once the database lock exceeds this time, an error will be reported.

This MySQL deadlock is mainly caused by adding, modifying or deleting transactions (adding @ transactional annotation or configuring the configuration file), and then catching the exception, resulting in the transaction not receiving the exception, waiting for the exception, not ending, and will not trigger the rollback operation, so the table is locked.

Throw the exception in the catch so that the transaction can trigger rollback and release the lock resource of the table: throw new runtimeException (E);


Or, the current call transaction method is set to Propagation.SUPPORTS:  @ Transactional(propagation=Propagation.SUPPORTS)


  perhaps   Use the following statement to find the data submitted for the transaction and kill the thread.

select * from information_ schema.innodb_ trx

kill 20057;


Similar Posts: