When an ApsaraDB RDS for MySQL instance runs out of storage due to a large undo file, the instance is automatically locked — all write operations fail until the issue is resolved. This topic explains how to diagnose the issue, unlock the instance, and clean up the undo file.
Diagnose the issue
To confirm that undo files are consuming your storage, go to the Monitoring and Alerts page and check the storage usage details. If the Instance Status shows Locking and storage usage is dominated by a large undo file, follow the steps below.
Root cause
Undo data grows when long-running queries run against an InnoDB table while large amounts of data are being written to the same table. MySQL generates undo data to support transaction rollback. When this data fills available storage, RDS locks the instance to prevent data loss — setting the instance status to Locking.
This issue does not affect instances running MySQL 8.0. MySQL 8.0 automatically deletes the undo file when it is no longer needed.
Step 1: Unlock the instance
To bring the instance out of the Locking state, expand its storage capacity. See Change instance specifications. After the change takes effect, the instance is unlocked automatically.
Step 2: Delete the undo file
How you clean up the undo file depends on your MySQL version.
MySQL 5.7
Connect to the instance and run the following statement to check where the undo data is stored. For connection instructions, see Connect to an ApsaraDB RDS for MySQL instance.
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';If the value is 2 — the instance uses independent undo tablespaces, and the undo file can be reclaimed automatically. When the undo file exceeds the size set by innodb_max_undo_log_size and no active transactions reference the data, MySQL truncates the file to free storage. To adjust this threshold, see Modify instance parameters.
If the value is 0 — the undo data is stored in the ibdata1 system tablespace and cannot be deleted. To resolve this, choose one of the following options:
Create a new RDS instance and migrate the data from the original instance.
Upgrade the major engine version to MySQL 8.0. See Upgrade the major engine version.
Upgrading the major engine version may introduce compatibility issues. Review the limits and differences between versions before proceeding. See Upgrade the major engine version.
Both migration and upgrade involve an instance switchover. Perform these operations during off-peak hours and make sure your application is configured to automatically reconnect. See Impacts of an instance switchover.
The innodb_undo_tablespaces parameter can only be set at instance creation and cannot be changed afterward. If the parameter was not specified when the MySQL 5.7 instance was created, it defaults to 0. In that case, updating the minor engine version will not enable independent undo tablespaces.MySQL 5.5 or MySQL 5.6
The undo file cannot be deleted on these versions. Upgrade the major engine version to MySQL 5.7 or MySQL 8.0. See Upgrade the major engine version.
If you upgrade to MySQL 5.7, set the Edition parameter to High-availability Edition. This edition sets innodb_undo_tablespaces to 2 by default, allowing undo data to be stored in and deleted from independent undo tablespaces.
Upgrading the major engine version may introduce compatibility issues. Review the limits and differences between versions before proceeding. See Upgrade the major engine version.
The upgrade involves an instance switchover. Perform the upgrade during off-peak hours and make sure your application is configured to automatically reconnect. See Impacts of an instance switchover.
What to do next
To reduce the risk of this issue recurring:
Optimize SQL statements to avoid slow queries.
Schedule index creation and deletion, table schema modifications, table maintenance, and table deletion during off-peak hours.
Monitor and terminate long-running sessions and transactions. See View monitoring information.
References
Check and clean up other file types to free additional storage:
Enable autonomy service features to prevent undo file growth from locking your instance: