How do I report progress of a long running transaction in MySQL? I have this transaction in a stored procedure:

...
START TRANSACTION;

WHILE @Codes &gt; 0 DO

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();

END WHILE;

COMMIT;
...


This could be inserting anywhere up to 1M unique rows taking up to 10 minutes which is fine and dandy but I need a way to report back its progress.

I would normally add an if statement that would run a query to update a job table of its progress every 1000 inserts but because this is in a transaction the update wouldn't appear if queried externally until the entire transaction is finished which defeats the purpose of having a status as you would only see when the job started and finished.

I have even thought of committing every 1000 inserts so I could report progress that way but then I lose the ability to roll back all inserts if something goes wrong later, or if the job is canceled before it is finished.

So, is there a way to run an update query inside a transaction that doesn't become part of the transaction and is written to the table/row with immediate effect?

If not, do you have any workarounds?
There are some caveats that apply to mixing transactional and non-transactional tables in the same query, but in this case, you're actually doing it because the side effects are desirable... so I see no problem with the following suggestion:

The MEMORY storage engine seems like a good fit for this application.  It's non-transactional, so your "status" inserts/updates would be visible immediately to other threads.

Data stored in a MEMORY table persists until the table is truncated or the rows are deleted, or until the server is restarted.  In a replication environment, a restarted master will also direct the slaves to empty their copy of a memory table when the master restarts, since the master's table will start empty.  

Also, if you're doing this in a replication environment, a binlog_format of MIXED or ROW would likely be best, since those formats allow more deterministic replication, and determinism becomes more tricky when replicating a query with transactional and non-transactional tables.