-
Enhancement
-
Resolution: Done
-
Major
-
None
-
None
In the same vein as DBZ-947 for sqlserver-connector, it'd be nice to allow different isolation levels when snapshotting in postgres. This would allow us to sacrifice consistency in exchange for weaker locks. For example, since concurrent index creation in postgres "must wait for any transactions that have a snapshot... to terminate"(docs), one table being snapshotted prevents index creation everywhere in the database.
I'd propose these modes for a new snapshot.isolation.mode config:
no-transaction – snapshot is not run in an explicit transaction
read-committed – snapshot is run in a transaction, using the default isolation level READ COMMITTED.
repeatable-reads – snapshot is run in a transaction with isolation level REPEATABLE READS, ensuring data consistency between the tables being scanned and blocking DDL on the selected tables and concurrent index creation throughout the database. Allows for serialization anomalies.
serializable – snapshot is run in a transaction with isolation level REPEATABLE READS, ensuring data consistency between the tables being scanned and blocking DDL on the selected tables and concurrent index creation throughout the database. With options READ ONLY, DEFERRABLE, this prevents serialization anomalies. (this is the current isolation level used)
It's possible that we can skip read-committed and repeatable-reads, since I can't think of any cases in which a debezium snapshot would gain anything from those settings.