read-access to a MyISAM table during a long INSERT?

Question

On mysql and using only myisam tables, I need to access the contents of a table during the course of a long-running INSERT.

Is there a way to prevent the INSERT from locking the table in a way that keeps a concurrent SELECT from running?

This is what I am driving at: to inspect how many records have been inserted up to now. Unfortunately WITH (NOLOCK) does not work on mysql and I could only find commands that control the transaction locks (eg, setting the transaction isolation level to READ UNCOMMITTED) -- which, from my understanding, should not apply to myisam tables at all since they don't support transactions in the first place.

Answer 1

MyISAM locking will block selects. Is there a reason for using MyISAM above InnoDB? If you don't want to change your engine, I suspect this might be a solution for you:

1: Create a materialized view of the table using a cron job (or other scheduled task) that your application can query without blocking.

2: Use a trigger to count up the number of inserts that have occurred, and look up the number of inserts using this meta-data table.