Thesql ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction

MySQLoptimization

This is a replica of my StackOverflow question

I am trying to update a row on a specific table of my database.

UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30';

and i am getting the error

ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction  
SQL Statement: UPDATE `Search`.`credentials` SET `status`='0' WHERE `id`='30'

I have set the timeout to 200

show variables like 'innodb_lock_wait_timeout';

+———————————-+——-+
| Variable_name                   | Value|
+———————————-+——-+
| innodb_lock_wait_timeout | 50     |
+———————————-+——-+

The database is been used, but not all the time, and not the table I want to update

mysql> show open tables where in_use>0;

+———–+—————————+——–+——————–+
|Database| Table
                       
| In_use | Name_locked |
+———–+—————————+——–+——————–+
| db1        | PayInformation
         |2
        |
0                      
|
| db1        | KeyMapping             
| 4        |
0                      
|
| db1        | NoticeInformation
    | 2
        |
0                      
|
| Search  |
companies               
| 1         |
0                      
|
| db1        |
ContractInformation | 5
        |
0                      
|
+———-+—————————+——–+———————+

some times there are more lines concerning the Search database, but none that is using the same table that i need to update.

and from the process list i get

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where db="afmSearch";

+—-+——+————-+——–+———+——+——-+——+
|  ID  |  USER  |  HOST     
   |  DB          |
 COMMAND  |  TIME  |  STATE  |
 INFO  |
+—-+——+————-+——–+———+——+——-+——+
|  xx  |  me      |  my.ip:44604
 |  Search  |  Sleep      
 |        1  |      
       |  NULL  |
+—-+——+————-+——–+———+——+——-+——+

Any ideas?

Best Answer

Some other connection was running something really long - probably longer than 50 seconds.

Possibly another connection is in autocommit=0 mode, but just sitting there, forgetting to do a COMMIT.

If you can, get the PROCESSLIST before it times out. Also do SHOW ENGINE=InnoDB STATUS;

Provide SHOW CREATE TABLE credentials. Look for other writes to that table, and check for index usage.

Related Question