All Products
Search
Document Center

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

更新時間:Aug 24, 2023

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.存储空间使用量

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

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

      2. 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;
      3. Execute the DROP TABLE <Table name>; statement in the database to delete the data.

      4. Wait approximately 5 minutes for the system to unlock your RDS instance.

      PostgreSQL

      1. Use DMS to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

      2. 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;
      3. Execute the DROP TABLE <Table name>; statement in the database to delete the data.

      4. Wait approximately 5 minutes for the system to unlock your RDS instance.

      SQL Server

      1. Use DMS to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

      2. 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
      3. Execute the DROP TABLE <Table name>; statement in the database to delete the data.

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