Data Management (DMS) provides the lock-free DML feature. You can use this feature to change data without
the need to lock tables. This topic describes how to perform a lock-free DML operation
in DMS.
Prerequisites
- The database is of one of the following types:
- MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, and MySQL databases from other sources
- PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, and PostgreSQL databases from other sources
- MariaDB: ApsaraDB for MariaDB TX and MariaDB databases from other sources
- ApsaraDB for OceanBase in MySQL mode
- PolarDB for Oracle
- The database instance is managed in Stable Change or Security Collaboration mode in
DMS. For more information, see View the control mode of an instance.
Background information
You can use the lock-free DML feature of DMS to split the data on which a single SQL
statement is to be executed into multiple batches and execute the SQL statement on
each batch. This way, you can ensure execution performance and reduce the impact of
the SQL statement on database performance or database space. This feature is especially
useful when you need to change a large amount of data. For example, you can use this
feature to clear historical data or update all fields in a table. For more information,
see Overview.
Procedure
- Go to the DMS console V5.0.
- In the top navigation bar, click Database Development. In the left-side navigation pane, choose
- On the Data Change Ticket Application page, set the parameters described in the following
table.
Parameter |
Description |
Database |
Select the database from the Database drop-down list. You can also enter a keyword
to search for the database.
Note You must have the permissions to perform data changes on the required database. For
more information, see View owned permissions.
|
SQL Statements for Change |
Enter DML statements in the field, such as UPDATE , DELETE , or INSERT_SELECT statements.
|
- Click Submit.
DMS prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification to modify the SQL statements and try again.
- After the ticket is approved, click Execute Change in the Execute step.
Note On the Ticket Details page, you can view the approval progress in the Approval step.
- Set the parameters for the task.
Parameter |
Description |
Execution Strategy |
- Running immediately: This is the default value. After you click Confirm Execution, the task is immediately run.
- Schedule: If you select this option, you must specify the start time for the task. After you
click Confirm Execution, the task is run at the specified point in time.
|
End Time |
- Enable: Specify the time when the task ends. The system stops the task at the specified
end time regardless of whether the task is complete. This prevents the task from affecting
your business during peak hours.
- Disable: This is the default value.
|
- Click Confirm Execution.
You can view the execution status, settings, and details of the task in the Execute step. You can also view the scheduling logs.