Skip to main content

Run large DML changes with OnlineDML

Running large updates or deletes directly can cause long transactions, lock waits, and service instability. NineData OnlineDML is built for large-scale data changes and splits them into batches to reduce impact on online services.

Use cases​

  • Bulk updates or deletes: Split one large DML operation into multiple batches.
  • Data repair: Fix historical data while reducing impact on online workloads.
  • Long transaction control: Use scan thresholds, batch size, and wait time to control execution pressure.

Background​

Large production changes are often urgent, especially during incident recovery. These jobs usually touch many rows, and adding an index just for a one-time update is often not worth the cost. Teams often fall back to stored procedures or temporary scripts to process the data in batches, but that approach is hard to reuse and can still put pressure on the database.

OnlineDML provides a simpler alternative. It breaks a large DML statement into smaller batches, reduces lock time, and keeps the online workload more stable.

Before you begin​

  • You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
  • Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.
  • Your role is Administrator. For more information about roles, see Roles.
  • The data source type is MySQL or PostgreSQL.

Precautions​

The OnlineDML feature is not supported under the following circumstances:

  • The DML statement executing the changes contains ORDER BY, GROUP BY, LIMIT, or involves operations on multiple tables.
  • The SELECT clause of the INSERT INTO SELECT statement includes a multi-table JOIN.
  • The target table lacks a primary key or unique key, or the corresponding primary key or unique key columns do not have the NOT NULL attribute.
  • The object being modified by the UPDATE statement is a primary key or unique key column.

Procedure​

OnlineDML is enabled by default in the SQL standards provided by NineData. Adjust the execution strategy for your business scenario, then bind the standard to the target data source. This article explains how to update the strategy.

  1. Sign in to the NineData Console.

  2. In the left navigation pane, click Datasource > Policy & Process.

  3. On the Policy & Process page, click the SQL Dev Policy tab.

  4. Click the target Name, or in the Actions column, click Details.

  5. On the Details page, click the Query & Update tab, find OnlineDML: Risk detection of large-scale data changes, and in the Actions column, click Edit.

    tip

    Ensure that OnlineDML: Risk detection of large-scale data changes is enabled, indicated by the status in the Status column.

  6. Configure the execution strategy for Online DML. The table below describes the main parameters.

    Parameter
    		Description
    Threshold for the number of rows scanned by DML statementsSet the scanned-row threshold for DML statements. The default is 20000. When the estimated scanned rows exceed this value, the statement is treated as a potential high-risk change. If OnlineDML is enabled, NineData executes the statement in batches to reduce table locks and business impact.
    OnlineDML SwitchTurn OnlineDML on or off. It is off by default. When enabled, DML statements that exceed the scanned-row threshold are executed in batches.
    OnlineDML batch size (number of rows)Set the number of modified rows in each OnlineDML batch. The default is 2000.
    OnlineDML Sleep time (s)Configure the OnlineDML wait time in seconds. This option is disabled by default. When OnlineDML exceeds a specified batch count or detects high database pressure, it pauses writes for the configured wait time.

  7. Click OK.

How it works​

After you configure the OnlineDML rule, submit SQL tasks that contain DML statements for data sources that use this strategy. During precheck, NineData estimates how many rows the DML statement scans. If the number exceeds the threshold, OnlineDML runs the statement in batches.

onlinedml

Result​

After the OnlineDML rule is configured and bound to the target data source, SQL tasks that exceed the scan threshold can be executed in batches. Use the SQL task details page to review execution status, batch behavior, and any errors that need follow-up.

Next Steps​

  • Submit a SQL task that contains the target DML statement.
  • Review execution details after the task finishes.