Analyzing and Resolving MySQL Global Read‑Lock Deadlocks in Parallel Replication

Phenomenon

On a MySQL 8.0.18 replica, the threads table shows many connections waiting for various locks, especially global read lock and commit lock . The author captured a partial snapshot of the threads output, highlighting rows that are blocked by set global read_only=on statements.

Analysis

Three types of waiting threads are identified:

Threads waiting for a global read lock

Threads waiting for a commit lock

Threads waiting for MTS sequential commit

Using performance_schema.metadata_locks, the author traces the lock chain: thread 47295994 is waiting for a global read lock, which is held by a set global read_only=on command issued by the proxy_monitor user from IP 10.108.76.139/140. Further queries reveal that the global read lock is blocked by commit locks held by threads 47295995 and 47295996.

mysql> select * from performance_schema.metadata_locks where THREAD_ID = 47295994;

The waiting state "Waiting for preceding transaction to commit" indicates that a transaction must wait for earlier transactions in the same commit group, which is controlled by the slave_preserve_commit_order parameter.

Lock‑Wait Diagram

A diagram (omitted here) illustrates the circular wait: the set global read_only thread holds a global read lock, another thread holds a commit lock, and the first thread is waiting for that commit lock, forming a deadlock.

Solution

Kill the sessions that acquired the global read lock (painful when thousands of proxy_monitor sessions exist).

Set slave_preserve_commit_order=0 to disable ordered commit in the replica, eliminating the deadlock without stopping replication.

Modify the monitoring script: if the replica is not read‑only, stop replication first, then enable read‑only, avoiding the lock‑contention scenario.

Commands that can trigger the deadlock include:

set global read_only=ON;  grant all on *.* to ...;  flush tables with read lock;

These statements acquire a global read lock and, under parallel replication with slave_preserve_commit_order=1, may deadlock when later transactions in the same commit group wait for earlier ones.

Reproducing the Two Deadlocks

Using sysbench to generate load, repeatedly executing set global read_only=ON on the replica eventually hangs. Killing the offending set global read_only session restores replication.

Deadlock 1 (observed in the customer site) shows the lock chain described above; screenshots of performance_schema.threads and metadata_locks confirm the same pattern.

Deadlock 2 demonstrates a slightly different ordering where a transaction acquires the global commit lock before the global read lock, leading to a similar circular wait.

References

https://sq.163yun.com/blog/article/211685211777150976 http://dbaplus.cn/news-11-1874-1.html http://mysql.taobao.org/monthly/2018/02/01/ Bug report: https://bugs.mysql.com/bug.php?id=95863