Behavior of transactions on multiple connections 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?
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.