patternsqlModerate
Do I need --skip-lock-tables with --single-transaction?
Viewed 0 times
tablesneedwithskipsingletransactionlock
Problem
I am performing regular backups using mysqldump on a schema consisting entirely of InnoDB tables. The mysqldump documentation has this to say about
The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
Yet approx 1 bazillion blog posts and answers here (and elsewhere on the stack exchange network) recommend:
If the two options are mutually exclusive, I would think that specifying
This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
Which leads me to believe that
Do I need to specify
--single-transaction:The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
Yet approx 1 bazillion blog posts and answers here (and elsewhere on the stack exchange network) recommend:
mysqldump --single-transaction --skip-lock-tables my_database > my_database.sqlIf the two options are mutually exclusive, I would think that specifying
--single-transaction should be enough. However the docs also state this about --opt:This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
Which leads me to believe that
--lock-tables is on by default.Do I need to specify
--skip-lock-tables along with --single-transaction or is merely setting the latter enough to ensure tables are not locked during a dump?Solution
If you use the
https://github.com/mysql/mysql-server/blob/8.0/client/mysqldump.cc#L1076
--single-transaction option, it turns off --lock-tables. That's what the documentation means when it says they are mutually exclusive.https://github.com/mysql/mysql-server/blob/8.0/client/mysqldump.cc#L1076
if (opt_single_transaction || opt_lock_all_tables) lock_tables = false;Code Snippets
if (opt_single_transaction || opt_lock_all_tables) lock_tables = false;Context
StackExchange Database Administrators Q#300259, answer score: 11
Revisions (0)
No revisions yet.