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

MySQL must handle three kinds of concurrency relationships: read‑read, read‑write, and write‑write. Read‑read has no conflict because only data is read. Read‑write can cause dirty reads, non‑repeatable reads, and phantom reads, which are resolved by the database's transaction isolation mechanisms. Write‑write may lead to update‑loss problems, such as the first‑type loss (a rolled‑back transaction overwriting a committed one) and the second‑type loss (one transaction overwriting another's committed data).

To solve concurrency, MySQL relies on locks (shared and exclusive) and an optimistic locking approach called MVCC (Multi‑Version Concurrency Control) that allows non‑blocking read‑write operations. InnoDB's MVCC, combined with Next‑Key Lock, eliminates dirty reads and non‑repeatable reads and resolves phantom reads under the Repeatable Read isolation level.

Under Repeatable Read, write‑write conflicts must be handled by developers using explicit locks. InnoDB provides two read modes: current read, which acquires locks and reads the latest data, and snapshot read, a non‑blocking read that returns a historical version based on a ReadView.

A ReadView records transaction IDs such as m_low_limit_id (next assignable ID), m_up_limit_id (minimum active transaction ID), m_creator_trx_id (the transaction that created the view), and m_ids (list of active transaction IDs). Visibility rules are:

1. id < m_up_limit_id || id == m_creator_trx_id  // visible
2. id >= m_low_limit_id                         // not visible
3. m_ids.empty() || !std::binary_search(p, p + m_ids.size(), id) // visible

If a version's transaction ID is in m_ids , the transaction was still active when the ReadView was created, so the version is invisible; otherwise it is visible.

Examples illustrate how snapshot reads can cause update loss when two transactions increment a column concurrently. Using an exclusive lock (e.g., SELECT FOR UPDATE ) or an optimistic lock with a version column prevents the loss. The optimistic‑lock example uses a simple SQL statement:

update table_renzhi set age = age + 1 where id = 1;

Pessimistic locking can be applied with SELECT FOR UPDATE , causing other transactions to block until the lock is released.

In summary, InnoDB's MVCC eliminates dirty reads and non‑repeatable reads, while Next‑Key Lock handles phantom reads; however, write‑write conflicts under Repeatable Read still require developers to employ optimistic or pessimistic locking techniques to avoid update failures or lost updates.