5

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:

  1. Connection 1: start transaction
  2. Connection 1: add and update rows
  3. Connection 2: update progress
  4. 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?

  • Probably even a different DB would not help as there is probably something wrong with your setup. If it were a locking issue, your script would deadlock itself (unless it uses multiple threads) so maybe transaction management is off. Is the "progress" connection using autocommit mode or explicit transactions? – jkavalik Jun 10 '16 at 20:23
3

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:

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.

  • Interesting, I hadn't heard of "READ UNCOMMITTED". Sounds promising. I'll give that a shot and report back. As a side note: I'm similarly considering converting the 'job progress' table to MyISAM to ensure that updates to it are automatically committed outside the context of a transaction to my InnoDB tables. – Matt James Jun 10 '16 at 23:35
  • 1
    @MattJames yes, a MyISAM table might work, too. – ypercubeᵀᴹ Jun 10 '16 at 23:39
  • Hmm, in my testing, using your "SET SESSION" query above did not make it so that my "progress-reading" connection could see updates to this record. I'm not quire sure why and sadly, won't have time to really debug it further. The solution for me was to switch the tables to MyISAM, which I'll be adding as another answer. – Matt James Jun 13 '16 at 13:05
  • 1
    The difference between SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; and SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; is explained in the links. – ypercubeᵀᴹ Jun 13 '16 at 13:39
0

None of the other suggested solutions appeared to work for me. Instead, I ended up switching the "job progress" table over to MyISAM from InnoDB. Since MyISAM does not support transactions, the records are written to the table right away even during a transaction.

In the future, I'd love an alternative way to keep all tables in InnoDB for consistency, but this was the only solution I found that was working for me.

0

You may have one giant transaction for Connection 2.

"3. Connection 2: update progress" should be in a transaction unto itself. Simplest is to not use BEGIN in Connection 2 and have autocommit=0.

That makes Connection 2 pretty much the same as MyISAM.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.