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

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 SELECT a 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 '...';  

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.

Replay

Category: mysql Time: 2016-07-29 Views: 4

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.147 (s). 12 q(s)