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

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

Release the storage of your RDS instance

1. 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.

2. In the left-side navigation pane, click Monitoring and Alerts to view the storage that is occupied by each type of data.

3. Delete data to release storage based on the type of data.

   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.

   • Data files

     Database engine	Solution
     MySQL	Use Data Management (DMS) to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL 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. 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	Use DMS to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL 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 t.NAME AS TableName, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE 1=1 AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name 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.

   • Log files

     Database engine	Solution
     MySQL	Delete binary log files. For more information, see Delete the binary log files of an ApsaraDB RDS for MySQL instance.
     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.

   • 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?

   • System files: You cannot delete system files.

Expand the storage capacity of your RDS instance

1. 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.

2. In the Configuration Information section of the Basic Information page, click Change Specifications.

   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.

3. 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.

   Storage type	Duration	Description
   Local disks	Based on your business requirements	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 occurs 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.

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. For more information about how to renew an RDS instance, see Manually renew an ApsaraDB RDS for MySQL instance.

• 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%. For more information, see Configure an alert rule for an ApsaraDB RDS for MySQL instance.

• Enable SQL Explorer and Audit. 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. For more information about SQL Explorer and Audit, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

• 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 and Configure automatic storage expansion for an ApsaraDB RDS for PostgreSQL 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 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.