MySQL Replication – Understanding ‘Waiting for Dependent Transaction to Commit’

MySQLreplication

Quite often the Coordinator thread on our replica database has the status "Waiting for dependent transaction to commit".

I'm a little confused as to what this means, and I can't see this status in the list of thread states in the MySQL documentation.

I'm mainly curious if the "dependent transaction" is a transaction currently running on one of the worker threads on the replica (as in a table is potentially locked), or if the "dependent transaction" is something occurring on the Master that the replica is waiting for?

Best Answer

If slave_parallel_workers is changed from default 0 value then a number of events applier threads will be launched. Independent events are applied in parallel. If some event is dependant on another one (say INSERT and then an UPDATE to the same row), an UPDATE event will be applied sequentially after the INSERT. Threads dependant on some other event becomes in "Waiting for dependent transaction to commit" state.

This is not a problem but rather a sign that some queries are long enough to create the trailing queue of dependant queries. Some optimization may be requiered but that is not mandatory.

Related Question