2

I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the database from my dump file while also utilizing transactions. That is I have no idea how to make the dump file in such a way that it's execution will restore the dumped databases in a single transaction, without issuing any locks.

Anyone encountered such problem?

2

--single-transaction is used to ensure the dump is consistent when dumping, it has nothing to do with import/restore.

Loading a dump is not generally possible in a single transaction because many CREATE statements do an implicit commit.

It is often not even desirable because for a rollback to be possible, the transaction needs to keep undo logs and if you import many rows over many tables, the logs will grow too much, eat memory and disk space and slow down the import and all other operations.

If your issue is import speed, check that extended inserts are being used in the dump (that each insert statement inserts multiple rows at once) and you have proper buffer pool and log size configured for InnoDB.

If you want to import the data without issuing any locks as stated in your question then I am afraid that's not possible, because DML (inserts, updates, deletes) just have to lock to keep things ACID.

You should not restore from a dump a database of any service while it is running and restoring a specific database to a server instance hosting other databases should not issue any locks hindering these other services.

  • Thanks for the detailed response. Can you clarify one more thing: is it advised to always --add-locks into mysqldump then? – cubrman May 31 '16 at 8:27
  • @cubrman I don't think so. LOCK TABLES is mostly MyISAM thing and is mentioned in the manual for MyISAM insert speed, but not in the InnoDB equivalent page. – jkavalik May 31 '16 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.