Data Management (DMS) provides the lock-free DML feature. You can use this feature to change data without the need to lock tables. This topic describes how to perform a lock-free DML operation in DMS.

Prerequisites

  • The database is of one of the following types:
    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, and MySQL databases from other sources
    • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, and PostgreSQL databases from other sources
    • MariaDB: ApsaraDB for MariaDB TX and MariaDB databases from other sources
    • ApsaraDB for OceanBase in MySQL mode
    • PolarDB for 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.

Background information

You can use the lock-free DML feature of DMS to split the data on which a single SQL statement is to be executed into multiple batches and execute the SQL statement on each batch. This way, you can ensure execution performance and reduce the impact of the SQL statement on database performance or database space. This feature is especially useful when you need to change a large amount of data. For example, you can use this feature to clear historical data or update all fields in a table. For more information, see Overview.

Procedure

  1. Go to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Lockless chan
  3. On the Data Change Ticket Application page, set the parameters described in the following table.
    Parameter Description
    Database 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.
    SQL Statements for Change Enter 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.
  4. Click Submit.
    DMS prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification to modify the SQL statements and try again.
  5. After the ticket is approved, click Execute Change in the Execute step.
    Note On the Ticket Details page, you can view the approval progress in the Approval step.
  6. Set the parameters 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.
    End Time
    • Enable: Specify 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.
    • Disable: This is the default value.
  7. Click Confirm Execution.
    You can view the execution status, settings, and details of the task in the Execute step. You can also view the scheduling logs.