“Error Code: 1205. Lock wait timeout exceeded; try restarting transaction” on delete event

2
votes

I'm trying to delete a row in my table 

delete from tbllink where linkid=243

but i keep getting that error message:

Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

As mentioned in other questions, I tried running : 

show open tables where in_use>0;

This returned 0 results. I also tried running: 

show processlist

This returned these columns:

> 2244  username IP:50487   newdatabasetemp Sleep   1777        
> 2247  username IP:50723   newdatabasetemp Sleep   1346        
> 2249  username IP:50725   newdatabasetemp Sleep   1285

I tried reconnecting to the server but still the same result.

2
mysqltimeout

2 Answers

2
votes

I was getting same error when I was deleting all records from a table.

Except me nobody was using server so I went ahead killed all running processes one by one using kill <process id> and it worked.

1
votes

Few points to consider :

  1. innodb_buffer_pool_size should be set to about 70% of available RAM.

  2. You can execute :

    SET GLOBAL innodb_lock_wait_timeout = 5000;

And then this:

SET innodb_lock_wait_timeout = 5000;
  1. Can you kill all the processes related to mysql and restart the server and try again?