Geek Answers Handbook

Exceeding MySQL Lockout Waiting

I have an error Lock wait timeout exceeded; try restarting transaction Lock wait timeout exceeded; try restarting transaction . What are the reasons for this and how to solve the problem? FYI: innodb_lock_wait_timeout = 100 in the MySQL configuration file.

+4
source share
2 answers

This is a lock conflict problem, which ultimately leads to a timeout on one of the locks. Here are some suggestions:

  • Make sure you have the correct indexes, as a result of which row-level locks are blocked, not table-level locks . This will reduce competition.
  • Make sure you have indexes in foreign keys . To check relational constraints during insert or update , some database locks the entire referenced table if there is no such index (I don't know if this applies to MySQL)
  • If the problem is still there, try to make the transaction faster / smaller. Again, this will reduce competition in the database.
  • Increase the wait time, but keep a reasonable value
+6
source

Does this happen in a system with a high level of traffic, where transactions take a lot of time (i.e. tables are locked for a long time)? If so, you can look at the transaction code to make them shorter / granular / more efficient.

0
source

More articles:


All Articles