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)
