Thanks Roman for driving this. Although Upsert sink can bring some consistency guarantee to JDBC sink, users have strong requirements to append exactly-once sink too.
+1 to use XA distribution transaction. For WAL, as far as my experience is concerned, writing JDBC in large quantities when checkpoint is completed will easily lead to too much pressure and some instability to database. For testing, maybe we need add some XA testing in e2e. But I'm worried about the performance of XA. Sometimes using MySQL's XA will lead to poor performance (of course, it may be related to usage). Best, Jingsong Lee On Mon, Jan 6, 2020 at 4:41 PM Roman Khachatryan <ro...@data-artisans.com> wrote: > Hi everyone, > > I'm currently working on exactly-once JDBC sink implementation for Flink. > Any ideas and/or feedback are welcome. > > I've considered the following options: > 1. Two-phase commit. This is similar to Kafka sink. > XA or database-specific API can be used. In case of XA, each sink subtask > acts as a transaction manager, and each checkpoint-subtask pair corresponds > to an XA transaction (with a single branch) > 2. Write-ahead log. This is similar to Cassandra sink. > Transactions metadata needs to be stored in the database along with data to > avoid adding duplicates after recovery. > > For some scenarios, WAL might be better, but in general, XA seems to be a > better option. > > ================== > XA vs WAL comparison > ================== > > 1. Consistency: XA preferable > WAL: longer inconsistency windows when writing from several sink subtasks > > 2. Performance and efficiency: XA preferable (depends on the use case) > XA: > - long-running transactions may delay GC and may hold locks (depends on the > use case) > - databases/drivers may have XA implementation issues > WAL: > - double (de)serialization and IO (first to flink state, then to database) > - read-from-state and write-to-database spikes on checkpoint completion > both may have read spikes in consumer > > 3. Database support: XA preferable > XA: most popular RDBMS do support it (at least mysql, pgsql, mssql, oracle, > db2, sybase) > WAL: meta table DDL may differ > > 4. Operability: depends on the use case > XA: > - increased undo segment (db may need to maintain a view from the > transaction start) > - abandoned transactions cleanup (abandoned tx may cause starvation if for > example database blocks inserts of duplicates in different transactions) > - (jars aren't an issue - most drivers ship XA implementation) > WAL: > - increased intermediate flink state > - need to maintain meta table > > 5. Simplicity: about the same > XA: more corner cases > WAL: state and meta table management > Both wrap writes into transactions > > 6. Testing - WAL preferable > XA requires MVVC and proper XA support (no jars needed for derby) > > -- > Regards, > Roman > -- Best, Jingsong Lee
