What do I do if my ApsaraDB RDS instance is in the Locking state? - ApsaraDB RDS

Problem description

On the Basic Information page of the RDS instance, the value of Status for the RDS instance is Locking.
The INSERT and UPDATE statements fail to be executed on the RDS instance because the RDS instance is in the Locking state.
Note
- If your RDS instance runs MySQL 5.6, MySQL 5.7, or MySQL 8.0 and uses the minor engine version of 20190815 or later, the RDS instance may encounter the following types of locks:
  - LOCK_WRITE_GROWTH: prohibits the increase in disk usage. If the storage capacity of your primary RDS instance is exhausted, this type of lock is triggered to disable the operations that may cause increase in disk usage. If you execute the DELETE statement to delete data, a large number of binary log files are generated, which increases disk usage. We recommend that you execute the DROP or TRUNCATE statement to delete data.
  - LOCK_READ: prohibits read operations. If the storage capacity of the read-only RDS instance is exhausted, this type of lock is triggered to disable data queries and write operations.
  - LOCK_WRITE: prohibits write operations. This type of lock may be triggered by instance expiration, host expiration for an ApsaraDB MyBase cluster, or instance migration. In addition to the limits of LOCK_WRITE_GROWTH, this type of lock also disables write operations of additional data, such as DROP and TRUNCATE operations.
  If the RDS instance is locked and you execute some SQL statements on the RDS instance, the ERROR 1290 (HY000): The MySQL server is running with the LOCK_WRITE_GROWTH option so it cannot execute this statement error message is displayed.
- If your RDS instance that runs MySQL 5.1 or MySQL 5.5 and uses any minor engine version is locked, you cannot perform operations on the RDS instance regardless of the causes for the lock. This also applies to the RDS instances that run MySQL 5.6, MySQL 5.7, or MySQL 8.0 and use a minor engine version earlier than 20190815.

Causes

Cause 1: The storage capacity of your RDS instance is exhausted.
Cause 2: Your Alibaba Cloud account has overdue payments, or your RDS instance expires.

Handling method corresponding to Cause 1

You can go to the Basic Information page of the RDS instance to check whether the storage of your RDS instance is exhausted in the Usage Statistics section. 存储空间使用量 Methods:

Release the storage of your RDS instance
Expand the storage capacity of your RDS instance

Release the storage of your RDS instance

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Monitoring and Alerts to view the storage that is occupied by each type of data.

Delete data to release storage based on the database type.

Warning

We recommend that you do not delete data unless necessary. If you want to delete data to release storage, you must back up the data before you delete it to prevent data loss. You can also expand the storage capacity of your RDS instance to unlock the RDS instance.

Temporary files
RDS for MySQL instances generate temporary tables to perform the sorting, grouping, or join operations specified by queries or generate binary log cache files before committing a large transaction. The disk space of your instance may be exhausted by these temporary files.
For more information, 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

To monitor the performance and status of a database, the management system of the database generates various logs, such as query logs, slow query logs, and error logs.

Database engine	Solution
MySQL	On the Monitoring and Alerts page, view the storage usage. Then, delete log files based on the storage usage. What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by binary log files? FAQ about the general query log feature of ApsaraDB RDS for MySQL
PostgreSQL	You cannot manually delete the log files of an ApsaraDB RDS for PostgreSQL instance. You can manually delete inactive replication slots to allow AliPG to automatically delete WAL logs. For more information, see Use the WAL log management feature for an ApsaraDB RDS for PostgreSQL instance.
SQL Server	You cannot manually delete the log files of an ApsaraDB RDS for SQL Server instance. However, you can shrink transaction logs in the ApsaraDB RDS console.

Data files

Database engine	Solution
MySQL	Use Data Management (DMS) to connect to your RDS instance. Execute the following statement to view the size of the specified table in the database on your RDS instance and check the historical data or unnecessary data that can be deleted: `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;` Execute the `DROP TABLE <Table name>;` statement in the database to delete the data. Wait approximately 5 minutes for the system to unlock your RDS instance.
PostgreSQL	Use DMS to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance. Note If you cannot connect to your RDS instance, expand the storage capacity of the instance first and then clear the disk space. After the disk space is cleared, reduce the storage capacity as needed. For more information about how to expand and reduce the instance storage capacity, see Expand the storage capacity of your RDS instance and Change instance specifications. Execute the following statement to view the size of the specified table in the database on your RDS instance and check the historical data or unnecessary data that can be deleted: `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;` Execute the `DROP TABLE <Table name>;` statement in the database to delete the data. Wait approximately 5 minutes for the system to unlock your RDS instance.
SQL Server	Perform the operations provided in Troubleshoot insufficient storage issues on an ApsaraDB RDS for SQL Server instance.

System files: You cannot delete system files.

