MySQL detailed explanation of transaction isolation mechanism and implementation principles

This article brings you relevant knowledge about mysql, which mainly introduces the use of MySQL transactions and the risks of long transactions, MySQL transactions and their characteristics, and the consequences of concurrent transactions Questions, transaction isolation levels and demonstrations, single version control locks and multi-version concurrency control MVCC, etc. Let’s take a look at them together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

##1. MySQL transaction usage

1. What is a transaction?

A transaction is a series of operations performed as a single logical unit of work. Either all or none of these operations are an integral unit of work.

For example, in the process of paying for something, there will be a series of operations, such as checking the balance, adding or subtracting, and updating the balance. These operations must be simultaneous. Otherwise, it will show that your payment was successful, but the system did not receive the money

In MySQL, transaction support is implemented at the engine layer, and the MyISAM engine does not support transactions

2. Transactions Four Characteristics

To become a transaction, a logical unit of work must satisfy four characteristics in a relational database management system.

The so-called ACID: atomicity, consistency, isolation, and durability.

Atomicity: All operations of the transaction are either completed or incomplete and will not end in the intermediate stages.

Consistency: The integrity limits of the database will not be broken before and after a transaction.

Isolation: Relationships are displayed when multiple transactions access the same data in the database at the same time.

Persistence: After the transaction is completed, the changes made by the transaction will be persisted and will not be lost.

ACID needs to be guaranteed through Redo and Undo logs. Detailed explanation of MySQL log system: (to be added later)

3. MySQL transaction usage

MySQL has the following two transaction startup methods:

3.1. Explicitly start transaction statement

begin or start transaction. The matching commit statement is commit and the rollback statement is rollback.

BEGIN -- 开启事务
START TRANSACTION -- 开启事务

INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );

COMMIT -- 提交事务

ROLLBACK -- 回滚事务

3.2. Turn off automatic submission

set autocommit=0, this command will turn off the automatic submission of this thread. This means that if you only execute a select statement, the transaction will be started and will not be automatically committed. This transaction persists until you actively execute a commit or rollback statement, or until you disconnect. (Not recommended!)

set autocommit=0 -- 关闭自动提交

INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );

COMMIT -- 提交事务

ROLLBACK -- 回滚事务

2. MySQL transaction isolation and isolation mechanism

When multiple transactions are executed simultaneously on the database (can be understood as multi-tasking, Concurrency scenarios), dirty reads, non-repeatable reads, and phantom reads may occur.

1. Four isolation levels

In order to solve these problems, MySQL introduced the concept of "isolation level".

The higher the isolation level, the lower the efficiency. Many times, we must find a balance between the two. SQL standard transaction isolation levels include: read (read) uncommitted (read uncommitted), read (read) committed (read committed), repeatable read (repeatable read) and serializable (serializable).

Read Uncommitted (RU, Read Uncommitted), which can read the intermediate process of the transaction. It violates ACID properties and has dirty read issues. Therefore it is basically not used and can be ignored.

Read Committed (RC, Read Committed), which means that if other transactions have been committed, we can see that this is also the most commonly used level. However, due to some historical reasons, RC may not be used much in production environments.

Repeatable Read (RR, Repeatable Read) is currently the most widely used level. It features gap locking, which is still the default level. At this level, deadlocks, low concurrency, and other problems often occur.

Serialization (serializable) is not a multi-version implementation, but a single-version implementation, because all its implementations are implemented through locks. Basically not used and can be ignored.

2. Problems caused by concurrent transactions

Dirty reading: Transaction A reads the data updated by transaction B, and then B rolls back, so The data read by A is dirty (data that should not actually exist)

Non-repeatable read: Transaction A reads the same data multiple times. Transaction B updates and commits data during multiple reads of transaction A, resulting in inconsistent results when transaction A captures the same data multiple times.

A transaction reads data submitted by another transaction, causing the data read twice before and after to be different

幻读：A查出来数据，此时B提交，A再次查同一数据时结果不一致。一个事务前后两次读取的数据不一致，是因为其他事务插入数据导致的事务并发情况

不可重复读和幻读很容易混淆，不可重复读侧重于修改，而幻读侧重于添加或删除。要解决不可重复读取的问题，只需要锁，符合条件的行，而要解决幻读问题需要锁表。

3、隔离级别问题剖析与演示

3.1 查看mysql事务隔离级别

SELECT @@transaction_isolation; -- 查看mysql事务隔离级别
SELECT @@tx_isolation;          -- 查看mysql事务隔离级别

3.2、脏读问题

将事务隔离级别修改为读未提交，可以看到，事务还没有提交，这时候去查询这条数据，发现数据已经可见了。

set session transaction isolation level read uncommitted; -- 设置成读未提交
SELECT @@tx_isolation;          -- 查看mysql事务隔离级别

START TRANSACTION -- 事务A
INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );
ROLLBACK

select * from fork_business_detail where id= 4 -- 事务B

3.3、不可重复读

一个事务读取到其他事务已提交的数据导致前后两次读取数据不一样的情况。

select * from fork_business_detail where id= 4;

BEGIN; -- 开启事务
select * from fork_business_detail where id= 4;
UPDATE fork_business_detail set SUB_ODR_ID=123004 where id= 4;
COMMIT;

select * from fork_business_detail where id= 5;

三、MySQL事务实现原理

1、单版本控制——锁

serializable ，使用锁独占方式来确保只有一个版本时事务被隔离，因此锁可以理解为单版本控制。

在MySQL事务中，锁的实现与隔离级别有关。在RR（Repeatable Read）隔离级别下，MySQL使用间隙锁来防止以并行性为代价写入数据，以解决虚拟读取的问题。

这种类型的锁通常会导致死锁，因为它没有足够的并行性和许多冲突。现在流行的Row模式可以避免许多冲突甚至死锁，因此建议默认使用Row+RC（Read Committed）模式隔离级别，这可以大大提高数据库的读写并行性。

2、多版本控制MVCC

多版本控制，也称为MVCC，是指数据的多版本处理，以实现数据库中的高度并发数据访问，以及事务的可见性，以确保事务可以看到其应该看到的数据版本。

如何生成多个版本？

每次修改数据库时，撤消（ Undo log）日志都会记录当前修改记录的事务号和修改前数据状态的存储地址（即ROLL_PTR），以便在必要时回滚旧数据版本。

例如，读取事务查询当前记录，但最近的事务尚未提交。根据原子性，读取事务无法看到最新的数据，但您可以在回滚段中找到旧版本数据，从而生成多个版本。

多版本控制巧妙地将独占和独占的稀有资源转换为并发，大大提高了数据库吞吐量和读/写性能。

推荐学习：mysql视频教程

The above is the detailed content of MySQL detailed explanation of transaction isolation mechanism and implementation principles. For more information, please follow other related articles on the PHP Chinese website!