Resolve an RDS instance Locking state due to exhausted storage or overdue payments - ApsaraDB RDS

When an RDS instance enters the Locking state, INSERT and UPDATE operations are blocked. This article explains how to identify the cause and restore the instance to the Running state.

Lock types

The Locking state covers different lock types depending on your MySQL minor engine version. Understanding which type is active helps you choose the correct recovery action.

Applies to: ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0 with minor engine version 20190815 or later.

Lock type	Trigger	Blocked operations
`LOCK_WRITE_GROWTH`	Primary instance storage exhausted	Operations that increase disk usage. Use DROP or TRUNCATE instead of DELETE, because DELETE generates binary log files and increases disk usage.
`LOCK_READ`	Read-only instance storage exhausted	All read and write operations
`LOCK_WRITE`	Instance expiration, ApsaraDB MyBase host expiration, or instance migration	All write operations including DROP and TRUNCATE

When LOCK_WRITE_GROWTH is active, executing a blocked SQL statement returns:

ERROR 1290 (HY000): The MySQL server is running with the LOCK_WRITE_GROWTH option so it cannot execute this statement

For RDS instances running MySQL 5.1 or 5.5, or MySQL 5.6, 5.7, and 8.0 with a minor engine version earlier than 20190815, the instance is fully locked regardless of the cause. No operations are permitted.

Identify the cause

On the Basic Information page of your RDS instance, check the Status field.

The two most common causes are:

Storage exhausted: The instance storage capacity is full.
Overdue payment or expiration: Your Alibaba Cloud account has an overdue payment, or a subscription instance has expired.

Resolve a storage issue

Go to Monitoring and Alerts in the left-side navigation pane to see storage usage by category. Then take one of the following actions based on what is consuming the most space.

Warning

Back up your data before deleting anything to prevent data loss. Expanding storage capacity is a safe alternative to data deletion.

Release storage

Temporary files (`temp_file_size`)

ApsaraDB RDS for MySQL generates temporary tables for sorting, grouping, and join operations, and binary log cache files before large transactions are committed. For steps to clear temporary files, see What do I do if an ApsaraDB RDS for MySQL instance is in the Locked state because its storage capacity is exhausted by temporary files?

Log files (`binlog_size`, `general_log_size`)

Database engine	Resolution
MySQL	View storage usage on the Monitoring and Alerts page, then delete log files. See What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by binary log files? and Solution for an instance with full disk space caused by the General log
PostgreSQL	You cannot delete log files manually. Delete inactive replication slots to let AliPG automatically remove WAL logs. See Use the WAL log management feature for an ApsaraDB RDS for PostgreSQL instance
SQL Server	You cannot delete log files manually. Shrink transaction logs in the ApsaraDB RDS console. See Troubleshoot insufficient storage space issues on an ApsaraDB RDS for SQL Server instance

Data files (`user_data_size`)

For MySQL:

Connect to your RDS instance using Data Management (DMS).

Run the following query to find the largest tables:

SELECT
    TABLE_NAME,
    concat(round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,2),'MB') AS DATA
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = '<Database name>'
ORDER BY
    DATA + 0 DESC;

Drop tables that contain historical or unnecessary data:
```
DROP TABLE <Table name>;
```
Wait approximately 5 minutes. The system unlocks the instance automatically once storage drops below the limit.

For PostgreSQL:

Connect to your RDS instance using DMS. See Use DMS to log on to an ApsaraDB RDS instance.
If you cannot connect, expand storage capacity first, clear disk space, then reduce storage as needed. See Change instance specifications.

Run the following query to identify large tables:

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size
FROM
    information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC;

Drop tables that contain historical or unnecessary data:
```
DROP TABLE <Table name>;
```
Wait approximately 5 minutes. The system unlocks the instance automatically.

For SQL Server:

Follow the steps in Troubleshoot insufficient storage issues on an ApsaraDB RDS for SQL Server instance.

System files (`undolog_size`)

Excessive undo logs accumulate when long-running queries on InnoDB tables coincide with large data modifications. For resolution steps, see Troubleshoot insufficient storage caused by system file accumulation.

Expand storage capacity

Go to the Instances page. Select the region where your RDS instance resides, then click the instance ID.
In the Configuration Information section of the Basic Information page, click Change Specifications to expand the storage capacity.
After completing payment, check progress in Task Center.

Expansion duration varies by storage type:

Storage type	Duration	Notes
Premium Local SSDs	Varies	May trigger cross-instance data migration. A transient connection lasting approximately 30 seconds occurs. Expand during off-peak hours and configure your application to reconnect automatically.
Cloud disks	About 5 minutes	MySQL and PostgreSQL: no transient connections. SQL Server: a transient connection lasting approximately 30 seconds may occur; during the transient connection, you cannot perform most of the operations related to databases, accounts, and network settings. Expand during off-peak hours or configure your application to reconnect automatically. Specific RDS instances support storage expansion without data loss, which does not interrupt workloads.

Resolve an overdue payment or expiration

Subscription RDS instance: Renew the instance, then wait approximately 5 minutes and verify the instance status is Running.
Pay-as-you-go RDS instance: Top up your Alibaba Cloud account, then wait 5 minutes and verify the instance status is Running.

Prevent future locks

Configure the following to avoid the Locking state:

Storage alerts: Configure alert rules for storage usage. Set an alert threshold at 90% usage to get notified before storage is exhausted.
Automatic storage expansion: Configure auto-expansion so the system increases storage capacity when it runs low. See Configure automatic storage expansion for an ApsaraDB RDS for MySQL instance, Configure automatic storage expansion for an ApsaraDB RDS for PostgreSQL instance, and Configure automatic storage expansion for an ApsaraDB RDS for SQL Server instance.
Payment notifications: Configure overdue payment and expiration notifications in Message Center.
1. Log on to the ApsaraDB RDS console.
2. Click the icon in the upper-right corner to open Message Center.
3. In the left-side navigation pane, click Common Settings.
4. In the Notification Type column, select Product Overdue Payment, Suspension, And Imminent Release Notifications and click Modify.
5. In the Contact dialog box, select the contacts to notify and click Save.
SQL monitoring: Enable the SQL Explorer and Audit feature. When storage usage spikes unexpectedly, query the SQL statements executed during that period on the Monitoring and Alerts page and optimize them.
SQL optimization: Avoid frequent ORDER BY and GROUP BY operations on large datasets, as these generate large temporary files.

FAQ

Why is LOCK_WRITE_GROWTH still active after I deleted a large amount of data?

Running DELETE marks the removed records or data pages as reusable but does not shrink the underlying disk files or reclaim tablespace. To actually free disk space, run OPTIMIZE TABLE after deletion.

The instance still shows Locking even though storage is sufficient and payment is current

A task, such as a configuration change task, on the RDS instance is in progress. The instance unlocks automatically once the task completes. Click the icon in the upper-right corner of the Basic Information page to open the Tasks page and check progress.

Can I change instance specifications while the instance is locked?

If the instance is locked due to storage exhaustion, you can upgrade or downgrade specifications. If it is locked for other reasons such as overdue payments, resolve the payment issue first.

The locked instance uses a phased-out instance type. How do I expand storage?

Change to an available instance type first, then expand storage. For available types, see Primary ApsaraDB RDS instance types.

Why does storage usage keep increasing while the instance is locked?

INSERT and UPDATE operations are blocked, but SELECT queries can still generate log files and temporary data, which increases storage usage.