How to improve INSERT INTO ... SELECT locking behavior

Question

In our production database, we ran the following pseudo-code SQL batch query running every hour:

INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true)

Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes).

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTable to another table and do the query there.

Answer 1

Probably you could use Create View command (see Create View Syntax). For example,

Create View temp as SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true

After that you could use your insert statement with this view. Something like this

INSERT INTO TemporaryTable (SELECT * FROM temp)

This is only my proposal.

Answer 2

Disclaimer: I'm not very experienced with databases, and I'm not sure if this idea is workable. Please correct me if it's not.

How about setting up a secondary equivalent table HighlyContentiousTableInInnoDb2, and creating AFTER INSERT etc. triggers in the first table which keep the new table updated with the same data. Now you should be able to lock HighlyContentiousTableInInnoDb2, and only slow down the triggers of the primary table, instead of all queries.

Potential problems:

• 2 x data stored
• Additional work for all inserts, updates and deletes
• Might not be transactionally sound

Answer 3

the reason for the lock (readlock) is to secure your reading transaction not to read "dirty" data a parallel transaction might be currently writing. most DBMS offer the setting that users can set and revoke read & write locks manually. this might be interresting for you if reading dirty data is not a problem in your case.

i think there is no secure way to read from a table without any locks in a DBS with multiple transactions.

but the following is some brainstorming: if space is no issue, you can think about running two instances of the same table. HighlyContentiousTableInInnoDb2 for your constantly read/write transaction and a HighlyContentiousTableInInnoDb2_shadow for your batched access. maybe you can fill the shadow table automated via trigger/routines inside your DBMS, which is faster and smarter that an aditional write transaction everywhere.

another idea is the question: do all transactions need to access the whole table? otherwise you could use views to lock only necessery colums. if the continous access and your batched access are disjoint regarding columns, it might be possible that they dont lock each other!

Answer 4

If you can allow some anomalies you can change ISOLATION LEVEL to the least strict one - READ UNCOMMITTED. But during this time someone is allowed to read from ur destination table. Or you can lock destination table manually (I assume mysql is giving this functionality?).

Or alternatively you can use READ COMMITTED, which should not lock source table also. But it also locks inserted rows in destination table till commit.

I would choose second one.

Answer 5

I'm not familiar with MySQL, but hopefully there is an equivalent to the transaction isolation levels Snapshot and Read committed snapshot in SQL Server. Using any of these should solve your problem.

Answer 6

The answer to this question is much easier now: - Use Row Based Replication and Read Committed isolation level.

The locking you were experiencing disappears.

Longer explaination: http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html