I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the same table within a time interval (less than 30 seconds). The write processes sometimes experience a timeout cause they find the table locked (for more than 30 seconds) by the read operations.
Is there a way to prevent this behavior and allow the write processes to write even if other processes are reading?
The main cure for such is to speed up the reads. Let's see some of them, together with
SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a
GROUP BY, then an excellent solution is to build and maintain Summary Table(s).