All Products
Search

This Product

    Data Management:Perform lock-free DML operations

    Document Center

    Data Management:Perform lock-free DML operations

    Last Updated:May 22, 2025

    If you want to change a large amount of data in a table without locking the table, you can use the lock-free DML feature of Data Management (DMS). You can use this feature to split the data on which a single SQL statement is to be executed into multiple batches and executes the SQL statement on each batch. This way, the impact of the DML operations on database performance and database space can be reduced. This topic describes how to perform lock-free DML operations in DMS.

    Prerequisites

    • The database is of one of the following types:

      • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB for Xscale, and third-party MySQL databases

      • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, and third-party PostgreSQL databases

      • MariaDB: ApsaraDB for MariaDB and third-party MariaDB databases

      • ApsaraDB for OceanBase in MySQL mode

      • PolarDB for PostgreSQL(Compatible with Oracle)

    • The database instance is managed in Stable Change or Security Collaboration mode in DMS. For more information, see View the control mode of an instance.

    Usage notes

    The lock-free DML feature supports only operations on a single table and simple SQL statements such as UPDATE and DELETE.

    Procedure

    1. Log on to the DMS console V5.0.

    2. In the top navigation bar, choose Database Development > Data Change > Lockless Change.

      Note

      If you use the DMS console in simple mode, move the pointer over the function icon in the upper-left corner and choose All Features > Database Development > Data Change > Lockless Change.

    3. On the page that appears, configure the parameters and click Submit. The following table describes the parameters.

      Note

      In this example, the instance that is managed in Security Collaboration mode is used. If you configure a lock-free change ticket for an instance that is managed in Flexible Management or Stable Change mode, only the Database and SQL Statements for Change parameters are required.

      Parameter

      Required

      Description

      Database

      Yes

      The database on which you want to perform data changes. Select the database from the Database drop-down list. You can also enter a keyword to search for the database.

      Note

      You must have the permissions to perform data changes on the required database. For more information, see View owned permissions.

      Execution Method

      Yes

      The execution method of the ticket. Valid values:

      • Ticket Submitter Executes Upon Approval

      • Automatically Execute Upon Approval

      • Last Approver Executes

      Note

      If you are a DMS administrator, you can modify execution methods on the Configuration Management page of the O&M module. For more information, see Configuration management.

      Affected Rows

      Yes

      The estimated number of data rows that may be affected by the data change operation. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements and execute the SQL statements on the SQLConsole tab.

      Note

      If data is continuously written, count the number of existing data rows.

      SQL Statements for Change

      Yes

      Enter the DML statements in the field, such as UPDATE, DELETE, or INSERT_SELECT statements.

      Note

      You can also enter DDL statements to perform lock-free schema changes. For more information, see Perform lock-free DDL operations.

      SQL Text

      No

      This parameter is available only if you set the SQL Statements for Change parameter to Text. Enter the SQL statements to be executed in the SQL editor.

      Note

      • Separate multiple SQL statements with semicolons (;).

      • If you set the Change Target parameter to Data Source Instance, you must add the database name in the ${dbName}.${tableName} format before all table names in the SQL statement.

      • DMS checks whether the syntax of the SQL statement is valid when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.

      Attachment

      No

      This parameter is available only if you set the SQL Statements for Change parameter to Attachment. Upload the attachment that contains the SQL statements executed for data change.

      Note

      The file that you want to upload can be a TXT, ZIP, or SQL file. The size of the file cannot exceed 15 MB.

      SQL Statements for Rollback

      No

      The SQL statements that can be executed to roll back the data change.

      SQL Text

      No

      This parameter is available only if you set the SQL Statements for Rollback parameter to Text. Enter the SQL statements that can be executed to roll back the data change.

      Attachment

      No

      This parameter is available only if you set the SQL Statements for Rollback parameter to Attachment. Click Upload a file to upload the attachment that contains the SQL statements executed for rollback.

      Note

      The file that you want to upload can be a TXT, ZIP, or SQL file. The size of the file cannot exceed 15 MB.

      Change Stakeholder

      No

      The stakeholders involved in the data change. All the specified stakeholders can view the ticket details and participate in the approval process. Irrelevant users, except for DMS administrators and database administrators (DBAs), have no access to the ticket details.

      Attachments

      No

      The file used as an attachment of the ticket to provide additional information about the data change.

    4. Click Submit. After you submit the ticket, you can enable master-slave delay check, specify a threshold, and modify the SQL statements.

      • Optional. Enable master-slave delay check and specify a threshold. This prevents the primary/secondary switchover from being affected by high latency in synchronization between primary and secondary databases.

        In the Basic Information section of the ticket details panel, click chunk option. In the chunk option dialog box, set an appropriate value for the master-slave delay threshold (seconds) parameter. If the latency in synchronization between primary and secondary databases exceeds the threshold, DMS suspends the SQL execution.

        Note

        Only ApsaraDB RDS for MySQL databases support the master-slave delay check feature.

      • Optional. Modify the SQL statements.

        After you submit the ticket, DMS automatically prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification in the Precheck step to modify the SQL statements and try again.

    5. Click Submit for Approval. Tickets for instances that are managed in Security Collaboration mode are approved based on the specified approval rules. Tickets for instances that are managed in Stable Change mode are automatically approved.

    6. After the ticket is approved, click Execute Change in the Execute step.

      Note

      You can view the approval progress in the Approval step in the ticket details panel.

    7. Configure the parameters that are described in the following table for the task.

      Parameter

      Description

      Execution Strategy

      • Running immediately: This is the default value. After you click Confirm Execution, the task is immediately run.

      • Schedule: If you select this option, you must specify the start time for the task. After you click Confirm Execution, the task is run at the specified point in time.

      Note

      The actual execution time may have a deviation of ±1 minute from the specified execution time.

      Specify End Time

      Specifies the time when the task ends. The system stops the task at the specified end time regardless of whether the task is complete. This prevents the task from affecting your business during peak hours.

      Note

      The actual task end time may have a deviation of ±1 minute from the specified end time.

      Primary/Secondary Node Check

      You can enable this feature to continuously maintain data synchronization between primary and secondary instances, providing high availability and quick fault recovery.

      Canary Release Type

      The policy to execute the SQL statements. Valid values:

      • No Canary Release: DMS automatically executes all SQL statements in the task.

        Suspend after Executing the First SQL Statement: After the first SQL statement is executed successfully, DMS automatically pauses SQL execution. If you need to continue, click Retry. Then, DMS executes remaining SQL statements immediately without further pauses.

        Suspend after Executing an SQL Statement: DMS pauses after the execution of each single SQL statement. You need to manually click Retry to execute the next SQL statement.

    8. Click Confirm Execution.

      Note

      A suspended task can be restarted.

      You can view the status and details of the task in the Execute step. You can also click Scheduling Logs in the Actions column to view the scheduling logs of the task.

    References

    FAQ

    Q: Does the lock-free DML feature support complex SQL statements?

    A: No. The lock-free DML feature does not support complex SQL statements such as CREATE_INDEX or operations on multiple tables such as a DELETE statement that includes JOIN operations. For more information about the supported SQL statements, see the Limits section of the "Overview" topic.