Why MySQL Transactions Can Overdraw Balances: MVCC and Isolation Levels Explained

P0 Incident: Over‑deducted Balance

A production transaction system updates an account's balance on each transaction. When the deducted amount exceeds the current balance, the operation should fail, but a bug caused the balance to become negative.

The account table is defined as:

CREATE TABLE `account` (
  `id` bigint(20) NOT NULL,
  `balance` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The SQL sequence used to debit the balance is illustrated below:

To prevent concurrent updates, a write lock is acquired at step t3 . If the lock is held, other threads block until the first transaction commits.

When the database engine was switched from Oracle to MySQL (default isolation level RR ), the same SQL sequence produced an unexpected over‑deduction, leaving the balance negative.

Reproducing the Concurrency Issue

Assume two transactions run the same sequence on a record id=1, balance=1000. The timeline (RR) is:

Under RR, each transaction builds a consistency view at its first query. The results are:

Transaction 1 reads balance 1000 at t2, t4, t6.

Transaction 2 reads balance 1000 at t3, balance 900 at t5, and again 1000 at t11.

When the isolation level is changed to RC , the view is rebuilt for every query, leading to different results (t11 shows 900 instead of 1000).

MySQL MVCC Overview

MySQL InnoDB implements Multiversion Concurrency Control (MVCC) . Each row version stores three hidden fields:

DB_TRX_ID – the transaction that created the version.

DB_ROLL_PTR – pointer to the undo log.

ROW_ID – internal row identifier.

When a row is updated, a new version is written, the current transaction ID is stored in DB_TRX_ID, and the previous version is kept in the undo log. The undo log enables reconstruction of older versions.

Consistency View

When a transaction starts, MySQL creates a consistency view that records all active (uncommitted) transaction IDs. A row version is visible to the current transaction only if:

The version's transaction ID is smaller than the smallest active ID (already committed).

The version's transaction ID is larger than the largest active ID (created after the view, not visible).

The version's transaction ID belongs to an active transaction (still uncommitted, not visible).

The version's transaction ID is the current transaction's own ID (always visible).

RR builds the view at the first query; RC rebuilds it for every query.

Current Read vs Snapshot Read

Snapshot reads (the default) retrieve the version visible to the consistency view, allowing repeatable reads. SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE perform a current read, always returning the latest committed version.

Problem Analysis

In the incident, transaction 1 locked the row with SELECT ... FOR UPDATE, updated the balance to 900, and committed. Transaction 2, still using the RR view, could not see this new version at t5 and therefore read the older version (balance 1000). When the isolation level was switched to RC, the view at t11 included the committed update, so transaction 2 saw balance 900.

Summary

MySQL's default isolation level is RR. Each InnoDB row can have multiple versions, each tagged with a unique transaction ID. Consistency views determine which versions are visible based on the rules above. The key takeaways are:

Under RR, ordinary queries see only versions committed before the transaction started.

Under RC, each query sees versions committed before that specific query.

Current reads always return the latest version.

References

https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

http://mysql.taobao.org/monthly/2017/12/01/

http://mysql.taobao.org/monthly/2018/11/04/

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html