The repeatable_read isolation mode which is the default, keeps shared lock on the rows read till the initial snapshot completes. If the initial snapshot takes long, this can block other queries. Discussed the same with the Debezium team . Recommendations are

1. Use snapshot isolation mode if possible. Full consistency is guaranteed and no locks are taken during initial snapshot

2. Use read_committed if schema modification will not happen during initial snapshot. This does not prevent concurrent updates to the table. This should be a reasonable option for most customers.

3. Use repeatable_read. Shared locks prevent concurrent updates while the initial snapshot is taken.

The above will be doc changes. In addition, we can look at checking if snapshot isolation is set on the SQL Server. If it is automatically select snapshot isolation which is the most ideal option. Enabling snapshot isolation on the SQL Server has overhead. But since the customer has already chosen it, best option is to use it for the initial snapshot of the table as well.

Here is the discussion with Debezium team https://debezium.zulipchat.com/#narrow/stream/302529-users/topic/Snapshot.20isolation.20mode.20for.20SQL.20Server/near/285385769

Snippets

• the snapshot isolation mode is not the default one because it needs to be explicitly enabled on SQL Server. That was the only reason.

• using repeatable_read be default prevents concurrent schema modification which read_committed does not, so it is a safer option

Other context

Query to determine if Snapshot isolation is enabled