One thing that sometimes is really underestimated, it’s the capability to have enough knowledge of (distributed) systems in which sometimes is delivered value to stakeholders. Even if you don’t give so much importance to it (and you are right…), knowledge is something that is good for soul, help avoid headaches during incidents and makes resilient to bubbles. So this is how multi-version concurrency control for database snapshots gave me some trouble.

Context#

A user reported an issue related to snapshotting with Debezium for MariaDB. Specifically, the issue involved the generation of duplicate events when a snapshot takes a long time to complete (for example, when a table contains a large amount of data) and new records are inserted while the snapshot is in progress. We found that the fix requires creating a consistent read view using the MVCC mechanism. While this sounds straightforward, it is only part of the story. The bug had existed for a long time and was originally introduced for a specific reason, which is directly related to MVCC implementations.

TL:DR#

Multi-version Concurrency control (now MVCC) is an internal mechanism in many (not all) database engines (relational and not) that provides consistent reads without blocking writes. The idea is that each write produces a new copy (or version) of that data. Since writes don’t overwrite each other, the reads can get any version without any lock.

History#

MVCC isn’t a new idea, the first database to implement internally such mechanism was interbase and many other during the time introduced it:

  • ~1981 - Interbase
  • ~1984 - Oracle
  • ~1985 - PostgreSQL
  • ~2001 - MySQL (InnoDB)
  • … So mostly isn’t something hyped like a new IA model but research is going and new version and improvements are under the corner based on the drawbacks of such methodology).

Concurrent Transactions#

Before jump into the mechanism, we cannot go on without talking about theory behind it. We should demonstrate that database execution is still valid under MVCC. How? Well, concurrent transactions in a database are correct if the execution is equivalent if they were executed one after the other and this assumption must be valid also under MVCC. Where should we start? the log structure.

The Serial Log#

Without going too much into details, the log is an append-only, totally-ordered sequence of events ordered by time:

Logs in databases (and in distributed systems in general) are fundamental to deal with concurrency. We can use a log to represent what is going on in a database, and in particular the events are reads and writes operations executed on behalf of each transactions. The order defines the DBS execution during is concurrent work and can give a temporal idea of the state of the system. Formally:

A Transaction log is a partially ordered set (poset) \(T_{i}=(\Sigma_{i},<_{i})\) where \(\Sigma_{i}\) is the set of reads and writes issued by (an execution of) transaction \(i\), and \(<_{i}\) tells the order in which those operations must be executed.

– Multiversion Concurrency Control - Theory and Algorithms

So we can say that a DBS log over \(T\) represents an execution of \(T_{0},...,T_{n}\) in which there is a reads-from relationship between transactions. Formally:

Let \(L\) be a log over \(\{T_{0},..., T_{n}\}\). Transaction \(T_{j}\) reads-x-from \(T_{i}\) in \(L\) if:

  1. \(w_{i}[x]\) and \(r_{j}[x]\) are operations in \(L\)
  2. \(w_{i}[x] < r_{j}[x]\)
  3. no \(w_{k}[x]\) falls between these operations

Two logs over \(\{T_{0},..., T_{n}\}\) are equivalent if they have the same reads-from relationships.

We have defined a transaction log and equality between logs but when is correct? The concurrent execution should be the same if the execution is sequential. Formally:

a serial log is a totally ordered log on \(\Sigma\) such that for every pair of transactions \(T_{i}\) and \(T_{j}\) , either all the operations of \(T_{i}\) precede all those of \(T_{j}\), or vice versa. […] Serial logs represent the latter executions, and so a correct log is any log equivalent to a serial log. Such logs are termed serializable (SR).

We can determine if a log is serializable by analyzing the derived serialization graph. The serialization graph for \(L\), denoted \(SG(L)\), is a directed graph whose node are the transactions in \(T\) that are committed in \(L\) and whose edges are all \(T_{i} \rightarrow T_{j}\) where \((i \neq j)\) such that one of \(T_{i}\)’s operations precedes and conflicts with one of \(T_{j}\)’s operations in \(L\). If \(SG(L)\) is an acyclic directed graph, then \(L\) is \(SR\).

Multi-Version Concurrency Control#

