nzw921rx commented on issue #10606: URL: https://github.com/apache/seatunnel/issues/10606#issuecomment-4073279205
Hi @chl-wxp ### Usage Scenario #### 1. Business Context - **Source**: MySQL (or other CDC-capable DB) → Canal/Debezium → Kafka. - **Goal**: Multi-table merged writes into one JDBC Sink with multiple parallelism; use optimistic locking so concurrent updates do not overwrite each other. - **Parallelism**: Sink runs with multiple tasks (e.g. `parallelism=4`). Different tasks may process different keys/partitions of the same table, so the same primary key can receive out-of-order or concurrent CDC events. #### 2. Data Flow MySQL (multi-table CDC) → Kafka → SeaTunnel Kafka Source → Transform (optional: merge/routing) → JDBC Sink (multi-parallelism, upsert by primary key, optimistic lock required) Events for the same primary key can arrive out-of-order or nearly at the same time on different Sink tasks. A simple “insert or update by primary key” can let a **newer** event be overwritten by an **older** one. The UPDATE must therefore include a version (or timestamp) condition in the WHERE clause so that only the expected row version is updated—i.e. optimistic locking. #### 3. Current Pain Points - **Complex config**: Each table needs a long `custom_sql` with correct placeholder order; with many tables this is error-prone and hard to read. - **High maintenance**: Schema changes (new/removed/renamed columns) require updating SQL in multiple places; field order and aliases are easy to get wrong in multi-table setups. - **Steep learning curve**: Users must understand placeholder mapping and how to write the optimistic-lock WHERE (e.g. `WHERE id = ? AND version < ?`), which is a barrier for non-SQL users. #### 4. Desired Simplification With a built-in option (e.g. `optimistic.locking.field = "version"`) and `enable_upsert = true`, the connector would automatically add the version condition to the UPDATE WHERE clause. No hand-written `custom_sql` for optimistic locking; config stays short and stable when adding tables or changing schema. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
