All Products
Search

This Product

    Data Management:DML Lockless Change

    Document Center

    Data Management:DML Lockless Change

    Last Updated:Mar 30, 2026

    Lock-free DML splits a large UPDATE or DELETE into small batches and runs them sequentially, so the table stays available to other operations throughout the change. Use this feature when you need to modify a large number of rows without locking the table or affecting database performance.

    How it works

    When you submit a lock-free change ticket, DMS splits the target rows into batches based on primary key ranges and applies the SQL statement to each batch in sequence. Because each batch holds a lock only for its own brief execution window, other transactions can read and write the table between batches. You can control execution pacing with the canary release option, and set an end time to stop the task automatically before peak hours.

    Prerequisites

    Before you begin, ensure that you have:

    • A database of one of the following types:

      Database engine Supported instances
      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
      OceanBase ApsaraDB for OceanBase in MySQL mode
      Oracle-compatible PostgreSQL PolarDB for PostgreSQL (Compatible with Oracle)

    • A database instance managed in Stable Change or Security Collaboration mode in Data Management (DMS). For more information, see View the control mode of an instance.

    Limitations

    • Only single-table operations are supported.

    • Only UPDATE, DELETE, and INSERT_SELECT statements are supported. Complex statements such as CREATE_INDEX and multi-table operations such as DELETE with JOIN are not supported. For a full list, see the Limits section of the overview.

    • The master-slave delay check is available only for ApsaraDB RDS for MySQL instances.

    Submit a lock-free change ticket

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

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

      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. Configure the parameters and click Submit.

      The following example uses an instance managed in Security Collaboration mode. For instances managed in Flexible Management or Stable Change mode, only Database and SQL Statements for Change are required.
      Parameter Required Description
      Database Yes The database on which you want to perform the data change. Select from the Database drop-down list, or enter a keyword to search. You must have data change permissions on the selected database. For more information, see View owned permissions.
      Execution Method Yes Who runs the change after approval. Options: Ticket Submitter Executes Upon Approval, Automatically Execute Upon Approval, or Last Approver Executes. DMS administrators can modify available execution methods on the Configuration Management page in the O&M module. For more information, see Configuration management.
      Affected Rows Yes The estimated number of rows affected. To get the exact count, run a COUNT query on the SQLConsole tab before submitting. If data is being written continuously, count only the existing rows.
      SQL Statements for Change Yes The DML statement to execute: UPDATE, DELETE, or INSERT_SELECT. To perform lock-free schema changes instead, enter DDL statements. For more information, see Perform lock-free DDL operations.
      SQL Statements for Rollback No The SQL statements that can be executed to roll back the data change.
      SQL Text No Available only when SQL Statements for Rollback is set to Text. Enter the rollback SQL directly in the field.
      Attachment No Available only when SQL Statements for Rollback is set to Attachment. Upload a TXT, ZIP, or SQL file containing the rollback SQL. The file must not exceed 15 MB.
      Change Stakeholder No Other users who can view ticket details and participate in the approval process. Users not listed here (except DMS administrators and database administrators (DBAs)) cannot access the ticket.
      Attachments No Supporting files that provide additional context for the data change.

    4. After submitting, optionally configure precheck settings:

      • Enable master-slave delay check (ApsaraDB RDS for MySQL only): In the Basic Information section of the ticket details panel, click chunk option. In the dialog box, set the master-slave delay threshold in seconds. If the replication lag between primary and secondary databases exceeds the threshold, DMS suspends SQL execution to protect the primary/secondary switchover.

      • Modify SQL statements if the precheck fails: DMS runs a precheck automatically after submission. If the precheck fails, click SQL Statements for Modification in the Precheck step to update the statements and resubmit.

    5. Click Submit for Approval. To track approval progress, check the Approval step in the ticket details panel.

      • Security Collaboration mode: The ticket is routed through the configured approval rules.

      • Stable Change mode: The ticket is approved automatically.

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

    7. Configure the execution parameters and click Confirm Execution.

      Parameter Description
      Execution Strategy Running immediately (default): starts the task as soon as you click Confirm Execution. Schedule: starts the task at a specified time. Actual start time may differ by up to ±1 minute.
      Specify End Time Stops the task at the specified time regardless of whether it has completed. Use this to prevent the task from running during peak hours. Actual end time may differ by up to ±1 minute.
      Primary/Secondary Node Check Continuously monitors data synchronization between primary and secondary instances to maintain high availability.
      Canary Release Type Controls execution pacing: No Canary Release runs all batches automatically. Suspend after Executing the First SQL Statement pauses after the first batch — click Retry to continue. Suspend after Executing an SQL Statement pauses after each batch — click Retry to proceed to the next one.

    8. Monitor progress in the Execute step. Click Scheduling Logs in the Actions column to view detailed scheduling logs.

      A suspended task can be restarted at any time.

    Verify the change

    After the task completes, confirm the change applied correctly:

    1. Open the SQLConsole tab and run a query on the affected table to verify that the updated or deleted rows match your expectations.

    2. If you need to export the results, see Export databases or Export SQL result sets.

    FAQ

    • Does lock-free DML support complex SQL statements?

      No. The lock-free DML feature does not support complex SQL statements such as CREATE_INDEX or multi-table operations such as a DELETE statement that includes JOIN operations. For a complete list, see the Limits section of the overview.

    • Q: Does DMS lockless change detect locked tables or blocking?

      A: During a lockless data change, DMS splits a large SQL statement into multiple mini-batches for execution. After each batch, it pauses briefly to avoid prolonged resource occupation. During actual execution, it does not pre-check for row locks. MySQL still locks the modified rows, but the overall impact is minimal because of the 'mini-batch + pause' approach. It cannot guarantee 100% lock-free operation. For DMS lockless schema evolution, a temporary table simulates DDL statement execution to pre-check if the operation causes table locks or blocking. If a table lock is detected, the validation fails and a prompt appears.

    • Q: Does DMS lockless change task incremental replay validation support adjustment?

      A: Yes, it does. Validation adjustment is supported. First, on the Operations Management > Configuration Management page, search for and enable the 'Allow adjustment of lockless result change replay data validation ratio' feature.

      1. Access the corresponding DDL ticket through the console.

      2. In the execution section, click the Details button.

      3. Find the corresponding script and click Execution Progress.

      4. At the bottom, you will find a validation ratio. Adjust the validation ratio as needed. The unit is per mille (‰).

    What's next

    API reference

    The following API operations are related to lock-free change tickets: