Why Does MySQL Show Negative Balances? Unraveling MVCC and Isolation Levels

P0 Incident: Over‑Deducted Balance

A production incident occurred where a transaction system deducted more than the available balance, resulting in a negative account balance after switching the database from Oracle to MySQL.

Database Schema

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 update sequence involves selecting the balance, comparing it with the deduction amount, and then updating the balance within a transaction that uses a write lock at step t3.

When the database runs under MySQL's default REPEATABLE READ (RR) isolation, two concurrent transactions can read stale versions, leading to the balance being updated to a negative value.

Why are the same queries executed multiple times? Because the queries are in different methods and cannot share results, forcing repeated database reads.

MVCC

MySQL implements Multi‑Version Concurrency Control (MVCC) to allow concurrent reads while writes lock rows. Each row version stores hidden fields DB_TRX_ID, DB_ROLL_PTR, and ROW_ID.

Consistency View

At the start of a transaction MySQL builds a consistency view that records all active (uncommitted) transactions. A row version is visible to the current transaction only if its DB_TRX_ID satisfies specific rules.

If the version's transaction ID is less than the smallest active ID, it is visible.

If greater than the largest active ID, it is invisible.

If the ID is in the active set, it is invisible.

If the ID lies between the min and max active IDs, it is visible.

If the ID equals the current transaction's ID, it is visible.

Current Read vs Snapshot Read

Snapshot reads retrieve a version that was committed before the consistency view was created, while SELECT ... FOR UPDATE forces a current read of the latest version.

Problem Analysis

Under RR, both transactions create a consistency view at t2 and t3. Transaction 1 acquires a write lock with SELECT ... FOR UPDATE, updates the balance to 900, and commits. Transaction 2, blocked until the lock is released, performs a current read and sees the version 900, but because the latest version’s transaction is still active, it falls back to the previous version 1000, producing the over‑deduction.

When the isolation level is changed to READ COMMITTED (RC), a new consistency view is built for each query, so at t11 transaction 2 sees the committed 900 balance, eliminating the anomaly.

Summary

MySQL’s default isolation is REPEATABLE READ; each InnoDB row can have multiple versions identified by a transaction ID.

Consistency views determine which versions are visible under RR and RC.

Current reads always return the latest version; snapshot reads return the version visible to the view.