Bug #51640 mysqldump locks all tables across all databases although it shouldn't
Submitted: 2 Mar 2010 12:54 Modified: 3 Mar 2010 8:12
Reporter: Björn Wiberg Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0.90 OS:IBM AIX (5.3 TL8 SP1 (5300-08-01-0819))
Assigned to: CPU Architecture:Any
Tags: mysqldump

[2 Mar 2010 12:54] Björn Wiberg
mysqldump issues a FLUSH TABLES WITH READ LOCK (global locking) SQL command although --lock-all-tables has not been specified. This makes other sessions "hang" during the dump.

We're using the 5.0.90 32-bit binary distribution from the MySQL website.

We're using a mix of MyISAM and InnoDB tables on our system so unfortunately we cannot use --single-transaction.

Specifying --skip-lock-all-tables or --databases db1 (insted of just specifying db1 as the database name) on the command line does not seem to help.

How to repeat:
# MySQL client options
pager=/usr/bin/less -S

# For MyISAM or MyISAM + InnoDB

Command line:
mysqldump largedb > out.sql

From a mysql client session, issue:
...which will show:

| 978412 | root        | localhost                        | NULL            | Query       |     29 | Flushing tables                                                | FLUSH TABLES WITH READ LOCK                   
| 978418 | uucms       | pica.its.uu.se:58315             | uucmsdb         | Query       |     28 | Waiting for release of readlock                                | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978419 | uucms       | pica.its.uu.se:58316             | uucmsdb         | Query       |     28 | Waiting for release of readlock                                | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978420 | uucms       | pica.its.uu.se:58317             | uucmsdb         | Query       |     28 | Waiting for release of readlock                                | REPLACE INTO pcms_pubsession (sid, data) VALUE
| 978421 | uucms       | pica.its.uu.se:58318             | uucmsdb         | Query       |     27 | Waiting for release of readlock                                | REPLACE INTO pcms_pubsession (sid, data) VALUE

...i.e. the global flushing with read lock and more and more sessions waiting for it (the dump) to complete.

The general query log shows:

                 965266 Connect     root@localhost on 
                 965266 Query       /*!40100 SET @@SQL_MODE='' */
                 965266 Query       /*!40103 SET TIME_ZONE='+00:00' */
                 965266 Query       FLUSH TABLES
                 965266 Query       FLUSH TABLES WITH READ LOCK
                 965266 Refresh    
                 965266 Query       SHOW MASTER STATUS
                 965266 Init DB     abmdb
                 965266 Query       SHOW CREATE DATABASE IF NOT EXISTS `abmdb`
                 965266 Query       show tables
                 965266 Query       show table status like 'PublikationerTypDef'

Running mysqldump --help shows:

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
all                               TRUE
all-databases                     FALSE
add-drop-database                 TRUE
add-drop-table                    TRUE
add-locks                         TRUE
allow-keywords                    TRUE
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   TRUE
compress                          TRUE
create-options                    TRUE
databases                         FALSE
debug-info                        FALSE
default-character-set             utf8
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
first-slave                       FALSE
flush-logs                        TRUE
flush-privileges                  TRUE
force                             TRUE
hex-blob                          FALSE
host                              (No default value)
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
master-data                       2
max_allowed_packet                25165824
net_buffer_length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              0
quick                             TRUE
quote-names                       TRUE
routines                          TRUE
set-charset                       TRUE
single-transaction                FALSE
dump-date                         TRUE
socket                            (No default value)
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              root
verbose                           FALSE
where                             (No default value)

Suggested fix:
If --lock-tables is specified, but not --lock-all-tables (-x), mysqldump should not lock all tables across all databases but instead only all tables in the selected or affected database(s), as specified by the database name or database name(s) following --databases on the command line.

(I leave the decision whether or not --databases db1 db2 should lock all tables in both db1 and db2 during the full duration of the dump, or first all tables in db1 and then all tables in db2, to you.)
[3 Mar 2010 8:12] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have option master-data specified in the configuration file. And according to http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html:

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified...