14

I get this error repeatedly daily 4 - 5 times.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

This time I restart my phpmyadmin but it happens again and again. I am not able to point out this problem.

I am using Magento ver. 1.9.0.1 in website. I have 7000+ products. In Server I have 16GB RAM.

How to fix this issue?

Mohit Kumar Arora
  • 9,951
  • 7
  • 27
  • 55
Magento 2
  • 3,834
  • 7
  • 52
  • 107

4 Answers4

12

You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec

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

You can set it to higher value in /etc/my.cnf permanently with this line

[mysqld]
innodb_lock_wait_timeout=120

and restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120;

You could also just set it for the duration of your session

SET innodb_lock_wait_timeout = 120;

followed by your query

Arunendra
  • 7,446
  • 3
  • 29
  • 54
  • 5
    why not 140' or 2000'? how this could actually solve the issue? by hiding it? – Elzo Valugi Apr 10 '17 at 14:39
  • 1
    you have a problem that causes tables to be locked more than reasonably expected. If you do this you force yourself to wait 2 minutes everytime instead of fixing the issue once. And as Elzo says...what happens when 120 is not enough? – Carlos Robles Apr 27 '18 at 18:28
9

The reason behind this issue is if you are running a lot of custom scripts and killing the scripts before the database connection gets chance to close.

Something is blocking the execution of the query. Most likely another query updating, inserting or deleting from one of the tables in your query. You have to find out what that is:

If you can login to MySQL from CLI and run the following command

SHOW PROCESSLIST;

Once you locate the blocking/sleep process, find it's id and run : 

KILL {id};

Re-run your initial query.

May be this link will help you : SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction Magento.

Rohit Kundale
  • 3,492
  • 1
  • 21
  • 28
8

You will see this message if database contents you are trying to access has been locked by a (typically long running) previous process. MySQL will wait a certain amount of time for the lock to be removed before it gives up and throws that error. If you are able to track when you are seeing these error messages down to any consistent time of the day, you should look at what else the server is doing at that time - for instance is a database backup running. By doing this you should be able to narrow down possibilities for what processes could be creating the lock although it's not always that straight forward to do - likely to be a bit of trial and error.

Jonathan Hussey
  • 4,990
  • 1
  • 16
  • 25
2

increase execution-time in you php.ini at server OR ask to hosting provider.

Surya prakash Patel
  • 202
  • 1
  • 2
  • 9