update single value in mysql is taking to long -> timeout

Question

this has been asked several times, but I can't find any solution to my problem.

I have a database with a table called 'DataPoint'. It has 513 rows.

mysql> describe DataPoint;
+-----------+------------+------+-----+-------------------+----------------+
| Field     | Type       | Null | Key | Default           | Extra          |
+-----------+------------+------+-----+-------------------+----------------+
| id        | int(11)    | NO   | PRI | NULL              | auto_increment |
| value     | double     | YES  |     | NULL              |                |
| timestamp | timestamp  | NO   |     | CURRENT_TIMESTAMP |                |
| sensorID  | char(12)   | YES  |     | NULL              |                |
| parseSync | tinyint(1) | YES  |     | NULL              |                |
+-----------+------------+------+-----+-------------------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT * from DataPoint where id=1;
+----+-------+---------------------+------------+-----------+
| id | value | timestamp           | sensorID   | parseSync |
+----+-------+---------------------+------------+-----------+
|  1 | -5.88 | 2015-07-08 22:01:34 | wsuH8FMVwO |         0 |
+----+-------+---------------------+------------+-----------+
1 row in set (0.01 sec)

mysql> update DataPoint SET parseSync = 1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The problem is with the last command 'update DataPoint SET parseSync = 1 where id=1;'. The database won't answer in 50 sec and then throws a timeout.

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

I don't see any error in my syntax, but i'm not an expert in mysql... am I missing something trivial?

thanks for your help.

Answer 1

Its not always your query that causing issue. Sometime its other query holding lock on particular table and went into unreachable state. I'd suggest you to follow below steps to find out Culprit query.

Step 1: Execute SHOW ENGINE INNODB STATUS; in MySQL. It'll show query running on table and locked resource.

Step 2: Now kill that process. Find out process id of that query using SHOW PROCESSLIST; and kill that process using KILL process_id;

Step 3: Find out solution for that Culprit query.

OR

Show us that Culprit query and We'll try to help you.

Answer 2

So I came up with a fairly simple solution. I found an entry in information_schema.INNODB_TRX as @Vatev mentioned, so I just restarted the service daemon with

sudo service mysql restart

after that, everything was fine.

thanks