I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with updating a table in the same database (but outside the scope of the transaction's work) to track the progress of the transaction.
All of this is happening in the same script, but again, with separate connections: one for the actual job being performed (that includes a transaction), and another for keeping meta info on the progress of the job.
I expected that this would work such that the job tracking record would update as the job progressed - 10%, 20%, 50%, etc because the transaction work is being done on a separate connection and impacting other tables. However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.
As far as I can tell, it doesn't deadlock because the "progress monitoring" connection isn't using a transaction at all. The "worker" connection wraps its queries in a transaction, but the other connection doesn't.
I check progress by just looking at a 'percent complete' field I fill in as the work progresses. Order of operations is:
- Connection 1: start transaction
- Connection 1: add and update rows
- Connection 2: update progress
- Connection 1: add and update more rows.
Then, in another HTTP connection I'm polling and outputting the progress value, but it never updates until the transaction on connection 1 is committed.
Is there any way to get the behavior I want, outside of moving the job-tracking table into an entirely different database?