Expand the storage capacity of your RDS instance

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the Configuration Information section of the Basic Information page, click Change Specifications to expand the storage capacity.
On the page that appears, expand the storage capacity of the RDS instance. For more information, see Change the specifications of an ApsaraDB RDS for MySQL instance.

After you complete the payment, click the icon in the upper-right corner of the Basic Information page to view the configuration change progress on the Tasks page.

The time required to expand the storage capacity varies based on the storage type of your RDS instance. The following table describes the time required to expand the storage capacity. You can log on to the ApsaraDB RDS console and click Task Center in the left-side navigation pane to view the progress of storage capacity expansion.

Storage type	Duration	Description
Local disks	Vary with scenarios	If the storage resources of an RDS instance cannot meet your business requirements, cross-instance data migration may be triggered, and the time required to expand the storage capacity varies based on multiple factors. We recommend that you expand the storage capacity during off-peak hours. A specification change causes a transient connection that lasts approximately 30 seconds. We recommend that you change the specifications of your RDS instance during off-peak hours. In addition, make sure that your application is configured to automatically reconnect to your RDS instance. During the transient connection, you cannot perform most of the operations that are related to databases, accounts, and network settings.
Cloud disks	About 5 minutes	If your RDS instance runs MySQL or PostgreSQL and uses cloud disks, no transient connections occur when you expand the storage capacity of the RDS instance. If your RDS instance runs SQL Server and uses cloud disks, a transient connection that lasts approximately 30 seconds may occur when you expand the storage capacity of the RDS instance. During the transient connection, you cannot perform most of the operations that are related to databases, accounts, and network settings on the RDS instance. We recommend that you expand the storage capacity of your RDS instance during off-peak hours. Alternatively, make sure that your application is configured to automatically reconnect to your RDS instance. Specific RDS instances support storage capacity expansion without data loss. This does not interrupt your workloads.

Handling method corresponding to Cause 2

Subscription RDS instance: If your RDS instance expires and is not renewed, renew the instance. Wait approximately 5 minutes. Then, check whether the instance is in the Running state.
Pay-as-you-go RDS instance: If your Alibaba Cloud account has an overdue payment, top up your Alibaba Cloud account and wait 5 minutes to check whether the RDS instance is in the Running state.

O&M suggestions

We recommend that you perform the following configurations to prevent your RDS instance from being locked:

Configure a notification policy for overdue payments and instance expiration.
1. Log on to the ApsaraDB RDS console.
2. Click the icon in the upper-right corner to go to the Message Center page.
3. In the left-side navigation pane, click Common Settings.
4. In the Notification Type column of the Common Settings page, select Product Overdue Payment, Suspension, and Imminent Release Notifications and click Modify.
5. In the Modify Contact dialog box, select alert contacts and click Save.
Configure alert rules for storage usage. We recommend that you configure an alert rule based on which an alert is triggered when the storage usage exceeds 90%.
Enable the SQL Explorer and Audit feature. If the storage usage significantly increases, you can query the SQL statements that are executed during the increase based on information on the Monitoring and Alerts page and optimize the SQL statements.
Configure automatic storage expansion. If the storage resources of your RDS instance are insufficient, the system automatically expands the storage capacity of your RDS instance. For more information, 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.
Optimize SQL statements to prevent frequent use of the ORDER BY and GROUP BY clauses in scenarios in which large temporary files are used.

Why is LOCK_WRITE_GROWTH still enabled after I delete a large amount of data from my RDS instance?

If you execute DELETE statement to delete data, the system marks the deleted records or data pages as reusable. In this case, the system does not directly reduce the size of disk files to reclaim tablespaces. If you want to release tablepaces, perform the operations provided in How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance?.

Why is my RDS instance still locked although the instance has sufficient storage resources or is renewed?

A task, such as a configuration change task, on the RDS instance is in progress. After the task is complete, the RDS instance is automatically unlocked. You can click the icon in the upper-right corner of the Basic Information page to go to the Tasks page and view the task progress.

Am I able to upgrade or downgrade the configuration of my RDS instance when the instance is in the Locking state?

If your RDS instance is locked because the storage capacity of the instance is exhausted, you can upgrade or downgrade the configuration of your RDS instance. If your RDS instance is locked due to other reasons such as overdue payments, you must complete the overdue payments and then upgrade or downgrade the configuration of your RDS instance.

My RDS instance in the Locked state uses a phased-out instance type. How do I unlock the instance by expanding storage capacity?

You need to change the instance type of your RDS instance to an available instance type and then expand the storage capacity to unlock the instance. For more information about the available instance types, see Primary ApsaraDB RDS instance types.

Why does the storage usage of an instance in the Locked state keep increasing?

Data cannot be written to an instance in the Locked state by performing INSERT or UPDATE operations. However, when query operations are performed on the instance, log files or temporary data may be generated and increase the instance storage usage.