Lock-free DML operation

Limitations

Only UPDATE, DELETE, and INSERT...SELECT statements are supported.

UPDATE and DELETE

INSERT...SELECT

Procedure

1. Log in to DMS 5.0.

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

   Note

   If you are using the DMS console in simple mode, click the icon in the upper-left corner. In the navigation pane that appears, choose All Features > Database Development > Data Change > Lockless Change.

3. Configure the parameters for the Lockless Change ticket and click Submit. The following table describes some of the parameters.

   Note

   This example shows the configuration for an instance in Security Collaboration mode. For instances in Flexible Management or Stable Change mode, you need to configure only the Database and SQL statements for change parameters.

   Parameter	Required	Description
   Database	Yes	Search for and select the target database. Note You must have permissions to change data in the target database. For more information, see View your permissions.
   Business Background	No	Provide context for the change to reduce communication overhead.
   Execution Method	Yes	Select an execution method for the ticket: Ticket Submitter Executes Upon Approval Automatically Execute Upon Approval Last Approver Executes Note Administrators can modify the list of execution methods in O&M > Configuration Management. For more information, see Configuration Management.
   Affected rows	Yes	Estimate the number of affected rows. You can run a COUNT query in the SQL Console to get this number. Note If data is continuously written to the table, count only the number of existing data rows.
   SQL Statements for Change	Yes	Enter the DML statements, such as UPDATE, DELETE, or INSERT_SELECT. Note If you enter DDL statements, you are performing a DDL lock-free schema change task. For more information, see Perform a lock-free schema change by using a Lockless Change ticket.
   SQL Statements for Rollback	No	A reverse script for the statements in SQL statements for change. The script must be executable.
   SQL Text	No	This parameter appears only if you select Text for the Rollback SQL parameter. Enter the rollback SQL statements, which are the reverse of the change SQL statements.
   Attachment	No	This parameter appears only if you select Attachment for the SQL Statements for Rollback parameter. Click File to upload the rollback SQL attachment. Note Only .txt, .zip, and .sql files are supported. The file size cannot exceed 15 MB.
   Change Stakeholder	No	Specified stakeholders can view and collaborate on the ticket. Non-stakeholders (except for administrators and DBAs) cannot view it.
   Attachments	No	Upload attachments to provide more context for this ticket.

4. After you submit the ticket, you can enable the primary-secondary latency check, set a threshold, and modify the SQL statements.

   • (Optional) Enable the primary-secondary latency check and set a threshold. This prevents excessive primary-secondary latency from affecting instance switchovers.

     In the Basic Information section, click chunk option, and set a reasonable primary-secondary latency threshold in seconds. If the primary-secondary latency exceeds the threshold, the SQL execution is interrupted.

     Note

     This feature is currently available only for ApsaraDB RDS for MySQL databases.

   • (Optional) Modify the SQL statements.

     After you submit the application, the system automatically pre-checks the SQL statements. If the pre-check fails, you can click Edit SQL, edit the statements based on the failure reason, and then retry.

5. Click Submit. Tickets for instances in Security Collaboration mode require approval based on configured rules, while tickets for instances in Stable Change mode are approved automatically.

6. After the ticket is approved, click Execute Change in the Execute section of the ticket details page.

   Note

   You can view the approval progress in the Approval section of the ticket details page.

7. Configure the task execution parameters.

   Configuration Item	Description
   Execution Strategy	Running immediately: The default option. The task is executed immediately after you click Confirm Execution. Schedule: Select a start time for the task. After you click Confirm Execution, the task runs at the specified time. Note The actual execution time of a scheduled task may have a margin of error of ±1 minute.
   Specify End Time	Specify an end time for the task. If the task is not completed by the specified end time, the system stops executing the remaining SQL tasks. This prevents tasks from running during peak hours and affecting business operations. Note The actual end time of the task may have a margin of error of ±1 minute.
   Primary/Secondary Node Check	Enabling this check ensures real-time data synchronization between the primary and standby instances, high availability, and fast recovery from failures.
   Canary Release Type	The policy for executing SQL statements in batches. 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 successfully executed, DMS automatically pauses the task. To continue, click Retry. The remaining SQL statements are then executed at once without further pauses. Suspend after Executing a SQL Statement: The task pauses automatically after each SQL statement is executed. You must manually click Retry to execute the next SQL statement.

8. Click Confirm Execution.

   Note

   When a paused task is restarted, it starts over from the beginning.

   In the Execute section, you can view the task execution status, task settings, details, and Scheduling Logs.

Related documents

• After you perform a lock-free data change, you may also need to perform the following operations:

  • Query table data in the SQL window. For more information, see Query data.

  • Export data from your database. For more information, see Export databases.

  • Export SQL result sets. For more information, see Export SQL result sets.

• The following API operations are related to lock-free data changes:

  • CreateFreeLockCorrectOrder

  • ModifyDataCorrectExecSQL

  • PauseDataCorrectSQLJob

  • GetDataCorrectTaskDetail

  • GetDataCorrectOrderDetail

FAQ

• Q: Does the lock-free data change feature support complex SQL operations?

  A: No. For example, it does not support CREATE_INDEX or multi-table operations such as a DELETE statement that includes a JOIN. For more information about supported SQL types, see Limitations.

• Q: Does DMS Lockless Change detect table locks and blocking?

  A: When DMS performs a lockless data change, it splits large SQL statements into smaller batches and pauses after each batch. This avoids occupying resources for a long time. During execution, DMS does not perform row lock detection in advance. MySQL still locks the modified rows. The overall impact is minimal because of the small batches and pauses, but this cannot guarantee that the table will not be locked. For a lockless schema change, DMS uses a temporary table to simulate your DDL statement. This pre-checks whether the operation will cause table locking or blocking. If the check determines that the operation will lock the table, the verification fails and a message is displayed.

• Q: Can the incremental playback verification for a DMS Lockless Change task be adjusted?

  A: Yes. First, on the O&M > Configuration Management page, search for and enable the "Allow adjustment of the playback data verification ratio for lockless change results" feature.

  1. Go to the corresponding DDL ticket in the console.

  2. In the execution section, click Details.

  3. Find the corresponding script and click Execution Progress.

  4. The verification ratio is displayed at the bottom. Adjust the ratio as needed. The unit is per mille (‰).