Compréhension approfondie des transactions, de 4 fonctionnalités majeures et des niveaux d'isolement dans MySQL

Cet article est une étude avancée de MySQL. Il vous donnera une introduction détaillée aux transactions dans MySQL, les quatre fonctionnalités principales (ACID) et le niveau d'isolation des transactions. J'espère qu'il vous sera utile !

【Recommandation associée : Tutoriel vidéo mysql】

La version de l'environnement utilisée pour le fonctionnement et les tests de cet article est 5.7.215.7.21

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.00 sec)

记住：我们常见的MySQL存储引擎中只有InnoDB是支持事务的。所以下面的操作也都是在InnoDB下做的。

一. 什么是事务

事务就是现实中抽象出来一种逻辑操作，要么都执行，要么都不执行，不能存在部分执行的情况。

比较经典的案例就是银行转账：小A向小B转账100元

正常的情况：小A的账户扣减100元，小B的账户增加100元。

非正常情况: 小A的账户扣减100元，小B账户金额不变。

非正常情况下，小A账户扣减100之后银行系统出现问题，小B账户增加100元的操作并没有执行。也就是两边金额对不上了，小A不愿意，小B不愿意，银行也不愿意啊。事务的出现就是为了避免非正常情况的出现，让大家都满意。

二. 事务的4大特性（ACID）

1. 原子性（Atomicity）

事务的操作是不可分割的，要么都操作，要么都不操作，就像转账一样，不存在中间状态。而且这个原子性不是说只有一个动作，可能会有很多的操作，但是从结果上看是不可分割的，也就是说原子性是一个结果状态。

2. 一致性（Consistency）

执行事务的前后，数据保持一致，就像银行账户系统一样无论事务是否成功，两者的账户总额应该是一样的。

3. 隔离性（Isolation）

多个事务同时操作数据的时候，多个事务直接互相隔离，不会互相影响。

4. 持久性（Durability）

一个事务在提交后对数据的影响是永久的，写入磁盘中不会丢失。

三. 显式事务、隐式事务

mysql的事务分为显式事务和隐式事务,默认的事务是隐式事务，由变量autocommit 在操作的时候会自动开启，提交，回滚。

控制的关键命令如下

set autocommit=0; -- 关闭自动提交事务（显式）
set autocommit=1; -- 开启自动提交事务（隐式）
  -- 当autocommit=0的时候手动控制事务
rollback; -- 回滚事务
commit;  -- 提交事务
-- 当autocommit=1 自动提交事务，但是可以控制手动提交
start transaction; -- 开启事务（或者用begin开启事务）
commit; -- 提交事务
rollback; -- 回滚事务
SAVEPOINT 保存点名称;  -- 保存点(相当于存档，可以不用回滚全部操作)
rollback to  保存点;  -- 回滚到某个保存点 （这个后面就不测试，知道有这个操作就行）

先建一张表ajisun

mysql> create table ajisun(id int(5), name varchar(20) character set utf8  COLLATE utf8_bin ) engine=innodb character set= utf8mb4 COLLATE = utf8mb4_bin;
Query OK, 0 rows affected (0.03 sec)

1. 隐式事务

-- 看下当前autocommit的状态是，默认是on状态
mysql> show variables like 'autocommit'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

--  插入一条数据
mysql> insert into ajisun values(1,'阿纪');
Query OK, 1 row affected (0.00 sec)
mysql> rollback;

-- 执行rollback 也是没有效果的，还是能够查询到插入的数据（不需要我们手动控制commit）
mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 阿纪   |
+------+--------+
1 row in set (0.00 sec)

2. 显式事务方式1

显式事务由我们自己控制事务的开启，提交，回滚等操作

-- 开启显式事务-回滚
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 阿纪   |
+------+--------+
1 row in set (0.00 sec)

mysql> insert into ajisun values(2,'纪先生');
Query OK, 1 row affected (0.00 sec)
-- 插入后可以看见2条数据
mysql> select * from ajisun;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 阿纪      |
|    2 | 纪先生    |
+------+-----------+
2 rows in set (0.00 sec)
-- 回滚之后上面插入的数据就没了
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 阿纪   |
+------+--------+
1 row in set (0.00 sec)

-- 插入一条数据
mysql> insert into ajisun values(2,'ajisun');
Query OK, 1 row affected (0.01 sec)
-- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- 回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
-- 先提交commit，在rollback 数据依然存在，说明commit生效，事务已提交，回滚就不生效了。
mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 阿纪   |
|    2 | ajisun |
+------+--------+
2 rows in set (0.00 sec)

3. 显式事务方式2

使用start transaction

先改成默认的事务 set autocommit=1;

