DMSLockFreeSqlOperator - Data Management - Alibaba Cloud Documentation Center

The DMSLockFreeSqlOperator executes large-scale, lock-free DML operations by splitting them into smaller batches through the DMS Chunk DML service.

Features

Executes large-scale, lock-free DML operations using the DMS Chunk DML service. Ideal for UPDATE or DELETE statements on large tables, as it splits them into smaller batches to prevent long-running table locks.

Parameters

Note

The sql, instance, and database parameters support Jinja templates.

Parameter	Type	Required	Description
sql	string	Yes	The DML statement (UPDATE or DELETE) to execute.
instance	string	Yes	The connection name (DBLink) of the DMS-managed database instance.
database	string	Yes	The name of the database. Note For a MySQL instance, specify only the database name, such as `my_database`. For a PostgreSQL instance, use the `database_name.schema_name` format, such as `my_database.public`.
callback	function	No	A callback function that processes the execution results.
polling_interval	int	No	The polling interval for checking execution status, in seconds. Default value: 10. If you set this parameter to 0 or a negative number, the task is submitted but the operator does not wait for the result. The status check includes a built-in retry mechanism.

Example

Note

task_id and dag are Airflow-specific parameters. For more information, see the official Airflow documentation.

from airflow import DAG
from airflow.providers.alibabadms.cloud.operators.dms_lock_free_sql import DMSLockFreeSqlOperator

with DAG(
    "dms_lock_free_sql_example",
) as dag:

    lock_free_task = DMSLockFreeSqlOperator(
        task_id="batch_update",
        instance="my_mysql_instance",
        database="my_database",
        sql="UPDATE orders SET status = 'archived' WHERE created_at < '{{ macros.ds_add(ds, -90) }}';",
        polling_interval=30,
        dag=dag,
    )

Note

All DMS Airflow operators support common features such as task cancellation and automatic retries. For more information, see Airflow DMS Operator.