How to configure SQL execution control - Data Management

Configure SQL execution policies and lock-free schema change runtime parameters within a security rule set. These controls protect database stability by limiting concurrent load, preventing lock contention, and reducing replication lag caused by high-frequency SQL activity.

Prerequisites

Before you begin, make sure that you have:

The DMS administrator or database administrator (DBA) role. For details, see View system roles
A database instance managed in Secure Collaboration mode. For details, see View the control mode of an instance

Configure SQL execution controls

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

Note
If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
Go to Security Rules: hover over the icon in the upper-left corner and choose All functions > Security and Specifications > Security Rules.

Note If you use the DMS console in normal mode, choose Security and Specifications > Security Rules in the top navigation bar.
Find the security rule set to modify and click Edit in the Actions column.
In the left-side pane of the Details page, click the SQL Execute Control tab.
(Optional) Set the Checkpoints parameter to Basic Configuration Item to filter the list to essential configuration items.
Find the configuration item to modify and click Edit in the Actions column.

SQL execution configuration items

The following configuration items control how DMS manages SQL execution load and timing on your database.

Database lock timeout mechanism

Default behavior: Enable Protection Mechanism If Lock Wait Times Out is on. When a lock wait times out, DMS triggers the protection mechanism to prevent SQL execution from being blocked.

Target resource concurrency control

Default behavior: Autonomous System Adaptation is on. DMS automatically limits the number of SQL statements executing concurrently.

Database load check

Default behavior: Enable Thread Protection Mechanism is on. If the database load is too high, DMS suspends SQL execution.

Configure the following parameters to tune the load threshold and retry behavior:

Parameter	Description
Maximum Running Threads	The thread count threshold above which DMS suspends SQL execution
Retry Interval (Seconds)	How long DMS waits between retries after suspending execution
Maximum Retries	The maximum number of retry attempts before DMS stops retrying

Sleep policy after SQL execution

Default behavior: Autonomous System Adaptation is on. This prevents latency in synchronization between primary and secondary databases and in the synchronization channels of Data Transmission Service (DTS). The latency can be caused by frequent SQL execution.

Lock-free schema change parameters

Note In most cases, DMS automatically runs lock-free schema changes in the optimal and safest way — you do not need to modify these runtime parameters manually. Before changing any parameter, hover over the

icon next to it to read the description.

To control lock-free schema change behavior, turn on the switch for a runtime parameter and set its value.

Runtime parameter	Description
Table Lock Timeout Period During Table Switchover (Seconds)	How long DMS waits to acquire a table lock before the switchover times out
Retries	The number of times DMS retries a failed table switchover
Full Copy Policy and Size	The policy and chunk size used when copying table data
Time Window for Table Switchover	The allowed time window within which DMS performs the table switchover
Cleanup Policy After Table Switchover	How DMS handles the original table after a successful switchover

References

Create a security rule