-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from ajisun where id=1;
Query OK, 1 row affected (0.00 sec)
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    2 | ajisun |
+------+--------+
1 row in set (0.00 sec)

-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from ajisun where id =2;
Query OK, 1 row affected (0.01 sec)
-- 回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
-- 删除操作失效了
mysql> select * from ajisun;
+------+--------+
| id   | name   |
+------+--------+
|    2 | ajisun |
+------+--------+
1 row in set (0.00 sec)

四. 并发事务中的问题

如果对表的操作同一时间只有一个事务就不会有问题，但是这是不可能的。现实中都是尽可能的利用，多个事务同时操作。多个事务就会带来不少的问题，例如脏读，脏写，`不可重复读，幻读

MySQL默认采用的 REPEATABLE_READ 隔离级别 
Oracle默认采用的 READ_COMMITTED 隔离级别

Rappelez-vous :

Dans notre MySQL commun moteur de stockage Seul InnoDB prend en charge les transactions. Par conséquent, les opérations suivantes sont également effectuées sous InnoDB.

1. Qu'est-ce qu'une transaction

Une transaction est une opération logique abstraite de la réalité , soit tous sont exécutés, soit aucun n'est exécuté, et il ne peut pas y avoir d'exécution partielle.

Un cas plus classique est le virement bancaire : Little A transfère 100 yuans à Little B

Situation normale : le compte de Little A est déduit de 100 yuans, et le compte de Little B est augmenté de 100 yuans.

Situation anormale : le compte du petit A est déduit de 100 yuans et le montant du compte du petit B reste inchangé.

Dans des circonstances anormales, il y a eu un problème avec le système bancaire après que le compte de Little A ait été déduit de 100 yuans, et l'opération consistant à ajouter 100 yuans au compte de Little B n'a pas été exécutée. C’est-à-dire que les montants des deux côtés ne correspondent pas. Le petit A n’est pas disposé, le petit B n’est pas disposé, et la banque non plus. Le but des affaires est d'éviter les situations anormales et de satisfaire tout le monde.

2. 4 caractéristiques majeures des transactions (ACID)

1. )

Les opérations de transactions sont indivisibles, soit toutes les opérations, soit aucune, tout comme les transferts, il n'y a pas d'état intermédiaire. Et cette atomicité ne signifie pas qu’il n’y a qu’une seule action, il peut y avoir plusieurs opérations, mais elle est indivisible du résultat, ce qui signifie que l’atomicité est un état résultat.

2. Cohérence

Les données restent cohérentes avant et après l'exécution de la transaction, tout comme le système de compte bancaire, que la transaction réussisse ou non. , les données entre les deux sont cohérentes. Le total du compte doit être le même.

3. Isolation

Lorsque plusieurs transactions exploitent des données en même temps, plusieurs transactions sont directement isolées les unes des autres et ne s'affecteront pas.

4. Durabilité

L'impact d'une transaction sur les données après la soumission est permanent et ne sera pas perdu lors de l'écriture sur le disque.

🎜🎜3. Transactions explicites et transactions implicites🎜🎜🎜Les transactions MySQL sont divisées en transactions explicites et Transaction implicite , la transaction par défaut est une transaction implicite et la variable autocommit démarrera, validera et annulera automatiquement pendant l'opération. 🎜🎜Les commandes clés pour le contrôle sont les suivantes🎜

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

🎜Créez d'abord une table ajisun🎜

set global transaction_isolation='read-uncommitted';
set global transaction_isolation='read-committed';
set global transaction_isolation='repeatable-read';
set global transaction_isolation='serializable';

🎜🎜1. Transaction implicite🎜🎜

mysql> set global transaction_isolation='serializable';
Query OK, 0 rows affected (0.01 sec)
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)
-- 当前会话（设置之前就已经存在的会，级别是默认的）
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

🎜🎜2. Mode de transaction explicite 1🎜🎜🎜Nous contrôlons nous-mêmes l'ouverture de la transaction. commit, rollback et autres opérations🎜

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)

set session transaction_isolation='read-uncommitted';
set session transaction_isolation='read-committed';
set session transaction_isolation='repeatable-read';
set session transaction_isolation='serializable';

🎜🎜3. Mode de transaction explicite 2🎜🎜🎜Utiliser start transaction🎜🎜Premier changement vers la transaction par défaut set autocommit=1;🎜

mysql> set session transaction_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

🎜🎜4. Problèmes dans les transactions simultanées🎜🎜🎜S'il n'y a qu'une seule transaction opérant sur la table en même temps, il n'y aura pas de problème, mais c'est impossible. En réalité, il est utilisé autant que possible et plusieurs transactions sont effectuées en même temps. Plusieurs transactions entraîneront de nombreux problèmes, tels que lecture sale, écriture sale, `lecture non répétable, lecture fantôme 🎜🎜🎜1. Lecture sale🎜🎜🎜🎜Une transaction lit les données modifiées d'une autre transaction non validée. 🎜🎜🎜Par exemple, deux transactions a et b : exploiter un enregistrement en même temps🎜🎜Après que la transaction a modifie l'enregistrement, il n'a pas été officiellement soumis à la base de données. À ce moment, la transaction b le lit, puis l'utilise. les données lues pour les opérations ultérieures. 🎜🎜Si la transaction a est annulée et que les données modifiées n'existent plus, alors la transaction b utilise des données inexistantes. Ce sont des données sales. 🎜🎜🎜2. Dirty write (perte de données) 🎜🎜🎜🎜Une transaction modifie les données modifiées par une autre transaction non validée 🎜🎜🎜Par exemple, deux transactions a, b : opèrent un enregistrement en même temps 🎜🎜une transaction ne le fait pas modifiez-le Soumettez, puis la transaction b modifie également la même donnée, puis la transaction b valide les données. 🎜🎜Si la transaction a annule ses propres modifications, ainsi que les modifications de la transaction b, le problème causé est le suivant : la transaction b est modifiée et soumise, mais la base de données n'a pas changé. Cette situation est appelée écriture sale. 🎜🎜🎜3. Lecture non répétable🎜🎜🎜🎜Une transaction ne peut lire que les données modifiées par une autre transaction qui a été soumise, et chaque fois que d'autres transactions modifient et soumettent les données, la transaction peut interroger et obtenir la dernière valeur. 🎜