In a mutiversion DBS, each write produces a new version and the assumptions and definitions must be still valid. We now denote versions of \(x\) by \(x_{i}, x_{j},...\), where the subscript is the index of the transaction that wrote the version. So than the operation on versions are denoted with \(r_{i}[x_{j}]\) and \(w_{i}[x_{j}]\). These operation now are version operations that are translated in a multiversion DBS by a function \(h\) which maps each \(w_{i}[x]\) into \(w_{i}[x_{i}]\), and each \(r_{i}[x]\) into \(r_{i}[x_{j}]\) for some j. Formally:

a multiversion DBS log (or simply MV log) over \(T\) is a poset \(L=(\Sigma, <)\) where:

  1. \(\Sigma=h(\cup_{i=1}^{n} \Sigma_{i})\)
  2. for each \(T_{i}\) and all operations \(op_{i}\), and \(op'_{i}\) then \(h(op_{i})<h(op'_{i})\)
  3. if \(h(r_{j}[x]) = r_{j}[x_{i}]\), then \(w_{i}[x_{i}]<r_{j}[x_{i}]\)

The meaning:

  1. each operation submitted by a transaction is translated into an appropriate multiversion operation.
  2. MV log preserves all orderings stipulated by transactions
  3. transaction may not read a version until it has been produced.

The proof of equivalence in MVCC is left to the reader, but it’s important to take in mind that the mechanism has an important cost in term of memory, indeed, the MVCC isn’t a feature inside all the databases. For example, Postgres has his MVCC implementation that defines how are stored the data in the database.

An Example: Postgres#

Database engines implement the MVCC in different ways and provide different syntax for using this feature. In PostgreSQL, MVCC-related information is stored in the 23-byte header that is present in every tuple.

Supposing we have a sample table like:

CREATE TABLE product (
	id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    vendor      TEXT NOT NULL,
    qty         INTEGER NOT NULL DEFAULT 0
);

if we add a row:

INSERT INTO product (name, vendor, qty) VALUES ('ps5', 'Sony', 5)

the table contains:

 ctid  | xmin | xmax | id | name | vendor | qty |
-------+------+------+----+------+--------+-----+
 (0,1) |  423 |    0 |  1 | ps5  | sony   |  5  |
(1 row)

There are a series of hidden columns:

  • ctid- Tuple identifier within the page.
  • xmin- Transaction ID that created this tuple.
  • xmax- Transaction ID that deleted or superseded this tuple (0means the tuple is not deleted and visible to all requests).

These additional information are important to implement MVCC. Let’s see what happen if we start a transaction:

begin;

and select the product 1 in the table:

select ctid, xmin, xmax, * from product where id = 1;

 ctid  | xmin | xmax | id | name | vendor | qty |
-------+------+------+----+------+--------+-----+
 (0,1) |  425 |    0 |  1 | ps5  | sony   |  5  |
(1 row)

if we try to update the qty for the row and we read again:

update product set qty=4 where id = 1;

select ctid, xmin, xmax, * from product where id = 1;

 ctid  | xmin | xmax | id | name | vendor | qty |
-------+------+------+----+------+--------+-----+
 (0,5) |  427 |    0 |  1 | ps5  | sony   |  4  |
(1 row)

and check the actual transaction id:

select txid_current_if_assigned();

txid_current_if_assigned
--------------------------

                    427
(1 row)

now if we open another session without committing and we retrieve the product:

select ctid, xmin, xmax, * from product where id = 1;

 ctid  | xmin | xmax | id | name | vendor | qty |
-------+------+------+----+------+--------+-----+
 (0,1) |  425 |  427 |  1 | ps5  | sony   |  5  |
(1 row)

As you can see xmin xmax and ctid are different. In particular, the xmax is no longer zero but contains the transaction that is still open. if we commit the previous transaction:

commit;

and in the other session we read again the tuple:

select ctid, xmin, xmax, * from product where id = 1;

 ctid  | xmin | xmax | id | name | vendor | qty |
-------+------+------+----+------+--------+-----+
 (0,1) |  427 |    0 |  1 | ps5  | sony   |  4  |
(1 row)

now we see the tuple aligned at the previous transaction.

The Transaction Isolation level#

The BEGIN statement defines the starting point for a transaction but also how much it’s isolated from changes made by other concurrent transactions. In the previous example, the default level for postgreSQL is READ COMMITTED which means that each SQL statement sees a snapshot of data as of the start of that statement. A later SELECT in the same transaction can see changes committed by other transactions after the first SELECT. The default isolation level isn’t enough to get a consistent snapshot: all queries in the transaction must see the database as it existed when that snapshot was taken. Re-reading the same data will return the same result even if another transaction commits changes meanwhile. This kind of isolation level is REPEATABLE READ:

BEGIN ISOLATION LEVEL REPEATABLE READ;

The tradeoff#

PostgreSQL’s MVCC implementation is easy to explain and to show the overall idea of the mechanism even if someone could say that it’s the worst among other databases. Indeed, there are some problems (or tradeoff) to consider:

  • Data duplication: new version means more data to save. It can increase the storage consumption, particularly for big tables
  • Dead Tuples: The presence of excessive expired versions (dead tuples) can degrade performances (increased scan time, long vacuum processing, etc.)

Debezium & Initial Snapshot#

The Initial Snapshot phase in Debezium when is possible, uses the MVCC mechanism with a transaction isolation level scope per transaction. This approach permits to get a snapshot of the datasource without sacrificing resources. Debezium offers connectors for many databases and engines and having a common ground to all of them is hard and sometimes epic.

The challenge is: how do you read an entire database consistently (potentially millions of rows across many tables) without blocking writes for the entire duration? For small databases a full lock might work, but when tables contain billions of rows, a snapshot can take time and locking writes for that long is simply not an option.

The approach combines a brief global lock with the MVCC mechanism we’ve been discussing: lock all writes for a moment, capture the current position in the transaction log, pin a consistent read view, then release the lock. All the heavy scanning happens after the lock is released, using the pinned read view to see a consistent state. The writes can resume immediately because the MVCC read view guarantees isolation.

The details of how this is implemented depend on the database and the storage engine.

MariaDB and storage engines#

Usually an OTLP supports MVCC to keep the transactionality under the read isolation conditions that are necessary to have a consistent view. We have seen the theory and how a database like Postgres implement and enable a consistent snapshot, but this is still valid when a database supports different storage engine?

A storage engine defines how a table’s data is physically stored and the mechanisms behind it like MVCC. While SQL provides a declarative description of the desired logical database state transitions, the storage engine is responsible for executing the low-level operations required to realize those transitions and persist the resulting state.

MariaDB (like MySQL) supports different storage engine and the current default storage engine for both is InnoDB which supports MVCC and transaction isolation like Postgres.

If we want to take a snapshot for a mariaDB database with InnoDB storage engine, we can do the following:

set autocommit = 0; #1

FLUSH TABLE WITH READ LOCK; #2
      
SHOW MASTER STATUS; #3
      
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; #4

UNLOCK TABLES; #5

SELECT * FROM customers; #6
       
COMMIT; #7

These statement are similar to one that we have seen for Postgres with a bit of additional complexity to address a snapshot: REPEATABLE READ defines the isolation level and the START TRANSACTION WITH CONSISTENT SNAPSHOT describe the starting point of the transaction. But if you look at the SQL above, START TRANSACTION WITH CONSISTENT SNAPSHOT is nowhere in the sequence.

Remember what we discussed about MVCC: the read view determines which version of the data a transaction can see. In InnoDB, under REPEATABLE READ, the read view is created lazily which means not when the transaction begins, but at the first SELECT. Consequently there is a dangerous window between UNLOCK TABLES and the first SELECT where concurrent writes can slip in:

Session A (Debezium)                    Session B (Application)
─────────────────────                   ─────────────────────
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  → pos=1000
SET SESSION TX ISOLATION REPEATABLE READ;
UNLOCK TABLES;
                                        INSERT INTO customers VALUES (42, 'Alice');
                                        COMMIT;  → written at pos=1001
SELECT * FROM customers; ← read view created HERE, SEES row 42!

Debezium captured binlog position 1000, but the SELECT sees row 42 which was written at position 1001. When Debezium later starts streaming changes from position 1000, it encounters that same INSERT again and produces a duplicate event.

The fix is to pin the read view before releasing the lock:

set autocommit = 0;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- pin the read view NOW
UNLOCK TABLES;                                -- safe: read view already exists
SELECT * FROM customers;                      -- sees data as of the snapshot
COMMIT;

