11

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?

8

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

  • 3
    why not 140' or 2000'? how this could actually solve the issue? by hiding it? – Elzo Valugi Apr 10 '17 at 14:39
  • 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
7

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.

4

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.

2

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

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.