How MySQL InnoDB Achieves High Concurrency with MVCC and Locks

1 Introduction

Concurrency control prevents data errors when multiple users access the database simultaneously, ensuring consistency. MySQL InnoDB provides robust concurrency mechanisms.

2 InnoDB Concurrency Control

MySQL supports several concurrency control methods:

Read-Write Locks : shared read locks, exclusive write locks.

Transaction Isolation Levels : Read Uncommitted, Read Committed, Repeatable Read, Serializable.

Lock Waits and Deadlocks : blocking and deadlock detection.

Segmented Locking : lock specific parts of the database.

Optimistic Concurrency Control : assume no conflict, check at commit.

Multiversion Concurrency Control (MVCC) : versioned reads for higher concurrency.

Proper configuration of these mechanisms ensures consistency and integrity.

Common means to guarantee consistency include:

Locking

Multi Versioning

3 Basic Lock Implementation

MySQL uses locks (shared and exclusive) to maintain data consistency. Basic lock flow: lock before operation, release after completion. Pure lock mechanism is serial and hurts performance.

Shared locks allow concurrent reads; exclusive locks block all other operations.

Therefore, locks alone are insufficient; Multi Versioning is needed for read/write concurrency.

4 Multi-Version Implementation Principle

MySQL implements MVCC using undo logs, redo logs, rollback segments, and read view, enabling non‑blocking consistent reads.

When a write occurs, a new version is cloned; reads continue on the old version until commit, after which they see the new version.

Illustration:

Steps:

Initial version V1.0

T1 writes, creates V2.0 (in progress)

T2 reads V1.0

T3 reads V1.0

Write completes, V2.0 becomes visible

T4 reads V2.0

Thus MVCC allows read/write without blocking, greatly improving concurrency.

Locks are serial and inefficient.

Read‑write locks allow concurrent reads but not writes.

Multi Versioning enables true read/write concurrency.

5 MySQL Multi-Version Implementations

5.1 Concept Introduction

InnoDB uses MVCC based on undo log, redo log, rollback segment, and read view. Undo logs store previous row versions for rollback; redo logs ensure durability.

Redo Log : buffers changes for ordered disk writes, improves performance and recovery.

Undo Log : stores before‑image of rows for rollback and crash recovery.

Insert: stores PK for delete on rollback.

Delete/Update: stores full old row for restoration.

Rollback Segment : temporary storage of previous values, used during transaction rollback.

5.2 Example Explanation

5.2.1 Initial Data

# Table structure
t_userinfo(id PK, name, sex, age);

# Default data
1,Brand,0,22
2,Helenlyn,1,19
3,Sol,0,21

Initialize table with sample rows; rollback segment empty.

5.2.2 Transaction Operations Example

start transaction;
delete from t_userinfo where id = 1;
update t_userinfo set name = 'Helenlyn...' where id = 2;
insert into t_userinfo(name, sex, age) values ('Lili', 1, 18);

Transaction not yet committed.

Before commit:

Deleted row id=1 stored in rollback segment.

Updated row id=2 stored in rollback segment.

Inserted row id=4 stored in rollback segment.

If commit succeeds, changes become permanent and rollback logs are cleared.

If rollback occurs, all changes revert and logs are cleared.

6 Summary

MySQL uses locks and Multi Versioning for concurrency control.

Locks are serial; read‑write locks allow concurrent reads; Multi Versioning enables full read/write concurrency.

Redo log ensures ACID for committed transactions; undo log enables rollback; rollback segment holds temporary old versions.

InnoDB is an MVCC‑based storage engine.

InnoDB's snapshot reads are lock‑free, providing high concurrency.