Description:
Could I get clarification on the locking issues related to using mysqldump with a database that has active inserts occurring at the time of the dump operation.
First some information on our environment.
1. We are using mostly INNODB tables
2. We rarely update data in the INNODB tables, we are only inserting into them with bulk loading operations (LOAD DATA)
3. We take a dump of the entire database at one time by issuing the command:
a. Mysqldump dbname > somefile.sql
4. We run in autocommit=1 mode
5. We set transaction_isolation='READ-UNCOMMITTED'
We do not use any options with the mysqldump command so my understanding is that we will get the set of options provided with ‘--opt' by default. Included in this set of options are the following which effect locking:
----------------------------------
--add locks:
Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded.
--lock tables:
Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.
----------------------------------
‘add locks’ appears to only impact the re-import of the data so is probably not applicable to my question. ‘lock tables’ does appear to impact the dump operation. My first reaction when reading the description of this would be to assume that all tables are locked during the dump operation and that concurrent updates into the INNODB tables would be locked out.
But reading the documentation regarding LOCK TABLES, I came upon the following comments:
Note the following regarding the use of LOCK TABLES with transactional tables:
• LOCK TABLES is not transaction-safe and implicitly commits any active transactions before attempting to lock the tables. Also, beginning a transaction (for example, with START TRANSACTION) implicitly performs an UNLOCK TABLES. (See Section 13.4.3, “Statements That Cause an Implicit Commit”.)
• The correct way to use LOCK TABLES with transactional tables, such as InnoDB tables, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. Note that we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1, to help old applications avoid unnecessary deadlocks.
This 2nd bullet implies that with INNODB, that the LOCK TABLES will NOT be issued for INNODB tables if we are running in AUTOCOMMIT=1 mode. Does mysqldump change the setting of autocommit during the dump operation? Does this would mean that concurrent inserts would NOT be locked out during the mysqldump?
Could you please clarify what behavior we should expect.
Also could you please comment on if we would be better using ‘--single-transaction’ as a mysqldump option.
Thanks
Lorraine Gray
How to repeat:
Not applicable
