MySQL Lock wait timeout exceeded
locking, mysql, timeout
Solution
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
Problem
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.