也就是在同一个事务中多次读取同一条记录，得到的内容都不一样（在每次读取之前都有其他事务完成修改并提交），这就是不可重复读。

4. 幻读

在一个事务内 相同条件查询数据，先后查询到的记录数不一样

也就是一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读

不可重复读和幻读的区别：不可重复读重点在于同一条记录前后数据值不一样（内容的变化），而幻读重点在于相同查询条件前后所获取的记录数不一样（条数的变化）

五. 事务的隔离级别

上面说的事务的并发问题，在不同的场景下要求不一样，能接受的问题也不一样。他们之间的严重性排序如下：

脏写 > 脏读 > 不可重复读 > 幻读

MySQL中提供了4种隔离级别来处理这几个问题，如下

隔离级别	脏读	不可重复读	幻影读
READ- UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读和幻读。但是并发度最高
• READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读和不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。并发度也是最低的

MySQL默认采用的 REPEATABLE_READ 隔离级别 
Oracle默认采用的 READ_COMMITTED 隔离级别

1. 如何设置隔离级别

可以通过变量参数transaction_isolation 查看隔离级别

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

修改的命令：SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL $[level];

level的值就是4中隔离级别READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ SERIALIZABLE

设置全局隔离级别

只对执行完该语句之后产生的会话起作用。

当前已经存在的会话无效。

set global transaction_isolation='read-uncommitted';
set global transaction_isolation='read-committed';
set global transaction_isolation='repeatable-read';
set global transaction_isolation='serializable';

例如：

会话A

mysql> set global transaction_isolation='serializable';
Query OK, 0 rows affected (0.01 sec)
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)
-- 当前会话（设置之前就已经存在的会，级别是默认的）
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

会话B(set之后新建的会话)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)

设置会话的隔离级别

对当前会话的所有后续的事务有效

该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。

如果在事务之间执行，则对后续的事务有效。

set session transaction_isolation='read-uncommitted';
set session transaction_isolation='read-committed';
set session transaction_isolation='repeatable-read';
set session transaction_isolation='serializable';

比如：

会话A

mysql> set session transaction_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

新建会话B(依然是默认的级别：可重复读)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

2. 怎么选择隔离级别

一般情况下默认的可重复读就好了，一般很少改这个，除非业务场景特殊

记住一点：隔离级别越高，并发问题就越少，但并发性也就越低，所以还是要根据业务选择来。

六. 总结

• 事务的四大特性：原子性，一致性，隔离性，持久性

• 事务的常见命令：

  set autocommit=0/1; -- 关闭/开启自动提交事务
  start transaction; -- 开启事务（或者用begin）
  rollback; -- 回滚事务
  commit; -- 提交事务

• 并发事务的问题：脏写 > 脏读 > 不可重复读 > 幻读

• 需要熟悉事务的4种隔离级别以及MySQL默认级别

• 怎么设置隔离级别（global，session）

更多编程相关知识，请访问：编程入门！！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!