The isolation and lock of MySQL

Isolation properties of InnoDB

InnoDB's transaction supports 4 isolation mechanisms, namely READ UNCOMMITTED, read COMMITTED, Repeatable read, and SERIALIZABLE. The default is repeatable READ.

The following is a detailed analysis of the 4 types of isolation of the linkages and differences.

Repeatable READ

In a query statement that does not have locks, this isolation level guarantees that the data read from each transaction is consistent.

In a lock-in query (update, etc.), if the query is a specified index query, then only one value in the lock index is locked, and if it is a range query for the specified index, then the gap in the range is locked, that is, the gap lock.

This isolation level is subject to repeated reads.

When you query two times in a transaction, you may return a different result because at the end of the first query, the isolation level locks only the corresponding values and values, and the other transaction inserts a value that does not exist (that is, a value that is not locked), which results in inconsistencies in the result of the two queries.

Explanation of repeated reading problems

Dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

READ COMMITTED

than repeatable read weak level, when the index query is specified, if it is a range query then does not add a gap lock, will only lock a value, which will lead to phantom read problems.

For example:

When you have an index column, the data is 1,3,5, when you query this column >1 data, because there is no gap lock exists, MySQL will only lock 3 and 5, and this time there is another transaction inserted 4, and then rollback, then the previous query will query 4, and then the query can not find 4, This is the question of phantom reading.

Phantom reading means that when a transaction executes two times, no rows are read at a time, the second is read, and the line is called a magical line.

Interpretation of Phantom Reading

Dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

READ UNCOMMITTED

A weaker level than read Committed, without lock isolation, can cause dirty reads. This is due to the fact that when some rows are not locked, other transactions can be updated to cause the read to dirty data.

Dirty reads mean that the data read to the other transaction has been modified but not yet committed.

SERIALIZABLE

On the basis of repeatable read, the ' lock in Share ' statement is automatically added. That is, during the select process, other operations that require an exclusive lock are not allowed to occur (update, etc.), allowing only operations that require a shared lock.

This isolation level guarantees full consistency of data reads, but the level causes all operations that require exclusive locks to become serial execution, and the performance and table locks are no different.

Reference

MySQL Terminology encyclopedia

Dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read

MySQL website on the isolation of the introduction

Dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Table-level lock and row-level lock table-level lock

The MyISAM database engine only supports table-level locks, including table-level shared and table-level exclusive locks, until InnoDB has yet to appear.

A shared lock supports simultaneous execution of multiple operations, i.e. concurrent execution, in which the other operation cannot acquire an exclusive lock in the event that an operation has a shared lock.

An exclusive lock can only be executed serially.

Row-level Locks

InnoDB supports row locks, which refine the granularity of locked objects to increase concurrency performance.

It is important to note that row-level locks are built on top of the index, which means that table locks are used in cases where the query does not have an indexed field.

Reference

A detailed comparative analysis of the MyISAM engine and the InnoDB engine

Baijiahao.baidu.com/s?id=1610581108528334819&wfr=spider&for=pc

About the details of read consistency in non-blocking situations

In InnoDB's storage engine scenario, MySQL gives a snapshot of the current table each time it is queried, meaning that other committed transactions are perceived before the query starts, and the other committed transactions are not perceived until the query is started.

For example

When you select COUNT (*) from table, you actually count the snapshot of the current table, do not acquire any locks, and if there is another insert operation at the same time, then the two operations can be completely simultaneous. Of course, this result may not be what you want to see in the business, perhaps you want strong consistency, that is, when the amount of statistical data do not allow other insertions or deletions, then you can first lock table; Select COUNT (*) from table; Finally unlock table, and the way to ensure the strong consistency of data through table lock.