How to use the lockless data change feature - Data Management

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

This example shows how to use the lock-free data change feature to change the data in the long_text_b field of the big_table table to 'random long text' in batches.

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 console and choose All Features > 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	Select the `poc_dev` database.
Business Background	No	Enter the background information for your business requirements to reduce communication costs.
Affected Rows	Yes	Estimate the number of data rows that this change will affect. You can use the `count` command in the SQL Console to get the statistics.
SQL Statements for Change	Yes	Enter the following `UPDATE` statement. This statement changes the data in the `long_text_b` field of the `big_table` table to `random long text` in batches. UPDATE `big_table` SET `long_text_b` = 'random long text' WHERE id < 1000000;

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 list of database instances, expand the POC_dev instance, and double-click the poc_dev database to open the SQL Console page.
3. In the SQL Console window, enter the following search statement and click Execute. The results show that the data in the long_text_b field has been updated.
```
SELECT * FROM `big_table`;
```