3

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?

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.

0

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 ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.