Search code examples
mysqlmariadbinnodb

Preventing lock wait timeouts with INSERT ... SELECT

I have an InnoDB books table for my app. A few times a day a very large number of independent INSERT and UPDATE queries run against the books table.

Incoming requests to the app also generate a temporary table based on books using an INSERT INTO [tmp table] (SELECT FROM books ...).

According to the MySQL docs on INSERT ... SELECT, this syntax locks on books until the temp table has been populated.

The combnination of locks from these selects, inserts, and updates on the books table periodically overwhelms MySQL, causing lock wait timeouts.

I've tried a suggestion from this percona article dumping the SELECT from books into an outfile, doing the INSERT as a separate step. This avoids the select locks, but is unreasonably slow loading.

Am I missing an alternative to the INSERT ... SELECT that will not lock the books table without sacrificing performance?

Solution

  • I'm a MySQL noob and probably understood the problem incorrectly but

    INSERT INTO [tmp table] (SELECT FROM books ...)

    should not set any locks to the books table but only to the tmp_table unless your transaction isolation level is SERIALIZABLE.

    UPDATEs could be run on READ COMMITTED isolation level so that InnoDB does not need to hold locks on every row to the end of the transaction (although this works only if the WHERE part consists of indexed columns only).