0

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;
  • Is there a leading wildcard on the LIKE string? – Rick James Jul 30 '16 at 5:20
  • @rick-james No, no leading wildcard, but a trailing wildcard. – joelittlejohn Jul 30 '16 at 9:01
  • If you have a key starting with a, then that should be ok. – Rick James Jul 30 '16 at 14:50
1

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.

  • I originally chose the ON DUPLICATE approach to avoid other errors being ignored. Could you explain how switching to IGNORE would help? I've added the CREATE statements to the original post. Really appreciate your input, thanks! – joelittlejohn Jul 30 '16 at 19:10
  • I modified my answer. – Rick James Jul 30 '16 at 22:50
  • No duplicate value in source_table (we're selecting only the PK), the ON DUPLICATE was there just in case target_table already contained the value. – joelittlejohn Aug 1 '16 at 9:21
  • Switching from ON DUPLICATE KEY to INSERT IGNORE seems to have solved the problem, thanks! – joelittlejohn Aug 1 '16 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.