MysqlDump: Lock wait timeout exceeded; try restarting transaction

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 😉

Getting Fancy with Mysqldump Tricks

Mysqldump tricks help you get the most out of this amazing and essential tool. It is typically used for backing up and migrating MySQL databases and data. But sometimes you may want to get a little fancier then just backing up or restoring a full database. Here are a few Mysqldump tricks to maximize your effectiveness.

Mysqldump Tricks – Dump a single MySQL table

If you want to dump a single table from a MySQL database you can. The following command will help you accomplish this amazing feat:

mysqldump [options] db_name table_name > filename.sql

And just like that you have a file with just that table exported into it. But what if you want to dump more than one table, but not the whole DB. The following command will help you get there:

mysqldump [options] db_name table1_name [table2_name table3_name ...] > filename.sql

Simply adding the table names with spaces between each name will add them to the export file.

Skip specific tables in a DB with Mysqldump

Now that we know how to export only specific tables from a DB. How can we use Mysqldump to export a full DB but exclude one or more tables? Use the following command for a single table:

mysqldump [options] db_name --ignore-table=db_name.table1_name > filename.sql

Or if you want to exclude multiple tables:

mysqldump [options] db_name --ignore-table=db_name.table1_name --ignore-table=db_name.table2_name --ignore-table=db_name.table3_name > filename.sql

Now your Mysqldump powers are increasing. Now let’s move on to something a bit different.

Restore a single table from a Mysqldump File

So you already have a Mysqldump file with multiple tables in it, or a full DB dump. But you only want to restore a single table from the file. Ok, so this isn’t exactly a Mysqldump trick, it’s a Sed trick, but just using a Mysqldump file.

Let’s say you have a Mysqldump file called “filename.sql” and you want to restore only the table named “myFavoriteTable”. Using the command below Sed will copy the correct table contents into the file “myFavoriteTable.sql” so you can restore that file/table individually.

sed -n -e '/CREATE TABLE.*`myFavoriteTable`/,/Table structure for table/p' filename.sql > myFavoriteTable.sql

Now you have several new Mysqldump tricks up your sleeve. Hopefully they save you time and effort in the future.

If you would like to learn how to use compression with mysqldump check out this post.