mysqldump, myisam, and read local

by

When mysqldump is run with –lock-tables against a database using MyISAM tables, the documentation states that each table is locked with READ LOCAL. As I understand it, READ LOCAL allows for concurrent inserts from other sessions.

I have a few questions regarding this.

With READ LOCAL, the only operations not available to other sessions are TRUNCATE, UPDATE, and DELETE. INSERTS and SELECTS will process normally on a table locked with READ LOCAL from other sessions, provided there are no ‘holes’ in the table. Is this a correct assumption?

In the case of making backups with mysqldump, since concurrent inserts are allowed and tables are locked sequentially (multiple table backups) then what is preventing relational tables from becoming inconsistent? Will the table dump contain data that was possibly added after the LOCK TABLE was issued?

Thank you for your time.

Answer

I think your assumption is correct. I just tested to confirm, and READ LOCAL allows for concurrent INSERTs from other sessions that won’t be visible to the locking session until it calls UNLOCK TABLE. UPDATE, TRUNCATE, and DELETE are blocked while the READ LOCAL lock is held. I didn’t test for the holes condition.

Regarding referential integrity, mysqldump’s –lock-tables option (which is on by default as of 5.1, not sure about earlier versions) locks all of the tables in the database at once with a single LOCK TABLES statement before dumping the data, so it should not be possible for it to take an inconsistent view of the data. You can confirm this by turning on the general query log and running mysqldump.

However, note this in the man page:

      Please note that when dumping multiple databases, --lock-tables locks tables for each database
      separately. Therefore, this option does not guarantee that the tables in the dump file are logically
      consistent between databases. Tables in different databases may be dumped in completely different
      states.

Also, I know you probably realize this, but MyISAM might not be the right storage engine if you are concerned about referential integrity.

Here is the simple code I used to confirm your assumption:

SESSION ONE

create database testing;
use testing;
create table t1 (a int not null, primary key(a));
insert into t1 values (1),(2),(3),(4),(5),(6);
select * from t1;
lock tables t1 read local;

SESSION TWO

use testing;
insert into t1 values (7);
-- Shows row with a=7
select * from t1;

SESSION ONE

-- Does not show row with a=7
select * from t1;

SESSION TWO

   -- Blocked
   update t1 set a=8 where a=2;

Hope that helps.

Attribution
Source : Link , Question Author : jr0d , Answer Author : jlupolt

Leave a Comment