A brief analysis of the transaction isolation level in MySQL and a discussion of its implementation principles

This article will take you to understand the transactions in MySQL and talk about the implementation principle of transaction isolation. I hope it can help you!

Speaking of database transactions, a lot of transaction-related knowledge must easily pop up in everyone's mind, such as the ACID characteristics of the transaction, isolation level, and solved problems (dirty read , non-repeatable read, phantom read), etc., but few people may really know how these features of transactions are implemented and why there are four isolation levels.

Today we will first talk about the implementation principle of transaction isolation in MySQL, and we will continue to publish articles to analyze the implementation principles of other features.

Of course MySQL is extensive and profound, and omissions in the article are inevitable. Criticisms and corrections are welcome.

Explanation

MySQL's transaction implementation logic is located at the engine layer, and not all engines support transactions. The following instructions are based on the InnoDB engine. .

Definition

Isolation refers to the fact that after different transactions are submitted and executed one after another, the final effect is serial. That is to say, for a transaction, it is During the execution process, the perceived data changes should only be caused by your own operations, and there should be no data changes caused by other transactions.

Isolation solves the problem of concurrent transactions.

Standard SQL isolation level

The simplest way to implement isolation is that each transaction is executed serially. If the previous transaction has not been completed, subsequent transactions will wait. However, this implementation method is obviously not very efficient in concurrency and is not suitable for use in actual environments.

In order to solve the above problems and achieve different levels of concurrency control, SQL standard makers have proposed different isolation levels: uncommitted read (read uncommitted), committed read (read committed), repeatable read (repeatable) read), serialized read (serializable). The most advanced isolation level is serialized read, and in other isolation levels, since transactions are executed concurrently, some problems are more or less allowed. See the following matrix table:

Note that MySQL's InnoDB engine solves the phantom read problem through gap locks at the repeatable read level, and solves the non-repeatable read problem through MVCC. See the analysis below for details.

Implementation Principle

Standard SQL Transaction Isolation Level Implementation Principle

The problem we encountered above is actually the control problem under concurrent transactions. The most common way to solve concurrent transactions is Pessimistic concurrency control (that is, locks in the database). The implementation of standard SQL transaction isolation level relies on locks. Let’s take a look at how it is implemented:

It can be seen that when only using locks to implement isolation level control, frequent locking and unlocking are required, and read and write conflicts are easy to occur (for example, at the RC level, transaction A updates data row 1, Transaction B must wait for transaction A to commit and release the lock before transaction A commits to read data row 1).

In order to solve the problem of read-write conflicts without locking, MySQL introduced the MVCC mechanism. For details, please see my previous analysis article: Understanding optimistic locks, pessimistic locks and MVCC in the database in one article.

InnoDB transaction isolation level implementation principle

Before proceeding with the analysis, we have several concepts that need to be understood first:

1. Locking read and consistency Non-locking read

Locked read: In a transaction, actively lock the read, such as SELECT ... LOCK IN SHARE MODE and SELECT ... FOR UPDATE. Row shared locks and row exclusive locks are added respectively. The classification of locks can be found in my previous analysis article: MySQL lock classifications you should know).

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Consistent non-locking reads: InnoDB Use MVCC to provide a snapshot of the database at a certain point in time to a transaction's queries. The query will see changes made by transactions committed before that point in time, but not changes made by later or uncommitted transactions (other than this transaction). That is to say, after starting a transaction, the data seen by the transaction is the data at the moment when the transaction is started, and subsequent modifications of other transactions will not be visible in this transaction.

Consistent read is the default mode for InnoDB to process SELECT statements at the RC and RR isolation levels. Consistent non-locking reads do not set any locks on the tables they access, so while performing consistent non-locking reads on the tables, other transactions can concurrently read or modify them.

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

2. Current read and snapshot read

The current read

reads the latest version, like UPDATE, DELETE, INSERT, SELECT... LOCK IN SHARE MODE, SELECT... FOR UPDATEThese operations are all current reads. Why are they called current reads? That is, it reads the latest version of the record. When reading, it must also ensure that other concurrent transactions cannot modify the current record, and the read record will be locked.

