Intercepting Write Transactions During the Optimization Phase

Introduction

In MySQL, if a single transaction involves a large number of data operations (such as inserting, updating, or deleting millions of rows), it may hold locks for extended periods and heavily consume I/O resources, severely affecting the normal execution of subsequent operations. Users often have to forcibly terminate the session, which will trigger rollback operations. The rollback typically takes as long as the original transaction execution. During this process, the database locks are continuously held and I/O resources are still occupied, further blocking other workloads.

To avoid such performance bottlenecks, Huawei Cloud RDS for MySQL intercepts large-scale write transactions before they are executed, preventing the system performance from deteriorating due to excessive data changes. This feature promptly alerts you to potential risks, helping ensure database stability and high availability. However, enabling this feature may slightly affect write performance.

Prerequisites

• For RDS for MySQL 5.7, the kernel version must be 5.7.44.241207 or later.
• For RDS for MySQL 8.0, the kernel version must be 8.0.41.250900 or later.

Parameters

Restrictions

Only statement-level restrictions are supported. Risks are identified before the following statements are executed:

As SQL statements are not executed, the system can only rely on the optimizer's estimated values for interception. The interception cannot be absolutely accurate. However, the accurate row count can be obtained in the following scenarios:

• The INSERT ... VALUES operation is performed on a table without any restriction.
• In the INSERT/REPLACE ... SELECT or CREATE TABLE ... SELECT statement, the SELECT results need to be cached (for example, materialized temporary tables and SORT cache) before being inserted.
• Before the UPDATE or DELETE statement is executed, the row IDs of all rows to be updated need to be cached (for example, temporary files and SORT cache).
• In a single-table query, indexes (such as ref or range) can be directly used for access, without extra filtering.

In the preceding scenarios, regardless of whether the PRECISE or ESTIMATED mode is configured, the system determines whether to intercept the statements based on the obtained number of rows.

Examples

The following are examples of intercepting write transactions in the optimization phase:

1. Table structure

   mysql> SHOW CREATE TABLE t1\G
   *************************** 1. row ***************************
          Table: t1
   Create Table: CREATE TABLE `t1` (
     `id` int NOT NULL AUTO_INCREMENT,
     `category` int NOT NULL,
     `value` int NOT NULL,
     `flag` enum('active','inactive') DEFAULT 'active',
     `created_at` timestamp NOT NULL DEFAULT '2025-05-05 01:01:01',
     PRIMARY KEY (`id`),
     KEY `idx_category` (`category`),
     KEY `idx_value` (`value`),
     KEY `idx_flag` (`flag`),
     KEY `idx_category_value` (`category`,`value`)
   ) ENGINE=InnoDB AUTO_INCREMENT=120109 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   1 row in set (0.00 sec)
    
   mysql> CREATE TABLE t2 LIKE t1;
   Query OK, 0 rows affected (0.15 sec)

2. Example of intercepting the INSERT ... SELECT statement

   mysql> EXPLAIN FORMAT=TREE INSERT INTO t2 SELECT * FROM t1 WHERE category=1;
   +------------------------------------------------------------------------------------------------------+
   | EXPLAIN                                                                                              |
   +------------------------------------------------------------------------------------------------------+
   | -> Insert into t2
       -> Index lookup on t1 using idx_category (category=1)  (cost=6276 rows=60112)
    |
   +------------------------------------------------------------------------------------------------------+
   1 row in set (0.02 sec)
    
   mysql> SET rds_max_data_mutation_num_per_dml=60111;
   Query OK, 0 rows affected (0.00 sec)
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1;
   ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in PRECISE mode.
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1;
   ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   mysql> EXPLAIN FORMAT=TREE INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active';
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | EXPLAIN                                                                                                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | -> Insert into t2
       -> Filter: (t1.flag = 'active')  (cost=3270 rows=30056)
           -> Index lookup on t1 using idx_category (category=1)  (cost=3270 rows=60112)
    |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
    
   mysql> SET rds_max_data_mutation_num_per_dml=30055;
   Query OK, 0 rows affected (0.00 sec)
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active';
   ERROR 4167 (HY000): The number [30056] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is not intercepted in PRECISE mode.
   mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active';
   Query OK, 32275 rows affected (37.11 sec)
   Records: 32275  Duplicates: 0  Warnings: 0
    
   mysql>

3. Example of intercepting the UPDATE statement

   mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=1;
   +--------------------------------------------------------------------------------+
   | EXPLAIN                                                                        |
   +--------------------------------------------------------------------------------+
   | -> Index lookup on t1 using idx_category (category=1)  (cost=4710 rows=44448)
    |
   +--------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
    
   mysql> SET rds_max_data_mutation_num_per_dml=44447;
   Query OK, 0 rows affected (0.00 sec)
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> UPDATE t1 SET value=value+100 WHERE category=1;
   ERROR 4167 (HY000): The number [44448] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in PRECISE mode.
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> UPDATE t1 SET value=value+100 WHERE category=1;
   ERROR 4167 (HY000): The number [44448] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
   mysql>  EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=1 AND flag='active';
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | EXPLAIN                                                                                                                                    |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | -> Filter: (t1.flag = 'active')  (cost=2487 rows=22224)
       -> Index lookup on t1 using idx_category (category=1)  (cost=2487 rows=44448)
    |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.01 sec)
    
   mysql>
   mysql> SET rds_max_data_mutation_num_per_dml=22223;
   Query OK, 0 rows affected (0.00 sec)
    
   mysql>
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> UPDATE t1 SET value=value+100 WHERE category=1 AND flag='active';
   ERROR 4167 (HY000): The number [22224] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is not intercepted in PRECISE mode.
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> UPDATE t1 SET value=value+100 WHERE category=1 AND flag='active';
   Query OK, 32275 rows affected (43.55 sec)
   Rows matched: 32275  Changed: 32275  Warnings: 0

4. Example of intercepting the DELETE statement

   mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=2;
   +--------------------------------------------------------------------------------+
   | EXPLAIN                                                                        |
   +--------------------------------------------------------------------------------+
   | -> Index lookup on t1 using idx_category (category=2)  (cost=6276 rows=60112)
    |
   +--------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
    
   mysql>
   mysql> SET rds_max_data_mutation_num_per_dml=60111;
   Query OK, 0 rows affected (0.00 sec)
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> DELETE FROM t1 WHERE category=2;
   ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   # If a single table uses the ref index without filter criteria and the estimated value exceeds the threshold, the statement is intercepted in PRECISE mode.
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> DELETE FROM t1 WHERE category=2;
   ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
   mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=2 AND flag='active';
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | EXPLAIN                                                                                                                                    |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | -> Filter: (t1.flag = 'active')  (cost=3270 rows=30056)
       -> Index lookup on t1 using idx_category (category=2)  (cost=3270 rows=60112)
    |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.12 sec)
    
   mysql> SET rds_max_data_mutation_num_per_dml=30055;
   Query OK, 0 rows affected (0.00 sec)
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is intercepted in ESTIMATED mode.
   mysql> SET rds_data_mutation_counting_mode='ESTIMATED';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> DELETE FROM t1 WHERE category=2 AND flag='active';
   ERROR 4167 (HY000): The number [30056] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'.
   mysql>
    
   # If there are filter criteria that cannot be indexed and the estimated value exceeds the threshold, the statement is not intercepted in PRECISE mode.
   mysql> SET rds_data_mutation_counting_mode='PRECISE';
   Query OK, 0 rows affected (0.00 sec)
    
   mysql> DELETE FROM t1 WHERE category=2 AND flag='active';
   Query OK, 32040 rows affected (41.72 sec)