All Products
Search
Document Center

Data change without locking

Last Updated: May 29, 2020

Background

When a large amount of business data is accumulated, you need to separate production data from historical data for management, or regularly delete stale data. During data processing, the following two problems often occur:

  • Too many rows are affected by a single SQL statement. In this case, the number of logs may exceed the upper limit. As a result, the SQL statement fails.
  • An SQL statement does not use indexes. In this case, tables may be locked. This increases the database load and even causes business failure.

Previously, a large amount of data is processed in batches. However, such processing schemes are accompanied with risks. Scheme examples and their risks are described as follows:

  • Scheme: Developers process data in batches. Risk: Tables may be locked if developers separate data by using an inappropriate method, such as using the LIMIT clause. Also, the latency in synchronization between the primary and secondary databases may be high if the interval of batch processing is too short. In such cases, the database and the business may be affected.
  • Scheme: Database administrators (DBAs) separate data into multiple parts for batch processing. Risk: It is a cumbersome, heavy, and error-prone work to manually separate data. In addition, it is difficult to dynamically adjust some settings.

Against this background, Data Management Service (DMS) offers the following feature: changing data without locking tables. This feature can be used to change a large amount of data at a time, such as deleting historical data and updating all fields in a table, without affecting the business. This is because this feature guarantees efficient SQL execution and limits the consumption of database performance and space.

Important notes

  • You cannot specify SQL transactions.
  • You cannot back up data in images before the data change.
  • You cannot duplicate a ticket for changing data without locking tables for reuse.

Procedure

  1. Log on to the DMS console.

  2. In the top navigation bar, choose Data Plans > Data Changes > Lock-Free Data Modify.

  3. On the page that appears, set the following parameters to create a data change ticket:

    • Reason Category: the reason for data change, which can help you find the ticket.
    • Business Background: the detailed change reason or goal, which can reduce communication costs.
    • Change Stakeholder: the stakeholders of the data change. The stakeholders can view the data change ticket and assist in submitting the ticket and performing the data change. Except for the DMS administrator and DBA, users irrelevant to the ticket are not allowed to view the ticket details.
    • Database: the database on which you have the change permission. You cannot submit a data change ticket if you only have the permission to query databases or change data in tables.
    • SQL Statements for Change: the executable SQL statements for changing data. DMS automatically checks whether the syntax of the SQL statements is correct when you submit the ticket. If the syntax is incorrect, you cannot submit the ticket.
    • SQL Statements for Rollback: the executable SQL statements for rolling back the data change performed by the SQL statements for changing data. You can enter the SQL statements or upload SQL files that are in the .txt or .sql format.
    • Attachments: You can upload images or files to add more information about the data change.Data change ticket
  4. Click Submit. After your ticket passes the precheck, click Submit for Approval. In the Prompt dialog box that appears, click OK.

    You can specify approvers as required in the approval process. DMS allows you to apply approval processes to objects down to the instance level.

  5. After your ticket is approved, click Execute Change. In the dialog box that appears, select the execution time of the SQL statements for data change and click Confirm Execution.

    • By default, the SQL statements are executed immediately. You can also specify the start time for executing these statements.
    • If you specify the end time for executing these statements, the execution stops at the end time and does not start again. This prevents the data change from affecting your business during peak hours.
  6. Wait until the execution is completed.

    • The execution sleeps for 0.5 second after every 10,000 data entries that are to be changed are retrieved.
    • By default, 1,000 data entries are changed at a time based on the primary key or the unique index. If the data change fails, DMS automatically lowers the number of data entries to be changed at a time. When no data satisfies the conditions specified in SQL statements, the data change is completed.