Change data without locking tables - Data Management - Alibaba Cloud Documentation Center

When you run UPDATE or DELETE on a large table, a standard transaction locks the table until the operation finishes — blocking reads and writes and risking timeouts that disrupt production traffic. Lock-free data change avoids this by splitting the affected rows into small batches and processing each batch as a separate, short-lived transaction. Each batch commits and releases its lock immediately, so the table stays responsive throughout.

Precautions

Lock-free data change supports only single-table and simple SQL operations (such as SELECT, UPDATE, and DELETE). Multi-table operations (such as DELETE statements that contain JOIN) are not supported.

Procedure

This example uses the lock-free data change feature to change the data in the big_table column of the long_text_b table in batches to random long text。

A regular user submits a ticket.

Log on to Data Management DMS 5.0。
In the top navigation bar, choose Database Development > Data Change > Lockless change.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the console and choose All Features > Database Development > Data Change > Lockless Change.

Configure the required information and click Submit。

The following table describes some parameters.

Parameter	Required	Description
Database	Yes	Select `poc_dev<\/code>.<\/code>`
Business Background	No	Describe your business requirements to reduce back-and-forth with approvers.
Affected Rows	Yes	Estimate the number of rows this change will affect. Run a `COUNT<\/code> query in the SQL Console to get this number.<\/code>`
SQL Statements for Change	Yes	Enter the UPDATE or DELETE statement to execute.

In the Ticket Details page, click Batch Execution Configuration button, set the following parameters, and click Submit Change。
Confirm the ticket details.
Note
You can view the Ticket Details page to check the ticket details. The system also performs a precheck on the SQL statement to be executed, including SQL syntax parsing, SQL type verification based on security rules, permission verification of the ticket submitter, and estimated scan rows.
After confirming the information is correct and the precheck is passed, click Submit for Approval, and click Confirm。
Important
Before submitting for approval, you can modify the ticket content and schedule configuration. After the ticket is submitted for approval, the information cannot be modified.

The administrator approves and executes the ticket.
1. Log on to Data Management DMS 5.0。
2. On the DMS console homepage, in the My Tickets section, click Pending Tickets。
3. In the My Tickets page, click the target ticket number。
4. In the Ticket Details page, confirm the ticket change information, and click Approve。
5. Enter your comments in the approval comments text box, and click OK。
6. Click Execute Change, select the execution time, and click OK。
7. Click Confirm Execution, the system will automatically start the execution.
  Note
  The default is to execute immediately. You can also select a start execution time.
  If you specify an end time, the system will no longer execute unfinished SQL statements after the time arrives. This feature is mainly used to prevent impact on business during peak hours.
8. In the Execution section, click Details, and click Execution Progress to view the task execution progress.
The administrator verifies the task.
1. Log on to 。
2. In the instance list, expand the POC_dev<\/code> instance and double-click the poc_dev<\/code> database to open the SQL Console.<\/code>
3. In the SQL Console window, enter the following query statement and click Execute. You can see that the data in the long_text_b column has been updated.
```
SELECT * FROM `big_table`;
```