START TRANSACTION WITH CONSISTENT SNAPSHOT forces InnoDB to create the read view immediately, while the global lock is still held. The snapshot is frozen at the same point in time as the captured binlog position. No race condition, no duplicates.

Simple, right? So why was this statement missing from Debezium’s code for years?

DBZ-1428#

The story starts in 2019 with a different bug: DBZ-1428. A user reported that Debezium’s MySQL connector failed when running against MyRocks, the RocksDB storage engine for MySQL:

“Only REPEATABLE READ isolation level is supported for START TRANSACTION WITH CONSISTENT SNAPSHOT in RocksDB Storage Engine.”

The error was puzzling because the code did set REPEATABLE READ before issuing the statement. The sequence at the time looked like this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- set isolation
FLUSH TABLES WITH READ LOCK;                          -- ← implicit commit!
START TRANSACTION WITH CONSISTENT SNAPSHOT;           -- ← MyRocks: Error 4062!

The FLUSH TABLES WITH READ LOCK statement causes an implicit commit in MySQL, and implicit commits have a subtle side effect: SET TRANSACTION (without SESSION) applies only to the next single transaction. The implicit commit starts and finishes an internal transaction, consuming the isolation setting. By the time START TRANSACTION WITH CONSISTENT SNAPSHOT executed, the isolation level had silently reverted to the session default.

The two engines diverge in how they manage the isolation level:

  • InnoDB is lenient: it accepts WITH CONSISTENT SNAPSHOT under any isolation level and silently ignores the clause if the level doesn’t support consistent reads. No error, just a warning.
  • MyRocks is strict: it explicitly validates that REPEATABLE READ is active and throws Error 4062 if it’s not.

InnoDB’s leniency meant the bug lived for years without anyone noticing and The WITH CONSISTENT SNAPSHOT clause was silently doing nothing which means the read view was never actually pinned at the right moment.

The DBZ-1428 fix re-set the isolation level after the FLUSH, but as a side effect, START TRANSACTION WITH CONSISTENT SNAPSHOT was removed entirely from the MariaDB connector code path. The reasoning seemed defensible: if some storage engines can’t handle this statement, better to remove it and avoid breaking those configurations but this decision silently introduced the duplicate-events bug that would remain undetected for years because it happens under some particular conditions.

DBZ-9006: the particular conditions#

Fast forward to 2025. A user reports that Debezium for MariaDB generates duplicate snapshot events when the snapshot runs long and concurrent writes happen. The symptoms matched exactly the race condition described above: without START TRANSACTION WITH CONSISTENT SNAPSHOT, the read view was created lazily at the first SELECT, after the global lock was already released.

The fix restored START TRANSACTION WITH CONSISTENT SNAPSHOT and introduced a proper way to handle engines that don’t support it:

if (connectorConfig.getSnapshotLockingStrategy()
        .useConsistentSnapshotTransaction()) {
    connection.executeWithoutCommitting(
        "START TRANSACTION WITH CONSISTENT SNAPSHOT");
}

For engines that support MVCC and consistent snapshots (InnoDB, MyRocks), the statement is now issued automatically but for configurations where consistent snapshots can’t be guaranteed, a new minimal_at_least_once locking mode explicitly acknowledges that duplicate events are possible which we can say it’s an honest tradeoff.

InnoDB vs MyRocks#

Both engines create snapshots lazily, deferring the read view until the first SELECT which makes START TRANSACTION WITH CONSISTENT SNAPSHOT essential for any tool that needs to pin a read view at a known point in time, but when something goes wrong, InnoDB forgives and MyRocks rejects throwing errors.

This demonstrate that you cannot be sure that a SQL code applied in the same database can have the same effect if the engine is different.

Conclusion#

What looked like a MyRocks compatibility issue (DBZ-1428) was a MySQL quirk (?): FLUSH TABLES WITH READ LOCK consuming the transaction-scoped isolation level through an implicit commit and what seemed like a defensive removal of a “problematic” SQL statement led to years of silent data inconsistency (DBZ-9006).

Understanding how MVCC creates read views, when snapshots are pinned, and the difference between lazy and eager snapshot creation is the difference between a correct snapshot that captures the database at a single point in time, and one that quietly drifts as concurrent transactions commit. After having all these things in your mind (or context) you can do the one-line fix.