All Products
Search

This Product

    Data Management:Perform lock-free DML operations

    Document Center

    Data Management:Perform lock-free DML operations

    Last Updated:Jan 25, 2024

    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 > Lock-free 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 functions > Database Development > Data Change > Lock-free 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:

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

      • 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 be uploaded 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 to be uploaded 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: If you select this option, the task is immediately run after you click Confirm Execution. This is the default value.

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

      Specify End Time

      • on: 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.

      • off: This is the default value.

    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.