All Products
Search
Document Center

Data Management:Perform lock-free DML operations

Last Updated:Sep 11, 2023

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

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.

Usage notes

The lock-free DML feature supports only operations on a single table and simple SQL statements such as UPDATE and DELETE. This feature does not support complex SQL statements such as CREATE_INDEX and operations on multiple tables. For example, the lock-free DML feature does not support a DELETE statement that includes JOIN operations. For more information about supported SQL types, see Limits.

Procedure

  1. Log on 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 change.

    Note

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

  3. On the page that appears, set the parameters described in the following table.

    Note

    In this example, the instance managed in Security Collaboration mode is used. If you configure a lock-free change ticket for an instance 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:

    • After Audit Approved, Order Submitter Execute
    • After Audit Approved, Auto Execute
    • Last Auditor Execute
    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.

    SQL Statements for Change

    Yes

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

    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 (;).

    • DMS checks whether the syntax of the SQL statements 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 to upload can be a TXT, ZIP, or SQL file. The size of the file cannot exceed 15 MB.

    SQL Statements for Rollback

    No

    Optional. 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 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 change operation. 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 change.

  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.

    Note

    A suspended task can be restarted.

    You can view the execution status, settings, and details of the task in the Execute step. You can also view the scheduling logs.