Data Management:Change data without locking tables

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.

1. Submit a ticket as a regular user.
   1. Log on to the DMS console V5.0 as a regular user.
   2. In the top navigation bar, choose Database Development > Data Change > Lockless change.
   3. Configure the parameters that are described in the following table and click Submit.

      Parameter	Required	Description
      Database	Yes	The database in which you want to change data. In this example, select the poc_dev database.
      Associated Iterations	No	The iteration to be associated with the project. To select an iteration, click Select/Change Iteration. In the dialog box that appears, find the desired iteration and click Associate in the Actions column.
      Reason Category	Yes	The reason for the change operation. This helps you find the ticket in subsequent operations.
      Business Background	Yes	The purposes or objectives of the change operation. This reduces unnecessary communication.
      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 reason categories 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 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 used for data change. Note The file to upload 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 that can be executed 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 used for rollback. Note The file to upload 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 change operation. 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) have no access to the ticket details.
      Attachments	No	The file used as an attachment of the ticket to provide additional information about the change.

   4. On the ticket details page, click chunk option. In the dialog box that appears, configure the following parameters and click Submit Change.

      Parameter	Description
      chunk size(rows)	The number of rows that are processed at a time. Default value: 1000.
      sleep seconds per 10000 rows	The interval at which the execution of SQL statements pauses after every 10,000 rows are processed. Unit: seconds. Default value: 0.5.
      Is enable master-slave delay check	Specifies whether to check the latency in primary-secondary replication.
      master-slave delay threshold (seconds)	The maximum synchronization latency between the primary and secondary databases. Unit: seconds. Default value: 10.
      is enable pruning optimization	Specifies whether to enable pruning optimization.

   5. 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 scan.
   6. 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.
      
2. Approve and handle the ticket as a DMS administrator.
   1. Log on to the DMS console V5.0 as a DMS administrator.
   2. On the Home page 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.

      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 when the end time that you specified arrives. This prevents SQL statements from affecting your business during peak hours.
   7. In the Execute step, click Details. In the dialog box that appears, click Execution Progress to view the progress.
3. Verify that the data is updated as a DMS administrator.
   1. Log on to the as a DMS administrator.
   2. In the 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`;