MySQL Lock wait timeout exceeded

Question

I have got the error 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 MySQL config file.

Answer 1

Is this happening on a high-trafficked system where transactions take a long time (i.e. tables are locked for a long time)? If so, you might want to look into your transaction code to make them shorter / more granular / more performant.

Answer 2

This is problem of lock contention, which ultimately result in a time-out on one of the lock. Here are a few suggestions:

• Make sure you have the correct indexes which result in row-level locks not table-level lock. This will reduce the contention.
• Make sure you have indexes on the foreign key constraints. To check the relational constraints during insert or update, some database lock the whole referenced table if there is no such index (don't know if this is the case of MySQL)
• If problem is still here, try to make the transaction faster/smaller. Again, this will reduce the contention on the database.
• Increase the timeout but keep the value reasonable