25

we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error Lock wait timeout exceeded; try restarting transaction which looks like never stop ending don't know what to do with this error to stop. why it is occurring all of sudden

27

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.

mysql> set innodb_lock_wait_timeout=100;

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

The transaction which is timeout, try to lock table which is hold by another process. and your timeout variable set with little number of second. so it shows error. You can see more status by the command.

SHOW ENGINE INNODB STATUS\G 

You can see list of locked tables by-

 show open tables where in_use>0;

Now see the thread which is using this table

  show full processlist;

now you can kill that thread or wait to complete it.

  • Let us continue this discussion in chat. – JAVAC May 8 '15 at 20:20
  • Thanks for this info, but how can one get to the root of the issue when locks keep on happening, even on very simple tables with small updates (like incrementing or updating a single field). – Wouter Sep 26 '18 at 8:43
-1

If using MySQL version less than 5.0 you cannot change the variable even for the session or global scope. The best solution is to capture the query and execute it after the traffic is slow.

  • 1
    OP says they're using 5.6, which (I assume) is 5.0 or greater. – RDFozz Jan 26 '18 at 21:30

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.