Root cause :
In general temporary tables are not safe for parallel replication. Please find the following comments from
Function: bool THD::open_temporary_table(TABLE_LIST *tl)
/*
Temporary tables are not safe for parallel replication. They were
designed to be visible to one thread only, so have no table locking.
Thus there is no protection against two conflicting transactions
committing in parallel and things like that.
So for now, anything that uses temporary tables will be serialised
with anything before it, when using parallel replication.
*/
if (rgi_slave &&
rgi_slave->is_parallel_exec &&
find_temporary_table(tl) &&
wait_for_prior_commit())
DBUG_RETURN(true);
/*
First check if there is a reusable open table available in the
open table list.
*/
if (find_and_use_tmp_table(tl, &table))
{
DBUG_RETURN(true); /* Error */
}
/*
No reusable table was found. We will have to open a new instance.
*/
if (!table && (share= find_tmp_table_share(tl)))
{
table= open_temporary_table(share, tl->get_table_name(), true);
}
When ever there a temporary tables in use "wait_for_prior_commit" needs to be invoked to serialize them.
At present the "wait_for_prior_commit" is done only in this case
if (rgi_slave &&
rgi_slave->is_parallel_exec &&
find_temporary_table(tl) &&
wait_for_prior_commit())
As per the above code the wait happens in cases where "find_temporary_table(tl)" is successful.
But in some cases the condition fails in those cases code looks for reusable tables , if they are not found it does "open_temporary_table" as shown below.
/*
No reusable table was found. We will have to open a new instance.
*/
if (!table && (share= find_tmp_table_share(tl)))
{
table= open_temporary_table(share, tl->get_table_name(), true);
}
After a successful open there is no call for "wait_for_prior_commit". Adding a wait seems to solve the problem.
Still more analysis needs to be done with respect to when code reaches above "open_temporary_table".
Reproduced the issue on 10.4 with following command:
perl mysql-test-run.pl --verbose-restart --force --retry=3 --max-save-core=0 --max-save-datadir=1 --mem --parallel=4 --ps-protocol rpl.rpl_parallel_temptable --repeat=30