MySQL Bugs: #86079: Deadlock with slave_preserve_commit

Bug #86079	Deadlock with slave_preserve_commit_order=ON with Bug#86078.
Submitted:	25 Apr 2017 19:28	Modified:	27 Feb 2018 17:32
Reporter:	Jean-François Gagné	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	8.0.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

with I was reproducing Bug#86078 to understand more, I ran the slave with slave_preserve_commit_order=ON.  This lead to a deadlock in the SQL_THREAD.  I had the following:

-SHOW SLAVE STATUS shows "Slave_SQL_Running: Yes" with a worker error,
-The worker error is the same as in Bug#86078 (Duplicate entry)

There is no way to stop the slave: a STOP SLAVE hangs and killing the worker do no help (I had to resort to kill -9).

Thanks in advance for looking into this,

JFG

How to repeat:
See description.

Thank you Jean, since this is related with Bug #86078, copying this internally as well to keep track of this.

regards,
Umesh

Posted by developer:

The issue was not easy to reproduce, but is not reproducible anymore after
BUG#26277771 and BUG#25585436 were fixed.

BUG#26177771 was allowing dependent transactions to be executed in parallel.

BUG#25585436 was making the MTS coordinator to misbehave was asked to stop in
parallel with workers signaling errors.

Fixed in the upcoming release 8.0.4.

A similar problem is identified in Bug#89247.