MySQL Bugs: #117644: Lock contention due to FLUSH PRIVILEGES

Bug #117644	Lock contention due to FLUSH PRIVILEGES
Submitted:	8 Mar 3:10	Modified:	12 Mar 14:28
Reporter:	Abdelrahman Omran	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.37	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
We recently encountered an issue after executing "FLUSH PRIVILEGES" on a busy server that led to lock contention, which is not mentioned in the official documentation. If this is the intended behavior, it would be very helpful if the documentation for this statement explicitly notes that it acquires a global lock.

How to repeat:
Run "FLUSH PRIVILEGES" on a very busy server with many concurrent transactions.

Suggested fix:
Provide clearer information about the side effects of using this statement.

Hi, there are some known issues wrt flush privileges on heavily loaded server. Some are fixed in more recent versions of MySQL but basically there is expected behavior for this type of action to affect loaded MySQL. 

We would need more data on what actually happened to your server and how it behaves on latest 8.0.41 to determine if this is a bug or expected behavior.

Thanks

Hi,

We are currently on version 8.0.37 and unable to perform a minor upgrade at the moment. The issue we encountered was high lock contention on the database. There were no concurrent transactions running that would have updated the privileges system (such as creating users or granting privileges). Additionally, although the statement executed quickly, the timing of the execution aligns closely with the incident we experienced.

Can you tell more about the expected behaviour you mentioned?

Thank you!

We observed a mutex lock (trx_sys), high row lock time, deadlocks, and the read/write throughput dropped close to 0. The system auto-recovered after almost 10 minutes.

Hi,

> We are currently on version 8.0.37 and unable to perform a minor upgrade

So no matter what we do we can't help you as any kind of fix would require you to upgrade.

> that would have updated the privileges system 

You do not need to update privilege system, every transaction need to check with priv. system if the user have permission to do what one wanna do hence the issue you encountered.

> The issue we encountered was high lock contention on the database.

This is a duplicate, we have this bug already reported and is unfixed. Question is if it even possible to fix in a feasible way.

If you can share your log file that might help.

I assume you have a lot of:

'Waiting for acl cache lock' 

Or combo of 'Waiting for acl cache lock' with 'Can not lock user management caches for processing.'

Thanks