Bug #89229 FLUSH PRIVILEGES may cause MTS deadlock
Submitted: 15 Jan 2018 3:18
Reporter: Libing Song Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[15 Jan 2018 3:18] Libing Song
FLUSH PRIVILEGES commit its transaction and release the table locks before binlog. That means a following GRANT statement could be binlogged in the same commit window with the FLUSH PRIVILEGES statement. Thus the FLUSH PRIVILEGES and the GRANT can be applied parallel on slave. That may cause a deadlock if slave-preserve-commit-order is ON and GRANT gets the locks before FLUSH PRIVILEGES. 

The deadlock looks like:
- GRANT (applied before FLUSH PRIVILEGES and the locks will be hold until commit)
  waiting for FLUSH PRIVILEGES to commit
- FLUSH PRIVILEGES(blocked when acquiring the locks.)
  waiting for GRANT to release the locks. 

How to repeat:
--source include/have_binlog_format_row.inc
--connect(con1, localhost, root)
--connect(con2, localhost, root)
--connection default

CREATE USER slb@localhost;

SET debug_sync= "bgc_after_sync_stage_before_commit_stage SIGNAL after_sync WAIT_FOR go_commit";

--connection con1

sleep 1;

show processlist;

SET debug_sync='now WAIT_FOR after_sync';
show processlist;
SET debug_sync= "bgc_after_flush_stage_before_sync_stage SIGNAL after_flush WAIT_FOR go_commit1";
--send GRANT ALL PRIVILEGES ON *.* TO 'slb'@localhost

--connection con2
SET debug_sync= 'now WAIT_FOR after_flush';
show processlist;
SET debug_sync='now SIGNAL go_commit';
show processlist;
SET debug_sync='now SIGNAL go_commit1';
show processlist;

--connection default
--connection con1

using mysqlbinlog to check the events, FLUSH PRIVILEGES and GRANT have same last_committed number
[15 Mar 2021 9:53] WANG GUANGYOU
hit the issue.