Multithreaded slave replication dead lock when set super_read_only to ON

This issue has been fixed by upstream in 8.0.23 by https://dev.mysql.com/worklog/task/?id=13574.

Release Notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html

There are three ways of hitting this issue:

• SET GLOBAL read_only=[ON/OFF]

• SET GLOBAL super_read_only=[ON/OFF]

• FLUSH TABLE WITH READ LOCK

As explained by  this is a design flaw. Few possible workarounds:

• Execute STOP SLAVE SQL_THREAD before executing any of the above operations

• In case of a xtrabackup executing FTWRL, use --safe-slave-backup to avoid the issue.

• If the deadlock is being observed in a GR setup, while taking backup (Backup tool executing FTWRL),  In Group replication topology with single primary node setup, take backup on the primary node.

• Disable slave_preserve_commit_order

• Disable MTS / run it with slave_parallel_workers=1

Hi,

Bug still reproducible with PS 8.0.20  and in my test it is for `FLUSH TABLES WITH READ LOCK' command on Slave.

To:
CC:

Hi, I'm jira-bot, Percona's Jira automation tool. I've detected that someone from
Percona has made an edit to the Summary field of an issue that you reported.

I'm not sentient (yet) so I'm not sure whether the person fixed a typo, changed
a few words, or completely rewrote the text. In any case, it is Percona Engineering's
intention to make the Summary and Description of an issue as accurate as possible
so that we're fixing the actual problem you're encountering, and to avoid
misunderstandings about symptoms and causes.

If the current Summary does not accurately reflect the problem you are reporting,
or if you feel the change was otherwise inappropriate in some way, please add a
new comment explaining things and we'll address it as soon as we can.

This message will be added only once per issue, regardless of how many times
the Summary is edited.

message-code:summary-edited

Hi

Thank you for the report.

Validated as described, also issue reproduciable with upstream version (8.0.16 and 5.7.26-log MySQL Community Server)reported a bug in upstream.

https://bugs.mysql.com/bug.php?id=95863