mysql ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction 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            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;| Value|
  +----------------------------------+-------+ 
  | innodb_lock_wait_timeout | 50  &nbsp;&nbsp;&nbsp;  |
  +----------------------------------+-------+


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

mysql&gt; show open tables where in_use&gt;0;


  +-----------+---------------------------+--------+--------------------+ |Database| Table
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  | In_use | Name_locked |
  +-----------+---------------------------+--------+--------------------+ | db1  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  | PayInformation 
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|2
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
  0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  | | db1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   | KeyMapping &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  |      4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
  0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  | | db1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   | NoticeInformation
  &nbsp;&nbsp;&nbsp;&nbsp;|      2
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
  0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  | | Search&nbsp; |
  companies&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  |      1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
  0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  | | db1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   |
  ContractInformation |      5
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
  0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  |
  +----------+---------------------------+--------+---------------------+


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&gt; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where db="afmSearch";



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


Any ideas?
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.