A bug that hid for years behind a silent SQL clause.

While working on Debezium’s MariaDB connector, I dug into a duplicate-events issue that traced back to a single missing statement: START TRANSACTION WITH CONSISTENT SNAPSHOT. Without it, InnoDB creates the MVCC read view lazily — at the first SELECT, not when the transaction begins. A few milliseconds of concurrent writes in between, and the snapshot quietly drifts past the captured binlog position.

The twist: that statement was intentionally removed in 2019 to fix a different bug. MyRocks (RocksDB engine) strictly validates the isolation level before accepting a consistent snapshot. InnoDB doesn’t — it silently ignores it and moves on. One engine screamed, the other smiled and did nothing. The defensive fix removed the statement entirely, and nobody noticed the side effect for years.

I wrote about the theory behind MVCC, how Postgres and MySQL/MariaDB implement it differently, and why understanding the internals of your storage engine matters more than you’d expect — especially when two engines give you different answers to the same SQL.

[link to article]

#databases #mvcc #debezium #mariadb #mysql #cdc #opensource