Understanding MySQL Concurrency: Read/Write Conflicts, MVCC, and Lock Strategies

MySQL must handle three concurrency relationships: read‑read (no conflict), read‑write (possible dirty, non‑repeatable, and phantom reads, resolved by transaction isolation), and write‑write (update loss, requiring explicit locking).

MySQL employs shared locks, exclusive locks, and optimistic MVCC (Multi‑Version Concurrency Control) to address these issues. MVCC enables non‑blocking snapshot reads, while shared/exclusive locks provide current reads.

InnoDB's MVCC implements snapshot reads using a ReadView that records transaction IDs such as m_low_limit_id (next assignable ID), m_up_limit_id (minimum active ID), m_creator_trx_id (the transaction that created the view), and m_ids (list of active transaction IDs). Visibility rules are:

If id < m_up_limit_id or id == m_creator_trx_id, the version is visible.

If id >= m_low_limit_id, the version is invisible.

If m_ids.empty() or !binary_search(m_ids, id), the version is visible.

Examples illustrate how write‑write conflicts cause lost updates when two transactions increment the same row without proper locking, and how using SELECT FOR UPDATE (pessimistic lock) or an optimistic lock with a version column can prevent the issue.

Sample optimistic‑lock update statement:

UPDATE table_renzhi SET age = age + 1 WHERE id = 1;

In summary, under the default Repeatable Read isolation, InnoDB solves dirty reads, non‑repeatable reads, and phantom reads via MVCC and next‑key locks, but write‑write conflicts still require developers to apply either pessimistic locks (e.g., SELECT FOR UPDATE) or optimistic locking techniques.