Snapshot reading

Reads the snapshot version, that is, the historical version. An unlocked SELECT operation is a snapshot read, that is, Non-blocking read without locking; The premise of snapshot read is that the isolation level is not uncommitted read and serialized read level, because uncommitted read always reads the latest data row, rather than the data row that conforms to the current transaction version , and serialized reading will lock the table.

3. Implicit locking and explicit locking

Implicit locking

InnoDB uses Two-stage lock protocol (without active display locking):

• Locking can be performed at any time, and InnoDB will automatically lock when needed based on the isolation level;
• Lock only It will be released when commit or rollback is executed, and all locks will be released at the same time.

Explicit locking

• InnoDB also supports explicit locking through specific statements (storage engine layer)

  select ... lock in share mode //共享锁
  select ... for update //排他锁

  Copy after login

• Display locking at the MySQL Server layer:

  lock table
  unlock table

  Copy after login

After understanding the above concepts, let’s take a look at how InnoDB transactions are implemented (below) Read refers to non-actively locked select)

可以看到，InnoDB通过MVCC很好的解决了读写冲突的问题，而且提前一个级别就解决了标准级别下会出现的幻读问题，大大提升了数据库的并发能力。

一些常见误区

幻读到底包不包括了delete的情况?

不可重复读：前后多次读取一行，数据内容不一致，针对其他事务的update和delete操作。为了解决这个问题，使用行共享锁，锁定到事务结束（也就是RR级别，当然MySQL使用MVCC在RC级别就解决了这个问题）

幻读：当同一个查询在不同时间生成不同的行集合时就是出现了幻读，针对的是其他事务的insert操作，为了解决这个问题，锁定整个表到事务结束（也就是S级别，当然MySQL使用间隙锁在RR级别就解决了这个问题）

网上很多文章提到幻读和提交读的时候，有的说幻读包括了delete的情况，有的说delete应该属于提交读的问题，那到底真相如何呢？我们实际来看下MySQL的官方文档（如下）

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT) is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

可以看到，幻读针对的是结果集前后发生变化，所以看起来delete的情况应该归为幻读，但是我们实际分析下上面列出的标准SQL在RR级别的实现原理就知道，标准SQL的RR级别是会对查到的数据行加行共享锁，所以这时候其他事务想删除这些数据行其实是做不到的，所以在RR下，不会出现因delete而出现幻读现象，也就是幻读不包含delete的情况。

MVCC能解决了幻读问题?

网上很多文章会说MVCC或者MVCC+间隙锁解决了幻读问题，实际上MVCC并不能解决幻读问题。如以下的例子：

begin;

#假设users表为空，下面查出来的数据为空

select * from users; #没有加锁

#此时另一个事务提交了，且插入了一条id=1的数据

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，所以更新成功，并生成一个更新的快照

select * from users; #读快照，查出来id为1的一条记录，因为MVCC可以查到当前事务生成的快照

commit;

可以看到前后查出来的数据行不一致，发生了幻读。所以说只有MVCC是不能解决幻读问题的，解决幻读问题靠的是间隙锁。如下：

begin;

#假设users表为空，下面查出来的数据为空

select * from users lock in share mode; #加上共享锁

#此时另一个事务B想提交且插入了一条id=1的数据，由于有间隙锁，所以要等待

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，由于不存在数据，不进行更新

select * from users; #读快照，查出来的数据为空

commit;

#事务B提交成功并插入数据

注意，RR级别下想解决幻读问题，需要我们显式加锁，不然查询的时候还是不会加锁的。

原文地址：https://segmentfault.com/a/1190000025156465

作者: X先生

【相关推荐：mysql视频教程】

The above is the detailed content of A brief analysis of the transaction isolation level in MySQL and a discussion of its implementation principles. For more information, please follow other related articles on the PHP Chinese website!