The purpose of a transaction is to achieve read consistency. There are two methods: locking (lock-based concurrency control LBCC) and multi-version concurrency control MVCC. Locking is the lock corresponding to the four isolation levels.

The MVCC specification is as follows: The snapshot version is determined on the first query,

It can read the changes committed by transactions created after the snapshot is created. It cannot read the changes committed by transactions created after the snapshot is created

Changes (including uncommitted) of the current transaction can be read, and changes (including uncommitted) of other transactions cannot be read;

InnoDB provides three hidden fields for each table,

A diagram is used to understand transaction ids and delete version numbers

Navicat opens a session, Transaction 1 inserts two data,



Transaction 2 indicates that the snapshot version is determined on the first query,

The execution sequence is,

Transaction 1 Inserts two data

The Transaction 2 queries

Transaction 3 The third data newly inserted

The Transaction 2 queries

Transaction 4 Deletes the second data

The Transaction 2 queries

Transaction 5 Modify the first data

The Transaction 2 queries

After the snapshot version is determined by Tranaction 2, Transaction 3 inserts the third data, Transaction 4 deletes the second data, and Transaction 5 modifs the first data. Because the lookup is already established, you can only see the two data that Transaction 1 originally inserted.

The underlying principle of MVCC is read View. Each transaction, the first query, will be created

Number one, this transaction, create View number four

Second, the previous transaction, create View second

Number three, the next transaction, Create View number three

If you are active (not committed), you cannot read it.

For MVCC, different table types generate snapshots at different times. Only Repeat Read uses MVCC to solve the phantom read problem (update, delete, insert).



Limitations of MVCC: For read consistency, only the view queried for the first time can be saved, but the latest data cannot be queried.

From table locking to row locking: MyISAM only supports table locking, InnoDB supports table locking and row locking. Row locking is an improvement for InnoDB while ensuring data read consistency.

Table locks are:

Table locking and row locking



The following are row-level locks, because only the row-level locks are the essence of InnoDB, while ensuring read consistency to achieve relatively high efficiency.

Lock to transaction relationship: Lock is an implementation of read consistency (another implementation is MVCC), read consistency can achieve transaction isolation, the implementation of transaction isolation provides four different transaction isolation levels, transaction isolation level is a major feature of transactions.

InnoDB row lock (8 classes) :

Basic locks: read shared lock, write exclusive lock, intent lock

Locking algorithms (locking what range under what circumstances) : record locking, gap locking, near key locking

Unimportant: Insert intent lock, autoincrement lock,

Shared lock: lock is not allowed to be modified, so that when you read, you can read the same, but you can not modify

Different transactions can have read shared locks on the same row, but no one can change them anyway.

As with other row-level locks, if the Navicat session is disconnected, the lock is released.

Question: What is the meaning of these two table locks (intentional shared locks and intentional exclusive locks)?

One transaction locks a row of data (shared lock, exclusive lock). If another transaction wants to lock the row, it must scan the entire table, which is too slow. So, Mysql design

If a transaction assigns a read-shared lock to a row of data, assign an intended shared lock to the table first. If a transaction assigns a write-exclusive lock to a row of data, then assigns an intent exclusive lock to the table. The reason for this design is that a transaction can lock a table only if no transaction locks a row on any of the records in the table.

In this way, before each transaction adds a row lock, just look at the table to see if there is an intention to share lock and intention to exclude lock, as long as there is, do not add lock, always improve judgment efficiency.

Therefore, these two table locks (intentional shared lock, intentional exclusive lock) greatly improve the efficiency of different transactions to add row locks to the table, which is the biggest significance of the existence of these two table locks.

What range is locked under what circumstances? First understand the three concepts of record, gap and key

Indexed columns, whether integers or characters, can be sorted.

If the WHERE condition does not match the row, that is, the WHERE condition does not find the data in the database,

Gap locks and gap locks themselves do not conflict because they are designed specifically to block inserts. Gap locks are only available in InnoDB’s repeatable read isolation level. There are no gap locks for uncommitted and committed reads in InnoDB, which is why using repeatable can avoid phantom errors.

Select * from table_name where id>4 and id<7; select * from table_name where id=6; select * from table_name where id=6;

If you use WHERE ID >20 it will lock (10, positive infinity) and if you use INSERT ID = 11 it will fail.

MVCC is only available in RC RR,

At Read Uncommited, nothing, no locks, no MVCC,

In RC, there is MVCC, there is lock, only record lock, no gap lock, so there is no solution to the illusory problem

Inside the RR, there’s the MVCC, there’s the lock,

In Serializable, only locks, no MVCC