CODEWITHSUNDEEP
mysql
ChrisYee90
ChrisYee90

Reputation: 429

Lock wait timeout exceeded in MySQL

I get the following error:

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

I did the 

show processlist;

command and killed all the processes and well run the command

+------+------+-----------+-------------------+---------+------+-----------+---------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------+-------------------+---------+------+-----------+---------------------------------------------------------------------------------+ | 1749 | cyee | localhost | CY_Test | Killed | 2308 | query end | INSERT INTO CY_Test.patient2010 SELECT * FROM cmsdata-outpatient-2010.patient | | 1755 | cyee | localhost | cmsdata-inpatient | Query | 0 | NULL | SHOW FULL PROCESSLIST | +------+------+-----------+-------------------+---------+------+-----------+---------------------------------------------------------------------------------+

set innodb_lock_wait_timeout=1000;

that another site suggested, but i still get the same error. Any help would be appreciated, thanks!

Upvotes: 2

Views: 3010

Answers (1)

Hitesh Mundra
Hitesh Mundra

Reputation: 1588

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.

mysql> set innodb_lock_wait_timeout=100

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

The transaction which is timeout, try to lock table which is hold by another process. and your timeout variable set with little number of second. so it shows error. You can see more status by the command.

SHOW ENGINE INNODB STATUS\G

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.

Upvotes: 2

Related Questions