Fix Mysql::Error: Lock Wait Timeout Exceeded; Try Restarting Transaction Deadlocks In Your Rails Tests

Posted By Weston Ganger

If you are constantly recieving errors about Mysql::Error: Lock wait timeout exceeded you may have a dangling transaction.

To check your current active MySQL connection do:


mysql

mysql> show processlist;
----+------+-----------------+---------------+---------+-------+-------+------------------+
| Id | User | Host            | db            | Command | Time  | State | Info             |
+----+------+-----------------+---------------+---------+-------+-------+------------------+
|  1 | root | localhost:49172 | myapp         | Sleep   |   441 |       | NULL             |
|  2 | root | localhost       | myapp         | Query   |     0 | NULL  | show processlist |        |
|  3 | root | localhost       | myapp_test    | Sleep   | 63703 |       | NULL             |             |
+----+------+-----------------+---------------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)

You can see here that process ID 3 for myapp_test has been dangling for a long time. We need to kill that process.


mysql> kill 3;