Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

I had a similar problem and solved it by checking the threads that are running.To see the running threads use the following command in mysql command line interface:

SHOW PROCESSLIST;

It can also be sent from phpMyAdmin if you don't have access to mysql command line interface.
This will display a list of threads with corresponding ids and execution time, so you can KILL the threads that are taking too much time to execute.In phpMyAdmin you will have a button for stopping threads by using KILL, if you are using command line interface just use the KILL command followed by the thread id, like in the following example:

KILL 115;

This will terminate the connection for the corresponding thread.

You can check the currently running transactions with

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`

Your transaction should be one of the first, because it's the oldest in the list. Now just take the value from trx_mysql_thread_id and send it the KILL command:

KILL 1234;

If you're unsure which transaction is yours, repeat the first query very often and see which transactions persist.

Check InnoDB status for locks

SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what

SELECT * FROM `information_schema`.`innodb_locks`;

After investigating the results above, you should be able to see what is locking what.

The root cause of the issue might be in your code too - please check the related functions especially for annotations if you use JPA like Hibernate.

For example, as described here, the misuse of the following annotation might cause locks in the database:

@Transactional(propagation = Propagation.REQUIRES_NEW)