HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Do I need --skip-lock-tables with --single-transaction?

Submitted by: @import:stackexchange-dba··
0
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 --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.sql


If 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 --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.