Description:
This is related to bug:
http://bugs.mysql.com/bug.php?id=44568
That bug concerns online backup but describes a behavior of global read lock which would prevent customers being able to make a mysqldump --single-transaction backup if they need the --master-data option (which briefly uses global read lock to obtain the binlog file and position).
Jorgen describes the behavior of global read lock being starved by commits:
[5 Jun 2009 8:44] Jorgen Loland
The reason for this bug is starvation when the backup code tries to set the global read
lock. The global read lock is implemented so that you won't be able to set it if there is
an ongoing commit. The reason for the starvation is that there's nothing preventing new
threads from going into the commit phase while the backup thread waits for the global
read lock.
Related to the global read lock, commits do this (abstracted from
wait_if_global_read_lock):
if (!global_read_lock)
{
protect_against_global_readlock++;
<do commit things>
protect_against_global_readlock--;
}
To set the global read lock, the code does this:
while (protect_against_global_readlock)
wait;
global_read_lock=true;
The starvation can be avoided if the commit code has to wait if another thread *tries* to
set the global read lock. E.g., global_read_lock can be set to true before "while
(protect_against_global_readlock) wait;", effectively making new commits wait.
----
If that is the case you can not do a dump with single-transaction and master-data if you are performing a lot of commits without throttling back those commits thus preventing the benefit to using --single-transaction.
How to repeat:
Presumably from the behavior above and observed on customers outputs, perform a lot of commits, then try to dump with mysqldump --single-transaction --master-data.
Suggested fix:
A global read lock might have to be taken for many reasons, (preparing a slave without blocking InnoDB writes on a master is one scenario) on a busy server this behavior seems like it could wait indefinitely .
The fix should be what is described in that above bugs reports comment:
The starvation can be avoided if the commit code has to wait if another thread *tries* to
set the global read lock
© 2022, Oracle Corporation and/or its affiliates