ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (MYSQL)

mysql> delete from users;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)


[mysqld]
innodb_lock_wait_timeout=120
and restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120;
You could also just set it for the duration of your session

SET innodb_lock_wait_timeout = 120;

OR


You can see list of locked tables by-

 show open tables where in_use>0;
Now see the thread which is using this table

  show full processlist;
now you can kill that thread or wait to complete it.

  show full processlist;



+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+
| Id   | User       | Host                                        | db         | Command | Time | State | Info                  |
+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+
| 8970 | idba | localhost                               | honnikery| Sleep   |  107 |       | NULL                  |
| 8971 | idba | localhost                              | honnikery| Sleep   |  195 |       | NULL                  |
| 9052 | idba | localhost                                   | honnikery| Sleep   | 4027 |       | NULL                  |
| 9054 | idba |localhost                                  | honnikery| Sleep   | 2134 |       | NULL                  |
| 9055 | idba | localhost                                 | honnikery| Sleep   | 2165 |       | NULL                  |
| 9059 | idba | localhost                                   | honnikery| Query   |    0 | init  | show full processlist |
+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+


now you can kill that thread or wait to complete it.

mysql> kill 9052;
Query OK, 0 rows affected (0.00 sec)