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 for Xscale, and third-party MySQL databases
- PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, and third-party PostgreSQL databases
- MariaDB: ApsaraDB for MariaDB and third-party MariaDB databases
- ApsaraDB for OceanBase in MySQL mode
- PolarDB for PostgreSQL(Compatible with 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.
Usage notes
The lock-free DML feature supports only operations on a single table and simple SQL statements such as UPDATE and DELETE. This feature does not support complex SQL statements such as CREATE_INDEX and operations on multiple tables. For example, the lock-free DML feature does not support a DELETE statement that includes JOIN operations. For more information about supported SQL types, see Limits.
Procedure
- Log on to the DMS console V5.0.
In the top navigation bar, click Database Development. In the left-side navigation pane, choose .
NoteIf you use the DMS console in simple mode, move the pointer over the
icon in the upper-left corner and choose . On the page that appears, set the parameters described in the following table.
NoteIn this example, the instance managed in Security Collaboration mode is used. If you configure a lock-free change ticket for an instance managed in Flexible Management or Stable Change mode, only the Database and SQL Statements for Change parameters are required.
Parameter
Required
Description
Database
Yes
The database on which you want to perform data changes. Select the database from the Database drop-down list. You can also enter a keyword to search for the database.
NoteYou must have the permissions to perform data changes on the required database. For more information, see View owned permissions.
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
NoteIf you are a DMS administrator, you can modify execution methods on the Configuration Management page of the module. For more information, see Configuration management.
SQL Statements for Change
Yes
Enter the DML statement in the field, such as
UPDATE,DELETE, orINSERT_SELECTstatement.NoteYou can also enter DDL statements to perform lock-free schema changes. For more information, see Perform lock-free DDL operations
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.
NoteSeparate 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.
NoteThe 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
Optional. 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 executed for rollback.
NoteThe 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.
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.
NoteOn 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.
NoteA suspended task can be restarted.
You can view the execution status, settings, and details of the task in the Execute step. You can also view the scheduling logs.