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

Question

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?

Answer 1

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.

Answer 2

The problem (probable) turned out to be that i didnt close a ResultSet, inside Java. The strange thing is that MySql was not informing me that someone was using that table at all! Let alone that I thought results sets are closed when the method they are in is terminated. apparently not ;)