• Dirty read: Data that sees uncommitted changes
  • Phantom read: See newly created data that has not been committed

MVCC

The multiple Version concurrency Control (MVCC) for MySql is an concurrency control that allows for the concurrency control of multiple versions

ACID

  • Atomicity (Atomic)
    • Operations cannot be divided. Start, rollback, or commit control
  • Consistency (Consistency)
    • Data relationships are not corrupted before and after transaction execution
    • Tools: lock
  • Isolation (Isolation)
    • Execution isolation between multiple transactions (concurrency support)
    • The default isolation level is RR(Repeatable Read)
    • How does Innodb prevent dirty reads
    • How does Innodb prevent phantoms
    • Concurrency Control for multiple Version Concurrency Control
  • Durability (Durability)

How does Innodb prevent dirty reads

The current transaction can only see rows smaller than its version number, not rows larger than its version number

How does Innodb prevent phantoms

Insert example, same principle

Delete example, add a delete mark version number

The downside of MVCC is that it produces redundant data

MySql isolation level, default isolation level is RR(Repeatable Read)

MySql lock

Graph LR RR[RR] --> weak RR --> no dirty reads and weak --> RU[Read Uncommited] RU --> allow weak --> RC[Read Commited] RC --> only Read committed data RC --> No dirty Read is allowed. RR --> strict --> Serializable serialization --> Lock every row Read