Avoiding Insert Locking With Insert Into

Ran into an issue where shared locks were being applied (for InnoDB) on a statement with this format:

insert into a select * from b;

While that statement is running, not only does a have some locks applied, but so does b! problem is… my statement was not exactly that simple. Instead, the select statement involved multiple tables, several sub queries, and more fun… The query was tuned to be as efficient as possible but still was running long enough that having locks on the tables selected from was unacceptable.

The solution? I’m getting around the issue by writing the results of the select to a flat file – then immediately turning around and loading it into the destination table.

select * from a into outfile a.txt;
load data infile a.txt into b;

Not too bad…

A few little catches:

* You can’t overwrite the flat file with select into outfile – maybe a good thing overall, but a bit annoying in my case. No prob though – just modified the script a bit to change the name in a predictable – yet not repeatable way.

* The user you’re running as (against mysql) will need to have file privileges which isn’t automatically given as part of the grant all statement that you probably set yourself up with immediately:
grant file ON *.* TO my_user@localhost;

* Obviously, your user will need to have access to the destination directory that you’re writing your flat file to. So, make sure wherever you’re headed – you’ve taken care of making it writeable for that user.

* I personally was a bit nervous about what would be used as a seperator if I didn’t ‘do it myself’, so I did. Very simple, you just need character(s) that you know will be unique. Here’s the csv example:

select * from a into outfile ‘a.txt’ fields terminated by ‘,’ lines terminated by ‘\n’;
load data infile ‘a.txt’ into table b fields terminated by ‘,’ lines terminated by ‘\n’;

* My table has an auto_increment id field on it. So, I don’t want to have it in my txt – I want it to actually auto increment! Again, nice and easy: Just provide a field list that doesn’t have it:
load data infile ‘a.txt’ into table b fields (field_1, field_2)

* and to combine the last two ideas:
load data infile ‘a.txt’ into table b fields terminated by ‘,’ lines terminated by ‘\n’ (field_1, field_2);

Not bad! No more locks and negligible runtime difference. The extra moving pieces are less than ideal, but it’s not a whole lot of extra complication.


Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s