You can use the lock-free data manipulation language (DML) feature of Data Management (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, the execution performance can be ensured and the impact of the SQL statement on database performance or database storage can be reduced. 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 rows in a table.

Background information

As your business grows, business data accumulates. In this case, you may need to regularly clear table data or separately manage online data and historical data. The following issues may occur:
  • A single SQL statement affects a large number of data rows. In this case, the number of log entries exceeds the upper limit and the SQL statement fails to be executed.
  • An SQL statement is executed without specified indexes. This may cause table locking, increase database load, or even cause business failures.
To resolve these issues, you can divide the affected data into multiple batches and execute the SQL statement on each batch. However, this method is also accompanied by risks.
  • Developers write programs to divide data.
    • If an improper method is used to divide the data, the table may still be locked. For example, a developer may use the LIMIT clause to divide the data, which is improper.
    • In some cases, the interval for executing the different batches is not configured as expected. This causes great latency in synchronization between primary and secondary databases when data is changed.
  • Database administrators (DBAs) manually extract and divide data into multiple parts for batch processing.

    The process is complex and labor-intensive. In addition, this process is prone to errors and difficult to adjust.

To resolve the preceding issues, you can use the lock-free DML feature of DMS. For more information, see Perform lock-free DML operations.

Supported database 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)

Benefits

  • Your business is not affected.
  • The impact on database performance and database storage is reduced.
  • Execution efficiency is ensured for large amounts of data changes.

How it works

  • A single SQL statement is executed on different batches of data. The powerful DMS engine can split the data on which a single SQL statement is to be executed into multiple batches.
  • The execution enters a buffer period after the SQL statement is executed on each batch.

Limits

Only the UPDATE, DELETE, and INSERT SELECT statements are supported.

Statement typeDescription
UPDATE and DELETE
  • You can execute the statement only on a single table.
  • You must specify a WHERE clause in the statement. To update or delete all rows in a table, you must use the clause WHERE 1=1.
  • The UPDATE or DELETE statement cannot contain subqueries.
  • The UPDATE or DELETE statement cannot contain the LIMIT clause.
INSERT_SELECT
  • You can execute the SELECT statement only on a single table.
  • You must specify a WHERE clause in the SELECT clause. To select all rows in a table, you must use the clause WHERE 1=1.
  • The SELECT clause cannot contain the LIMIT, ORDER BY, or GROUP BY clause.

Scenarios

  • Clear historical data.
  • Update all rows in a table.