You are here because you got the following error while performing a MysqlDump: Lock wait timeout exceeded; try restarting transaction. What does this error mean and how can you get MysqlDump working again? You have come to the right place to get your answer.
Transactions
The error indicates that the mysqldump timed out while waiting for a transaction to complete. This error shows up when dumping from Innodb tables which support transactions. Transactions are the process by which the database ensures that all the data is properly modified in a table before it moves on to the next data change.
Think of a transaction in the database just like checking out at a grocery store. When you approach the cash register there is typically a line of other customers waiting to check out. The Cashier takes each customer in order and helps them purchase their items. When they have finished their purchase their transaction is complete. And the next customer in line is able to start their transaction.
So what is the Lock Wait Timeout
So when you get the error from MysqlDump: Lock wait timeout exceeded; try restarting transaction, this is why. MysqlDump is attempting to dump the information in the database/table. But to ensure the data is accurate it is attempting to lock the table during the process. This lock stops new transactions from starting and ensures that all previous transactions are complete.
If the lock attempt is timing out, that typically indicates that either the queue for changes was too long. Or a transaction took too long to complete. In either case the MysqlDump process was unable to get a complete copy of all the data. So the process errors out with “Lock wait timeout exceeded; try restarting transaction” and fails.
This error can be especially common when using MysqlDump in a cluster environment. Or on a very busy server with lots of transactions queued up.
How do I avoid Lock wait timeout exceeded errors?
The answer is a simple MysqlDump option that avoids the lock wait process altogether. I have previously briefly described the process in this post about how to Dump All MySQL Databases into Individual SQL Files. Use this MysqlDump option to avoid those errors.
--single-transaction
This simple command option tells MysqlDump to not wait for any table locks. Instead it will choose the most recent completed transaction for the table and export the data at that point. So you may not have the most 100% accurate data. Since transactions are still processing in the database. But you will have data that is 99.999999% accurate, just missing those latest changes.
This is super helpful in my environment taking periodic dumps of my databases at regular intervals. It is much more important that the data is backed up regularly than I have every change up to that second. If there was something changing at the time of the backup it will be changed in the next one. And if the MysqlDump process is failing because of too many changes, then I get no backup at all.
Good luck with your next MysqlDump process. Hopefully you get a good backup and avoid all the lock timeout errors 😉