Behavior of transactions on multiple connections

What you see is the correct behaviour for the default isolation setting (REPEATABLE READ) and even for lower (READ COMMITTED) setting. More details can be found in MariaDB and MySQL docs:

• SET TRANSACTION
• Transaction Isolation Levels
• Consistent Nonlocking Reads

which basically sums up to:

What one transaction is writing, other transactions should not be able to read. Not before the one transaction commits.

When a transaction is writing something to the database, there is no guarantee that the write will persist. The transaction may get an error and all of the writes it has done be rolled back. So, what you observe

However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

is correct from that point of view. There is nothing to be seen from the outside, progress is 0, until something is committed. That's what the Isolation property of transactions is all about.

---

I see 2 solutions to the issue:

• A. use the "worker", transaction 1 for progress report, as well. Who's better to know how much its work has progressed, than the worker itself?

• B. In the 2nd, "progress report" connection, use the lowest isolation setting (READ UNCOMMITTED):

  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
  

  Now, the transactions from this connection will be able to do "dirty reads", i.e. see uncommitted writes from the 1st transaction and will report correctly the (uncommitted) progress.