2

I have a database that I am trying to update a row value in but I am getting a "lock wait timeout" error. This is what the table looks like:

mysql> use rti;
Database changed
mysql> explain resin_products;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| resin_product_id   | int(11)      | NO   | PRI | NULL    |       |
| manufacturer       | varchar(45)  | YES  |     | NULL    |       |
| name               | varchar(45)  | YES  |     | NULL    |       |
| model_number       | varchar(45)  | YES  |     | NULL    |       |
| resin_type         | varchar(45)  | YES  |     | NULL    |       |
| primary_type       | varchar(45)  | YES  |     | NULL    |       |
| group              | varchar(45)  | YES  |     | NULL    |       |
| teir               | varchar(45)  | YES  |     | NULL    |       |
| chemical_structure | varchar(45)  | YES  |     | NULL    |       |
| physical_structure | varchar(45)  | YES  |     | NULL    |       |
| color              | varchar(45)  | YES  |     | NULL    |       |
| total_capacity     | varchar(45)  | YES  |     | NULL    |       |
| salt_split_CAP     | varchar(45)  | YES  |     | NULL    |       |
| price_per_cuft     | varchar(45)  | YES  |     | NULL    |       |
| comments           | varchar(500) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

Here is the command:

mysql> UPDATE resin_products SET price_per_cuft='00.04' WHERE resin_product_id=1;

This is the error that occurs after about 30 seconds:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is what my "lock_wait" variable is set to:

mysql> show variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)
Hooplator15
  • 1,540
  • 7
  • 31
  • 58
  • Possible dublicate of [Getting “Lock wait timeout exceeded; try restarting transaction” even though I'm not using a transaction](http://stackoverflow.com/q/5836623/4275342) – Roman Marusyk Sep 14 '15 at 20:59
  • or [Fixing “Lock wait timeout exceeded; try restarting transaction” for a 'stuck" Mysql table?](http://stackoverflow.com/q/2766785/4275342) – Roman Marusyk Sep 14 '15 at 21:02
  • 1
    look the result from > SHOW ENGINE INNODB STATUS; – Bernd Buffen Sep 14 '15 at 21:03

1 Answers1

2

First see the list of locked tables:

show open tables where in_use>0;

Also you can see the thread which is using this table:

show full processlist;

And then you can kill that thread 

kill 141;
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
  • I am not getting any open tables issue. This is what I am getting: mysql> show open tables where in_use>0; Empty set (0.00 sec) I will try and run the command that produces the error again and then immediately follow it with show open tables where in_use>0; – Hooplator15 Sep 14 '15 at 21:03
  • You can see more status by the command: `SHOW ENGINE INNODB STATUS\G` – Roman Marusyk Sep 14 '15 at 21:04
  • 1
    It turned out to be a process that was running for too long. I ran `SHOW FULL PROCESSLIST;` and then killed all of them. That solved the issue. – Hooplator15 Sep 15 '15 at 19:33