Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-1252

Snapshot isolation level options for postgres

    XMLWordPrintable

Details

    • Enhancement
    • Resolution: Unresolved
    • Major
    • None
    • None
    • postgresql-connector
    • None

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              slepkin Simon Lepkin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated: