nzw921rx opened a new issue, #10606: URL: https://github.com/apache/seatunnel/issues/10606
### Search before asking - [x] I had searched in the [feature](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Feature%22) and found no similar feature requirement. ### Description **Problem Statement** In multi-parallelism CDC streaming scenarios, extreme instantaneous concurrent data modifications introduce critical data consistency challenges. To mitigate source database pressure, we adopt multi-table merged writes to the JDBC Sink, which requires implementing optimistic lock-based updates via handwritten `custom_sql`. **Current Pain Points** - Overly Complex Configuration: Reliance on custom SQL for optimistic locking leads to verbose, error-prone task configurations, especially when handling multiple tables or complex update logic. - High Maintenance Overhead: Manual SQL writing increases the risk of field order mismatches, syntax errors, and makes configurations difficult to maintain and scale across different tables. - Usability Barrier: The current approach raises the learning curve for users, particularly those unfamiliar with SQL placeholder mapping and optimistic lock patterns. **Proposal** I propose adding a dedicated optimistic lock update option to the JDBC connector, which will: Automatically generate standardized optimistic lock update statements, eliminating the need for manual `custom_sql`. Simplify configuration by exposing intuitive parameters (e.g., `version` ,`update_time`) instead of raw SQL. Maintain full backward compatibility while significantly reducing configuration complexity and error rates in multi-parallelism CDC streaming workflows. **New Configuration Options** | configKey | Type | Required | Default | Description | |-------------------------------|----------|----------|---------|------------------| | `optimistic.locking.field` | String | false | - | After configuration, the Update SQL will automatically append `{field}<{source field} `checksum to the WHERE clause , Enabling requires setting `enable_upsert = true` | **Usage Example** ``` sink { JDBC { url = "jdbc:mysql://localhost:3306/xxx" driver = "com.mysql.cj.jdbc.Driver" table = "your_target_table" enable_upsert = true optimistic.locking.field = "version" } } ``` ### Usage Scenario _No response_ ### Related issues _No response_ ### Are you willing to submit a PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
