1

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!

ChrisYee90
  • 389
  • 1
  • 6
  • 12
  • 1
    Possible duplicate of [How to debug Lock wait timeout exceeded?](http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded) – xathien Oct 15 '15 at 15:09
  • thanks for the help, but i tried that and i still get the same error for some reason. – ChrisYee90 Oct 15 '15 at 15:14
  • Setting the lock wait timeout to a larger value is to help you debug what's causing the issue, not to solve the actual locking. Something in your code is causing this, and due to the myriad of ways it can happen, without knowing what your code is doing, we can't help. Start by checking for queries that will create locks on similar resources in parallel. – xathien Oct 15 '15 at 15:17
  • any idea where i can find those queries? thanks! – ChrisYee90 Oct 15 '15 at 15:32
  • You've set your lock wait timeout to 1000 seconds, so using `SHOW FULL PROCESSLIST` or a fancier tool like `innotop` (press Shift+T to see the Transaction view or Shift+Q for the Query view) can help find the stuff that's blocked. – xathien Oct 15 '15 at 15:36
  • I added the output from the show full processlist; – ChrisYee90 Oct 15 '15 at 15:45
  • It looks like you killed a very-long-running insert-select on a large table. `INSERT INTO ... SELECT FROM` will obviously lock writes to the table you're selecting from. Since you killed the query, it has to undo all its work, and that may take a long time depending on the number of rows. Your best bet now is a solid heap of patience. – xathien Oct 15 '15 at 15:51
  • cool thanks for the info! – ChrisYee90 Oct 15 '15 at 15:54
  • is there any way to speed up the process? maybe by making a new database and creating a newtable in it? – ChrisYee90 Oct 15 '15 at 15:59

1 Answers1

1

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.

Hitesh Mundra
  • 1,538
  • 1
  • 9
  • 13