Understanding MySQL Transactions: Concepts, Isolation Levels, MVCC, and Implementation Details

MySQL transactions ensure that a group of database operations either all succeed or all fail, preserving data consistency. The article begins with a classic bank transfer example to illustrate the need for atomicity and consistency.

It then introduces the ACID properties—Atomicity, Consistency, Isolation, Durability—and focuses on the Isolation aspect, describing phenomena such as dirty reads, non‑repeatable reads, and phantom reads.

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level is explained with behavior examples and the corresponding MySQL settings, e.g., transaction‑isolation=READ‑COMMITTED .

Implementation details cover InnoDB's multi‑version concurrency control (MVCC). Every row version stores a transaction id , and undo logs allow reconstruction of previous versions. The article shows how read‑views are built using low and high watermarks to determine which row versions are visible to a transaction.

Transaction start methods are described, including explicit BEGIN / START TRANSACTION , setting autocommit=0 , and the use of COMMIT WORK AND CHAIN to reduce round‑trips. It also notes that some client libraries enable autocommit off by default, which can unintentionally create long‑running transactions.

Practical SQL snippets illustrate how to detect long transactions, for example: SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

Examples demonstrate how different isolation levels affect the values returned by concurrent transactions (V1, V2, V3) and how MVCC ensures consistent reads without locking the entire dataset.

Finally, the article emphasizes avoiding long transactions because they retain old undo logs and lock resources, potentially inflating storage usage (e.g., 20 GB data with 200 GB undo logs) and degrading performance.

In summary, the piece provides a deep dive into MySQL transaction mechanics, isolation level trade‑offs, MVCC implementation, and best practices for managing transaction lifecycles.