skip to content

How can a CDC snapshot read a consistent copy of a source table without locking it for the whole scan?

level: middleimportance: should knowfreq 54%

answer

  1. one instant for the whole scan, not a smear
  2. multi-version reads do not block writers
  3. the lock is for pinning, not for scanning
  4. a long read view stops version cleanup
  5. cost scales with duration, so chunk it

basics

~20 s

Open the scan inside a repeatable-read transaction whose read view is pinned to the log position, so it sees one frozen instant for hours while writers proceed untouched. Any lock is needed only for the moment of pinning, not for the scan.

solid answer

~50 s

Multi-version storage engines let a transaction take a **read view** — a frozen picture of committed state at one instant — and keep serving reads from it no matter how long the scan runs, while concurrent writers create new versions unimpeded. A CDC snapshot exploits that: it starts a `REPEATABLE READ` transaction pinned to the same instant as the recorded log position, then scans. Postgres does this by exporting the snapshot created alongside a logical replication slot; MySQL takes a global read lock for a few milliseconds purely to pair the binary-log coordinates with a consistent InnoDB read view, then releases the lock and scans for hours. The cost is not blocking — it is that a hours-long read view **pins old row versions**, so version cleanup stalls and the source's table and undo space grow for as long as the snapshot runs.

code

sql · 8 lines
sql
FLUSH TABLES WITH READ LOCK;              -- held for milliseconds only
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SHOW MASTER STATUS;                       -- binlog file + position to pin
UNLOCK TABLES;                            -- writers resume immediately

SELECT * FROM orders ORDER BY id;         -- may run for hours
SELECT * FROM order_items ORDER BY id;    -- same instant as the line above
COMMIT;

go deeper

for a junior

Know that a snapshot runs inside one transaction so that everything it reads reflects the same moment, and that on modern engines this does not stop other users from writing.

for a middle

Explain the read view: acquired once at repeatable-read, reused for every statement, pinned to the same instant as the log position. Be able to say why MySQL needs a momentary lock and Postgres does not.

for a senior

Show the cost you actually pay — version cleanup stalls behind the open read view, undo and dead-tuple space grow for hours across the whole database — and the metrics you watch while a large snapshot runs.

for a principal

Own the choice between one long read view with a clean instant and many short ones plus a reconciliation algorithm, and decide where the scan runs — primary or replica — given the source's cleanup pressure and log-retention headroom.

## What consistency means for a snapshot A snapshot that takes six hours must not be a smear of six hours of state. If it reads `accounts` at 09:00 and `orders` at 14:00, a transfer that moved money between them at 11:00 appears in one table and not the other, and the sink starts life with data that never existed in the source at any instant. Worse, the log position pinned for the handover corresponds to *neither* moment, so replaying from it cannot repair the discrepancy for tables read before the pin. So the requirement is: the whole scan, across all captured tables, must observe the database as of **one** instant, and that instant must be the pinned log position. ## The lock-everything approach and why it is a last resort The obvious way is to prevent writes: take a table-level or global read lock, read the log position, scan every table, release. It is trivially correct and operationally unacceptable — production writes stall for the entire scan. On a multi-terabyte source that is hours of downtime, so it survives only for tiny reference tables or maintenance windows. ## The multi-version approach Modern engines keep multiple physical versions of a row, so a reader can be shown the version that was current at a chosen instant while writers append newer ones. A transaction at `REPEATABLE READ` (or an explicit snapshot isolation level) acquires a read view once and reuses it for every statement until it ends. That is exactly the primitive a snapshot needs: readers do not block writers, writers do not block readers, and the scan sees one instant no matter how long it takes. **Postgres.** Creating a logical replication slot yields a consistent point (an LSN) and can export a snapshot identifier. Another session runs `BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '…';` and now reads exactly the state at that LSN. No lock is ever taken on the tables. **MySQL/InnoDB.** There is no way to export a read view, so the connector uses `FLUSH TABLES WITH READ LOCK` to quiesce writes, issues `START TRANSACTION WITH CONSISTENT SNAPSHOT`, reads the current binary-log coordinates or executed GTID set, then `UNLOCK TABLES`. The lock is held for milliseconds. The InnoDB read view opened under it survives the unlock and carries the multi-hour scan. This is why a connector may briefly need elevated privileges and why a long-running query on the source can make that momentary lock wait — the lock must drain in-flight statements before it is granted, and a stuck one turns a millisecond stall into a real outage. ## What it actually costs Not blocking — bloat and pressure. - **Version retention.** Every version superseded during the scan must be kept because the snapshot might still need it. Background cleanup cannot advance past the snapshot's read horizon, so dead rows accumulate across the whole database, not only in the captured tables. A six-hour snapshot on a write-heavy source can leave the source measurably fatter afterwards. - **Undo or rollback growth.** Engines that keep prior images in a separate undo area see that area grow for the life of the transaction, and reads of hot rows get slower as version chains lengthen. - **Log retention.** Separately from the read view, the pinned log position must remain servable. Nothing before it can be recycled until streaming has consumed it. - **Read I/O.** The scan itself competes with the workload for buffer cache and disk, which is why chunking with a bounded fetch size and a deliberate pause between chunks is normal. These costs scale with **duration**, which is the argument for chunked snapshots, parallel table scans, and reading from a replica rather than the primary. ## Chunking without losing consistency Scanning a huge table in key-ordered chunks inside one long transaction keeps consistency but keeps the read view open just as long. The alternative — a fresh transaction per chunk — bounds the pressure but breaks the single-instant guarantee: chunk 1 is as of 09:00, chunk 900 as of 14:00. That is only safe if the pipeline reconciles the resulting inconsistency against the log, which is precisely what watermark-based incremental snapshots do. Choosing between them is the real design decision: one long read view and a clean instant, or many short ones plus a reconciliation algorithm. ## What to watch while it runs Oldest-transaction age, dead-tuple counts or undo size, the age of the pinned log position, and the scan's own progress in chunks. If the snapshot's duration is drifting toward the log retention window, you are heading for a failure that costs you the whole baseline.

  • Why does MySQL need a brief global read lock when Postgres needs none?
    Postgres can export a read view: the replication slot's creation returns both an LSN and a snapshot identifier another session can adopt, so the pairing is atomic without locking anything. InnoDB has no equivalent export, so the connector must quiesce writes for a moment to guarantee that the binary-log coordinates it reads and the read view it opens describe the same instant. The lock is a substitute for an export primitive, not a scanning requirement.
  • What breaks if each snapshot chunk runs in its own short transaction?
    The single-instant guarantee. Chunk 1 reflects 09:00 and chunk 900 reflects 14:00, so cross-chunk and cross-table relationships can be internally inconsistent. That is acceptable only if the pipeline reconciles chunks against the log — the watermark approach — where any key changed while a chunk was in flight is discarded from the chunk and taken from the stream instead.
  • Why can a long CDC snapshot degrade the source even though it never blocks a writer?
    Because the open read view sets the horizon for version cleanup. Superseded row versions across the whole database must be retained in case the snapshot still needs them, so dead rows and undo space accumulate for hours, version chains on hot rows lengthen, and reads slow down. The damage outlives the snapshot until cleanup catches up.

Freezing writes for the whole scan is closing the shop to count stock. A read view is counting from last night's photographs while customers keep shopping — the photos just take up space until you are done.

saying these in an interview costs you the question

  • Assumes a consistent snapshot requires locking tables for the whole scan
  • Thinks a long read view is free because it blocks nobody
  • Reads each table in its own transaction and calls it consistent
  • Cannot say why the log position and the read view must be paired
  • Believes chunking alone preserves the single-instant guarantee

context

resolving links…