Perform lock-free data change - Data Management - Alibaba Cloud Documentation Center

When a large amount of data is stored, you must separate production data from archived data or regularly delete data from tables. When you change data in a table, Data Management (DMS) provides the feature to change data without the need to lock tables. You can change data in batches based on the primary key or a unique key that does not accept null values in a table. This feature ensures high execution efficiency and minimizes the impact on database performance and storage.

Usage notes

You can use the lock-free data change feature to change data only in a single table by executing simple SQL statements, such as SELECT, UPDATE, and DELETE. This feature cannot be used to change data in multiple tables by executing complex SQL statements, such as a DELETE statement that contains JOIN operations.

Procedure

The following procedure describes how to change all the data in the long_text_b column in the big_table table to random long text.

Submit a ticket as a regular user.

Log on to the DMS console V5.0 as a regular user.
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 DMS console and choose All functions > Database Development > Data Change > Lockless change.

Configure the parameters and click Submit.

The following table describes some of the parameters.

Parameter	Required	Description
Database	Yes	The database in which you want to change data. In this example, the `poc_dev` database is selected.
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 following `UPDATE` statement. This statement is used to change all the data in the `long_text_b` column to `random long text` in the `big_table` table. UPDATE `big_table` SET `long_text_b` = 'random long text' WHERE id < 1000000;
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 for rollback. The SQL statements that you specify for this parameter are used 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) do not have access to the ticket details.

On the ticket details page, click chunk option. In the dialog box that appears, configure the following parameters and click Submit Change.
Confirm the ticket details.
Note
You can view the ticket details on the Ticket Details page. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to be scanned.
After the ticket details are confirmed and the precheck is complete, click Submit for Approval. In the message that appears, click OK.
Important
You can change the ticket details only before you submit the ticket for approval.

Approve and execute the ticket as a DMS administrator.
1. Log on to the DMS console V5.0 as a DMS administrator.
2. On the Homepage of the DMS console, click Pending Tickets in the My Tickets section.
3. On the My Tickets page, find the ticket that you want to handle and click the ticket number in the Ticket Number column.
4. In the Ticket Details panel, confirm the data change information in the ticket and click Approve.
5. In the Approval dialog box, enter comments and click Submit.
6. In the Execute step, click Execute Change. In the dialog box that appears, set the Execution Strategy parameter to Running immediately and click Confirm Execution.
7. After you click Confirm Execution, DMS starts to run the task.
  Note
  The default value of the Execution Strategy parameter is Running immediately. You can also set the Execution Strategy parameter to Schedule to select a ticket execution time.
  If you turn on Specify End Time, DMS stops executing SQL statements at the end time that you specified. This prevents SQL statements from affecting your business during peak hours.
8. In the Execute step, click Details. In the dialog box that appears, click Execution Progress to view the progress.
Verify that the data is updated as an administrator.
1. Log on to the DMS console V5.0 as a DMS administrator.
2. In the left-side instance list, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.
3. On the SQLConsole tab, enter the following SQL statement in the SQL editor and click Execute in the upper part of the SQL editor. All data in the long_text_b column is updated.
```
SELECT * FROM `big_table`;
```