All Products
Search

This Product

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

    Document Center

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

    Last Updated:Oct 21, 2026

    Symptoms

    • The instance status on the instance Basic Information page is Locking.

    • When an instance is Locking, you cannot INSERT or UPDATE data.

      Note

      • For RDS MySQL 5.6, 5.7, and 8.0 with minor engine versions released on or after 20190815, an instance can have the following three lock states:

        • LOCK_WRITE_GROWTH: A lock that prevents disk growth. It usually occurs because the disk of the primary instance is full, and it prevents operations that would increase disk usage. DELETE statements generate a large amount of binlog data and increase disk usage. If you need to clean up data, use DROP or TRUNCATE statements.

        • LOCK_READ: A lock that prevents reads. It usually occurs because the disk of a read-only instance is full, and it prevents queries and writes.

        • LOCK_WRITE: A lock that prevents writes. It can occur because the instance has expired, the host has expired (a state that exists only in MyBase), or the instance is being migrated. In addition to the LOCK_WRITE_GROWTH restrictions, it prevents other data writes, such as DROP and TRUNCATE.

        When an instance is locked, running some SQL statements returns the following error: ERROR 1290 (HY000): The MySQL server is running with the LOCK_WRITE_GROWTH option so it cannot execute this statement.

      • For all minor engine versions of RDS MySQL 5.1 and 5.5, and RDS MySQL 5.6, 5.7, and 8.0 minor engine versions released before 20190815, the instance cannot perform any operation after it is locked, regardless of the cause.

    Common causes

    • The instance storage is full.

    • The account has overdue payments, or the instance has expired.

    Resolve full instance storage

    On the instance Basic Information page, check whether the instance storage is full.

    In the Instance Resources section, the Storage Capacity field displays the used and total capacity. Click View Details for more information.

    Solutions:

    Release storage capacity

    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 disk space used by each type of instance data. On the Monitoring and Alerts page, you can view monitoring charts such as MySQL CPU/Memory Utilization, MySQL Storage Space Usage, and Disk Usage. The MySQL Storage Space Usage chart displays the space usage by component, including ins_size, binlog_size, relaylog_size, undolog_size, redolog_size, sys_data_size, and user_data_size. You can drag to select a time range and start a diagnosis.

    3. Clear disk space based on the database type.

      Warning

      Data is valuable. Delete data with caution. If possible, expand the storage capacity to unlock the instance instead of deleting data. If you must delete data, back up the database first to prevent data loss.

      • Temporary files (corresponding to temp_file_size in standard monitoring)

        Cause: A MySQL instance can generate temporary table files while sorting, grouping, or joining tables in query statements, or binlog cache files before a large transaction is committed. These files can fill the instance disk space.

        Solution: 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 (corresponding to binlog_size and general_log_size in standard monitoring)

        Cause: A database management system generates query logs, slow query logs, and error logs to help administrators monitor database performance and health.

        Engine

        Resolution

        MySQL

        Based on the disk space usage information in Monitoring and Alerts, delete the corresponding log files.

        PostgreSQL

        RDS PostgreSQL log files cannot be deleted manually.

        You can manually delete inactive Replication Slots to allow the RDS PostgreSQL kernel to automatically clear WAL logs. For more information, see Use the WAL log management feature.

        SQL Server

        RDS SQL Server log files cannot be deleted manually, but you can use the console to shrink transaction logs.

      • Data files (corresponding to user_data_size in standard monitoring)

        Database engine

        Resolution

        MySQL

        1. Connect to an instance through DMS.

        2. Run the following SQL statement to view database table sizes and identify historical or unnecessary data that you can delete.

          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. In the relevant database, use the DROP TABLE <Table name>; command to delete data.

        4. After cleanup, wait about 5 minutes for the RDS instance to unlock.

        PostgreSQL

        1. Connect to the instance through DMS. For more information, see Use DMS to log on to an ApsaraDB RDS instance.

          Note

          If you cannot connect to the instance, first Expand storage capacity, and then clear disk space after the expansion is complete. After cleanup, decide whether to reduce the storage capacity based on your requirements. For more information about reducing the storage capacity, see Change configuration.

        2. Run the following SQL statements to identify the sources of disk space usage:

          1. View the space usage of each database

          SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

          2. View the space usage of tables, including indexes, in the current database

          SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

          3. View the size of a specified table (original method)

          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. After you identify historical or unnecessary data that can be deleted, in the relevant database, use the DROP TABLE <Table name>; command to delete data.

          Important

          Unlock the instance by expanding the storage capacity before you delete data. After cleanup, wait about 5 minutes for the RDS instance to unlock.

        SQL Server

        For more information, see Insufficient space issues for RDS for SQL Server.

      • System files (corresponding to undolog_size in standard monitoring)

        Cause: If a query statement on an InnoDB table does not finish for a long time and the table undergoes significant data changes during the query, the system generates a large amount of undo information. The undo information consumes storage space until the storage is exhausted.

        Solution: See Binary log file accumulation.

    Expand storage capacity

    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. On the Basic Information page, in the Configuration Information section, click Change Specifications to expand the instance storage capacity.

    3. After you complete the payment, go to Task Center to view the configuration change progress.

      The expansion duration varies by storage type, as described below. You can visit the RDS console home page and view the expansion progress in Tasks in the left-side navigation pane.

      Storage type

      Expansion duration

      Notes

      Premium Local SSD

      Subject to actual conditions.

      • If no local resources are available, a cross-host migration is triggered. The expansion duration is affected by many factors, so expand the storage capacity during off-peak hours.

      • Configuration changes cause a transient connection interruption of about 15 seconds. Perform configuration changes during off-peak hours and make sure that your application has an automatic reconnection mechanism. During the interruption, most database-, account-, and network-related operations cannot be performed.

      Cloud disk

      About 5 minutes.

      • No transient connection interruption occurs while MySQL or PostgreSQL cloud disk instances are expanded.

      • A SQL Server cloud disk instance might have a transient connection interruption of about 30 seconds while expanding storage capacity. Most database-, account-, and network-related operations cannot be performed during the interruption. Perform configuration changes during off-peak hours, or make sure that your application has an automatic reconnection mechanism.Some instances currently support lossless storage expansion, which does not interrupt database access.

      After the expansion is complete, the instance ID and connection string remain unchanged. However, the instance IP address might change if the expansion of Premium Local SSD triggers a cross-host migration. Use the domain name in the connection string to connect to the instance instead of using the IP address directly.

    Resolve overdue payments or instance expiration

    • Subscription: If the instance has expired and was not renewed, renew the instance, and then wait 5 minutes and check whether the instance status is Running.

    • Pay-as-you-go: If the account has overdue payments, top up the account, and then wait 5 minutes and check whether the instance status is Running.

    Additional operational recommendations

    Configure the following settings to prevent the instance from being locked.

    • Set up alert notifications for instance expiration and overdue payments.

      1. Log on to the RDS management console.

      2. In the upper-right corner of the page, click the Notification icon to open the Message Center page.

      3. In the left-side navigation pane, click Basic Recipient Management.

      4. On the Basic Recipient Management page, under Message Type, select Product Overdue Payment, Service Suspension, and Imminent Release Notifications, and then click Modify.

      5. In the Modify Message Recipients dialog box, select the contacts to notify, and then click Save to complete the settings.

    • Configure an instance storage capacity alert. Set the alert to trigger when the storage usage exceeds 90%.

    • Enable SQL Explorer and Audit. When the storage usage increases sharply, use Monitoring and Alerts to query historical SQL statements from the period when the storage usage increased and optimize the SQL statements.

    • Configure automatic storage expansion. When resources are insufficient, the system automatically expands the storage capacity. For more information, see Configure automatic storage expansion for RDS MySQL, Configure automatic storage expansion for RDS PostgreSQL, and Configure automatic storage expansion for RDS SQL Server.

    • If temporary files are large, optimize the SQL statements and avoid frequent ORDER BY and GROUP BY operations.

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

    When you use a DELETE statement to delete data, the database marks only records or data pages as reusable. It does not directly reduce the size of disk files, so tablespace is not automatically reclaimed. To release tablespace, you can Use OPTIMIZE TABLE to reclaim MySQL tablespace.

    Why is an instance still locked when it has sufficient storage space or has been renewed?

    A task, such as a configuration change, is running on the instance. The instance unlocks automatically after the task finishes. In the upper-right corner of the Basic Information page of the instance, click the Button button to go to the Tasks page and view the task progress. If no task is running and the disk usage has dropped to 100%, the instance still goes through three stages before it unlocks: disk usage is checked on a fixed cycle rather than in real time; the system issues an unlock task only after it detects that the disk usage has dropped below 100%; and the unlock task runs and completes the unlock. The instance usually unlocks automatically within 10 minutes after the disk usage drops below 100%.

    Can I change instance specifications while the instance displays “Locking”?

    You can upgrade or downgrade specifications only when the instance is locked because the disk is full. If the instance is locked because of overdue payments, renew it first, and then change the specifications.

    How do I unlock an instance of a legacy instance type by expanding the storage capacity?

    First change the instance specifications to a currently available instance type, and then expand the instance storage capacity. For currently available instance types, see Primary ApsaraDB RDS instance types.

    Why can the storage usage increase while an instance is in the “Locking” state?

    In the “Locking” state, you cannot perform INSERT or UPDATE operations. However, query operations can still generate log files or other temporary data, which can further increase the storage usage.