A concept,

Concurrency Control, MVCC, Multi-version Concurrency Control When accessing a database concurrently (read or write), multiple versions of the data being processed within a transaction are managed to avoid concurrency problems caused by read failures caused by write operations.

Two, the essential basic knowledge

1. Lock type

Shared Locks (S Locks)

Exclusive Locks (X Locks)

Intention Shared Locks (IS Locks)

Intention Exclusive Locks (IX)

Auto-inc Locks

2. Shared lock (S) and exclusive lock (X)

A Shared lock

Also known as read lock, referred to as S lock, as the name implies, shared lock is multiple transactions for the same data can share a lock, can access the database, but can only read can not modify;Copy the code

Exclusive lock

Also known as write locks, referred to as "X lock, exclusive lock cannot coexist with other lock, such as a transaction to obtain a data line exclusive locks, other transactions can no longer access to career change lock (including Shared locks and exclusive locks), only the current affairs of access to the exclusive lock can be read the data and modify (other transactions at this time to read the data can be obtained from the snapshot)Copy the code

3. What exactly does InnoDB row lock lock?

InnoDB locks rows by locking index entries on indexes. InnoDB uses row-level locks only for data retrieval by index criteria. Otherwise, a table lock (which locks all records of the index) is used.

If our delete/modify statement does not hit the index, the entire table will be locked, which has a significant performance impact.

Three, MVCC, and its implementation principle

1, the undo log

The undo log and redo log form the MySQL transaction log, and WAL includes the redo log as well as the undo log. Innodb records the corresponding undo log first

So what is an undo log? As the name implies, unlike redo log, which is used to resubmit data after disaster, undo Log is used to roll back data changes

Using undo log, InnoDB implements ACID’s C Consistency

2. MVCC implementation

Concurrency Control is an important feature of InnoDB for concurrency and rollback

In each row of the database, add three additional fields:

  • DB_TRX_ID — Records the transaction ID of the last transaction to insert or update the row
  • DB_ROLL_PTR — pointer to undolog corresponding to the row change
  • DB_ROW_ID — The monotonically increasing row ID that is the primary key ID of AUTO_INCREMENT

3. Snapshot read and current read

reference

InnoDB lock mechanism in 5 minutes

The MVCC mechanism of MySQL is explained in this paper