You can use the lock-free 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, 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.

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 with 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-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

Benefits

  • Your business is not affected.
  • Minor impact on database performance and database space.
  • Guaranteed execution efficiency for large-volume 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.

Scenarios

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