Mysql – INSERT INTO a large amount of data with many clients running SELECT

blockinginsertlockingMySQLmysql-5.6

I need to run a query like this to insert a large amount of data (millions of rows) into a MySQL table (InnoDB, 5.6.10):

INSERT INTO target_table (a)
SELECT id FROM source_table
ON DUPLICATE KEY a=a;

This usually takes around 200 seconds to run when no clients are accessing target_table.

target_table now has around 20 clients 'polling' to see if data has arrived, so we now have a lot of clients performing queries like this one:

SELECT a FROM target_table WHERE a LIKE '0%';

I'm having trouble with the INSERT INTO statement now that these clients are polling – it fails with the error Lock wait timeout exceeded; try restarting transaction.

I'm a bit confused as to why whatever lock is required for INSERT INTO cannot be acquired because clients are running SELECT statements.

EDIT:

The CREATE statements for these tables are as follows:

CREATE TABLE `target_table` (
  `a` varchar(32) NOT NULL,
  PRIMARY KEY (`a`),
  CONSTRAINT `target_table_source_table` FOREIGN KEY (`a`) REFERENCES `source_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `source_table` (
  `id` varchar(32) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Best Answer

In source_table, are there often duplicate values? I assume a is the PRIMARY KEY of dest_table? If there are often dups, change to

INSERT INTO target_table
    SELECT DISTINCT a FROM source_table
ON DUPLICATE KEY a=a;

DISTINCT moves work into the SELECT, thereby lessening the conflicts over the INSERT.

Better yet, do this:

INSERT IGNORE INTO target_table
    SELECT DISTINCT a FROM source_table;

IGNORE is probably less work than IODKU.

Please provide SHOW CREATE TABLE for both tables; there could be subtle issues, especially with the PRIMARY KEY, timestamps, and secondary indexes.

Related Question