Open source MySQL introduced the LOCK_thd_security_ctx lock (Bug#32320541) for each session in MySQL 8.0.28. The lock can cause deadlocks when SHOW PROCESSLIST is executed alongside specific concurrent operations on ApsaraDB RDS for MySQL 8.0 minor versions 20220830 through 20230324.
Affected versions
ApsaraDB RDS for MySQL 8.0 instances running the following minor engine versions are affected:
20220830
20221121
20221231
20230324
Causes
For the full upstream bug report, see Bug#110494. The deadlock occurs when all three of the following lock acquisition sequences happen concurrently:
SHOW PROCESSLISTacquires locks in this order:LOCK_thd_remove→LOCK_thd_security_ctxCOM_CHANGE_USERacquires locks in this order:LOCK_thd_security_ctx→LOCK_statusCOM_STATISTICS,FLUSH STATUS, or the Performance Agent feature acquires locks in this order:LOCK_status→LOCK_thd_remove
When all three sequences overlap, a circular wait forms: LOCK_thd_remove → LOCK_thd_security_ctx → LOCK_status → LOCK_thd_remove.
Potential impacts
When all three concurrent conditions described in Causes are met, LOCK_thd_remove and LOCK_status become blocked, which causes the following issues on the affected RDS instance:
Existing connections cannot be closed. If new connections keep being established, the connection count reaches the upper limit and the instance becomes unreachable.
Statements such as
SHOW GLOBAL STATUScannot be executed, and monitoring data cannot be collected.Performance Agent stops recording performance data, and monitoring data is lost.
Solution
Update the minor engine version of your RDS instance to 20230610 or later. This version fixes the deadlock that can occur when SHOW PROCESSLIST is executed. For instructions, see Update the minor engine version.