La colonne
tutoriel mysql présente les transactions liées à MySQL, le niveau d'isolement et MVCC.
Le quatrième article de la série MySQL, le contenu principal est les transactions, y compris les caractéristiques ACID des transactions, les niveaux d'isolement, les lectures sales, les lectures non répétables , et des lectures fantômes Comprendre et contrôler la concurrence multiversion (MVCC) et d'autres contenus.
La transaction peut garantir qu'un ensemble indivisible d'opérations atomiques sera soit toutes exécutées, soit aucune d'entre elles ne sera exécutée. Parmi les moteurs de stockage couramment utilisés dans MySQL, InnoDB prend en charge les transactions, mais le moteur natif MyISAM ne prend pas en charge les transactions.
Dans cet article, sauf indication contraire, les tableaux de données et les données utilisées sont les suivants :
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(12) DEFAULT NULL) ENGINE = InnoDB;insert into user values(1, '刺猬');复制代码
La première chose dont vous avez besoin. comprendre ce que sont les transactions Les quatre caractéristiques principales d'ACID sont l'atomicité, la cohérence, l'isolement et la durabilité, qui sont également les quatre éléments de base d'une transaction.
Afin d'expliquer en détail les caractéristiques de l'ACID, voici un scénario : je vous transfère 100 yuans.
Supposons que cette opération puisse être divisée en les étapes suivantes (en supposant que le solde de mon compte et le vôtre soient tous deux de 100 yuans) :
L'atomicité d'une transaction signifie : une transaction doit être la plus petite unité de travail qui ne peut pas être davantage divisée. Les opérations dans une transaction réussiront ou échoueront. situation de fonctionnement partiel.
Dans le scénario de transfert ci-dessus, l'atomicité exige que ces cinq étapes soient toutes exécutées ou aucune. Il est impossible que mon compte débite 100 yuans, mais votre compte n'est pas arrivé.
La cohérence d'une transaction signifie que la base de données passe toujours d'un état de cohérence à un autre état de cohérence. La cohérence se concentre sur la visibilité des données, l'état intermédiaire des données. invisible pour le monde extérieur.
同时，事务的一致性要求符合开发人员定义的约束，如金额大于0、身高大于0等。
在上述的转账场景中，一致性能够保证最终执行完整个转账操作后，我账户的扣款金额与你账户到账金额是一致的，同时如果我和你的账户余额不满足金额的约束（如小于0），整个事务会回滚。
事务的隔离性是指：在一次状态转换过程中不会受到其他状态转换的影响。
假设我和你都有100元，我发起两次转账，转账金额都是50元，下面使用伪代码来表示的操作步骤：
read my
my=my-50
read yours
yours=yours+50
如果未保证隔离性就可能发生下面的情况：
| 时刻 | 第一次转账 | 第二次转账 | 我的账户余额 | 你的账户余额 |
|---|---|---|---|---|
| 1 | read my(100) | my=100 | yours=100 | |
| 2 | read my(100) | my=100 | yours=100 | |
| 3 | my=my-50=100-50=50 | my=50 | yours=100 | |
| 4 | read yours(100) | my=my-50=100-50=50 | my=50 | yours=100 |
| 5 | yours=yours+50=100+50=150 | my=50 | yours=150 | |
| 6 | read yours(150) | my=50 | yours=150 | |
| 7 | yours=yours+50=150+50=200 | my=50 | yours=200 | |
| 7 | end | end | my=50 | yours=200 |
两次转账后，最终的结果是我的账户余额为50元，你的账户余额为200元，这显然是不对的。
而如果在保证事务隔离性的情况下，就不会发生上面的情况，损失的只是一定程度上的一致性。
事务的持久性是指：事务在提交以后，它所做的修改就会被永久保存到数据库。
在上述的转账场景中，持久性就保证了在转账成功之后，我的账户余额为0，你的账户余额为200。
在 MySQL 中，我们可以通过 begin 或 start transaction 来开启事务，通过 commit 来关闭事务，如果 SQL 语句中没有这两个命令，默认情况下每一条 SQL 都是一个独立的事务，在执行完成后自动提交。
比如：
update user set name='重塑' where id=1;复制代码
假设我只执行这一条更新语句，在我关闭 MySQL 客户端然后重新打开一个新的客户端后，可以看到 user 表中的 name 字段值全变成了「重塑」，这也印证了这条更新语句在执行后已被自动提交。
自动提交是 MySQL 的一个默认属性，可以通过 SHOW VARIABLES LIKE 'autocommit' 语句来查看，当它的值为 ON 时，就代表开启事务的自动提交。
mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | ON | +---------------+-------+1 row in set (0.00 sec)复制代码
我们可以通过 SET autocommit = OFF 来关闭事务的自动提交。
然而，即便我们已经将 autocommit 变量的值改为 OFF 关闭事务自动提交了，在执行某些 SQL 语句的时候，MySQL 还是会将事务自动提交掉，这被称为隐式提交。
会触发隐式提交的 SQL 语句有：
create, drop, alter, truncate
create/drop user, grant, set password
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | begin; | |
| 2 | update user set name='重塑' where id=1; | |
| 3 | select name from user where id=1;(N1) | |
| 4 | begin; | |
| 5 | select name from user where id=1;(N2) |
Il y a deux instructions de requête N1 et N2 dans la transaction B. Le résultat de l'exécution est N1=hedgehog et N2=reshape, ce qui peut être prouvé.
Le niveau d'isolement d'une transaction précise la visibilité des modifications apportées dans une transaction au sein et entre les transactions. Des niveaux d'isolation inférieurs permettent généralement une concurrence plus élevée et une charge système inférieure.
Il existe quatre niveaux d'isolement des transactions définis dans la norme SQL, à savoir Read Uncommit, Read Comended, Repeatable Read, Seriallessly (Sérialisable).
Afin d'expliquer en détail ces quatre niveaux d'isolement et leurs phénomènes respectifs, supposons que deux transactions sont sur le point d'être exécutées. Le contenu de l'exécution est le suivant :
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | begin; | |
| 2 | begin; | |
| 3 | update user set name='重塑' where id=1; | |
| 4 | select name from user where id=1;(N1) | |
| 5 | commit; | |
| 6 | select name from user where id=1;(N2) | |
| 7 | commit; | |
| 8 | select name from user where id=1;(N3) |
Exécuté en transaction. A et transaction B Dans le processus, il existe trois requêtes pour N1, N2 et N3. Sous chaque niveau d'isolement, leurs valeurs sont différentes, qui sont discutées ci-dessous.
Sous le niveau d'isolement Lecture non validée, les modifications dans une transaction, même si elles ne sont pas validées, sont visibles par les autres transactions.
Dans le scénario ci-dessus, si le niveau d'isolement de la base de données est lu non validé, puisque la transaction A peut lire les données modifiées de la transaction non validée B, c'est-à-dire que la modification de la transaction B au moment 3 est visible par la transaction A, donc N1=remodeler, N2=remodeler, N3=remodeler.
Sous le niveau d'isolement Lecture validée, les modifications dans une transaction ne seront pas visibles par les autres transactions jusqu'à ce qu'elles soient validées.
Dans le scénario ci-dessus, si le niveau d'isolement de la base de données est validé en lecture, puisque la transaction A ne peut lire les données qu'après la soumission de la transaction B, c'est-à-dire que la modification de la transaction B au temps 3 n'est pas visible à la transaction A, N2 La requête à est effectuée après la soumission de la transaction B, elle est donc visible pour la transaction A. Donc N1=Hérisson, N2=Réinvention, N3=Réinvention.
La lecture répétable est le niveau d'isolation des transactions par défaut de MySQL . Sous le niveau d'isolement de lecture répétable, si vous interrogez le même enregistrement plusieurs fois dans une transaction, les résultats seront toujours cohérents .
Dans le scénario ci-dessus, si le niveau d'isolement de la base de données est en lecture répétable, puisque les requêtes N1 et N2 sont dans une transaction, leurs valeurssont toutes deux des "hérissons", et N3 est exécuté après la transaction A est soumis. Pour la requête effectuée, les modifications de la transaction B sont visibles, donc N3 = remodeler.
Sous le niveau d'isolement sérialisable, les transactions sont exécutées en série. Des verrous de lecture seront ajoutés pour la lecture et des verrous de lecture seront ajoutés pour l'écriture, les transactions ne seront pas exécutées simultanément, donc aucune exception ne se produira.
Dans le scénario ci-dessus, si le niveau d'isolement de la base de données est sérialisable, la transaction A est ouverte en premier et est bloquée à l'ouverture de la transaction B. La transaction B ne sera pas ouverte tant que la transaction A n'est pas soumise, donc N1. = Hérisson , N2=hérisson. La requête en N3 sera exécutée après la soumission de la transaction B (la transaction B est bloquée en premier et l'ordre d'exécution est avant l'instruction de requête N3), donc N3 = remodeler. 4. Problèmes causés par les niveaux d'isolement Dans différents niveaux d'isolement des transactions, si les transactions sont exécutées simultanément, de nombreux problèmes se produiront, tels qu'une lecture sale et une lecture non répétable (lecture non répétable ), Phantom Read (Phantom Read), etc. Différents exemples seront utilisés pour expliquer ces problèmes en détail ci-dessous. 4.1 Dirty ReadDirty Read (Dirty Read) signifie qu'une transaction peut lire des données modifiées par une autre transaction non validée.
Regardez le cas suivant, en supposant que le niveau d'isolement est lu non validé :Sous le niveau d'isolement de lecture non validée, la valeur de N1 est "reshape", et en raison de l'annulation de la transaction B, la valeur de N2 est "hérisson". Une lecture sale s'est produite ici à N1. De toute évidence, le résultat de la requête à N1 est constitué de données sales, ce qui aura un impact sur les activités normales.
Des lectures sales se produiront dans le niveau d'isolement de lecture non validé.
Lecture non répétable (Lecture non répétable) signifie que l'exécution de la même requête deux fois peut entraîner des résultats différents.
Continuez à utiliser le cas de transaction AB lors de l'introduction du niveau d'isolement et supposez que le niveau d'isolement est en lecture validée :
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | begin; | |
| 2 | begin; | |
| 3 | update user set name='重塑' where id=1; | |
| 4 | select name from user where id=1;(N1) | |
| 5 | commit; | |
| 6 | select name from user where id=1;(N2) | |
| 7 | commit; | |
| 8 | select name from user where id=1;(N3) |
Sous le niveau d'isolement en lecture validée, les transactions peuvent lire d'autres transactions Données soumises. Dans le cas ci-dessus, les résultats sont N1=hedgehog, N2=reshape, N3=reshape. Dans la transaction A, il y a deux requêtes identiques N1 et N2, mais les résultats de ces deux requêtes ne sont pas les mêmes. à plusieurs reprises.
Des lectures non répétables se produiront dans les niveaux d'isolement de lecture non validée et de lecture validée.
Lecture fantôme (Lecture fantôme) signifie que lorsqu'une transaction lit des enregistrements dans une certaine plage, une autre transaction insère un nouvel enregistrement dans cette plage Lorsque la transaction précédente lit à nouveau les enregistrements de cette plage, ce nouvel enregistrement sera lu.
Regardez le cas suivant, en supposant que le niveau d'isolement est reproductible :
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | begin; | |
| 2 | select name from user;(N1) | |
| 3 | begin; | |
| 4 | insert into user values(2, '五条人'); | |
| 5 | commit; | |
| 6 | select name from user;(N2) | |
| 7 | select name from user for update;(N3) | |
| 8 | commit; |
La transaction A comporte trois requêtes Entre N1 et N2, la transaction B a exécuté une instruction d'insertion et l'a soumise. La requête en N3 a utilisé for update.
Le résultat en N1 n'est évidemment qu'un "hérisson". Le résultat en N2 est également un "hérisson" car la transaction A est ouverte avant la transaction B. Le résultat en N3 est théoriquement au niveau d'isolation de lecture répétable. Il ne devrait y avoir que "hérisson" dans N2, mais en fait le résultat de N2 est "hérisson" et "gojo man", ce qui provoque une lecture fantôme.
C'est très étrange. Cela ne signifie-t-il pas que le niveau d'isolement de lecture répétable peut garantir que le même enregistrement est interrogé plusieurs fois dans une transaction et que les résultats sont toujours cohérents ? Ce résultat ne répond pas à la définition de lecture répétable.
En fait, sous le niveau d'isolement de lecture répétable, si vous utilisez lecture actuelle, une lecture fantôme peut se produire.
La lecture actuelle et la lecture instantanée seront discutées plus tard lorsque les principes de mise en œuvre des transactions et du MVCC seront introduits. Voici d'abord une conclusion.
Les lectures fantômes se produiront dans les niveaux d'isolement de lecture non validée, de lecture validée et de lecture répétable.
Ce qui nécessite une attention particulière ici est : la lecture fantôme et la lecture non répétable signifient toutes deux que les résultats de la même instruction de requête dans une transaction sont différents, mais la lecture fantôme la lecture est plus Elle se concentre sur l'interrogation des données nouvellement insérées par d'autres transactions (insertion) ou des données supprimées par d'autres transactions (supprimer), tandis que la lecture non répétable a une portée plus large Tant que les résultats sont différents, elle peut être considérée comme non. -lecture répétable, mais généralement on pense à la lecture non répétable. Concentrez-vous davantage sur la mise à jour des données par d'autres transactions.
Grâce à la description ci-dessus, nous connaissons déjà les concepts de quatre niveaux d'isolement et les problèmes qu'ils rencontreront respectivement. Plus le niveau d'isolement d'une transaction est élevé, plus l'isolement est fort. Il y aura moins de problèmes rencontrés. Mais en même temps, plus le niveau d’isolement est élevé, plus la capacité de concurrence est faible.
Le tableau suivant est un résumé des concepts de niveaux d'isolement et des problèmes pouvant survenir à différents niveaux d'isolement :
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 概念 |
|---|---|---|---|---|
| 读已提交 | √ | √ | √ | 事务中的修改，即便没有提交，对其他事务也都是可见的 |
| 读未提交 | √ | √ | 事务中的修改只有在提交之后，才会对其他事务可见 | |
| 可重复读 | √ | 一个事务中多次查询相同的记录，结果总是一致的 | ||
| 可串行化 | 事务都是串行执行的，读会加读锁，写会加写锁 |
MVCC（Multi-Version Concurrency Control）即多版本并发控制，这是 MySQL 为了提高数据库并发性能而实现的。它可以在并发读写数据库时，保证不同事务的读-写操作并发执行，同时也能解决脏读、不可重复读、幻读等事务隔离问题。
在前文讨论幻读的时候提到过当前读的概念，正是由于当前读，才会在可重复读的隔离级别下也会发生幻读的情况。
在解释可重复读隔离级别下发生幻读的原因之前，首先介绍 MVCC 的实现原理。
首先我们需要知道，InnoDB 的数据页中每一行的数据是有隐藏字段的：
DB_ROW_ID: 隐式主键，若表结构中未定义主键，InnoDB 会自动生成该字段作为表的主键DB_TRX_ID: 事务ID，代表修改此行记录的最后一次事务IDDB_ROLL_PTR: 回滚指针，指向此行记录的上一个版本（上一个事务ID对应的记录）每一条修改语句都会相应地记录一条回滚语句（undo log），如果把每一条回滚语句视为一条数据表中的记录，那么通过事务ID和回滚指针就可以将对同一行的修改记录看作一个链表，链表上的每一个节点就是一个快照版本，这就是 MVCC 中多版本的意思。
举个例子，假设对 user 表中唯一的一行「刺猬」进行多次修改。
update user set name='重塑' where id=1;update user set name='木马' where id=1;update user set name='达达' where id=1;复制代码
那么这条记录的Ma compréhension de MySQL Partie 4 : Transactions, niveaux disolement et MVCC就是：
在这个Ma compréhension de MySQL Partie 4 : Transactions, niveaux disolement et MVCC中，头结点就是当前记录的最新版本。DB_TRX_ID 事务ID 字段是非常重要的属性，先 Mark 一下。
除此之外，在读已提交（RC，Read Committed）和可重复读（RR，Repeatable Read）的隔离级别中，事务在启动的时候会创建一个读视图（Read View），用它来记录当前系统的活跃事务信息，通过读视图来进行本事务之间的可见性判断。
在读视图中有两个重要的属性：
需要注意下一个事务I的值，并不是事务ID列表中的最大值+1，而是当前系统中已存在过的事务的最大值+1。例如当前数据库中活跃的事务有(1,2)，此时事务2提交，同时又开启了新事务，在生成的读视图中，下一个事务ID的值为3。
我们通过将Ma compréhension de MySQL Partie 4 : Transactions, niveaux disolement et MVCC与读视图两者结合起来，来进行并发事务间可见性的判断，判断规则如下（假设现在要判断事务A是否可以访问到事务B的修改记录）：
当前事务ID小于事务A的最小事务ID的值，代表事务B是在事务A生成读视图之前就已经提交了的，所以事务B对于事务A来说是可见的。当前事务ID大于或等于事务A下一个事务ID的值，代表事务B是在事务A生成读视图之后才开启，所以事务B对于事务A来说是不可见的。当前事务ID在事务A的最小事务ID和下一个事务ID之间（左闭右开，[最小事务ID, 下一个事务ID)），需要分两种情况讨论：当前事务ID在事务A的事务ID列表中，代表创建事务A时事务B还是活跃的，未提交，所以事务B对于事务A来说是不可见的。当前事务ID不在事务A的事务ID列表中，代表创建事务A时事务B已经提交，所以事务B对于事务A来说是可见的。如果事务B对于事务A来说是不可见的，就需要顺着修改记录的Ma compréhension de MySQL Partie 4 : Transactions, niveaux disolement et MVCC，从回滚指针开始往前遍历，直到找到第一个对于事务A来说是可见的事务ID，或者遍历完Ma compréhension de MySQL Partie 4 : Transactions, niveaux disolement et MVCC也未找到（表示这条记录对事务A不可见）。
这就是 MVCC 的实现原理。
Ce qui doit être noté ici est le moment de création de la vue de lecture Dans la discussion ci-dessus, nous savons déjà qu'une transaction se fera. créez un au démarrage. Read View (Read View) , et il existe deux façons d'ouvrir une transaction, l'une est begin/start transaction, l'autre est start transaction with consistent snapshot. , le moment de création d'une vue en lecture est également différent :
begin/start transaction, la vue en lecture sera créée lors de l'exécution de la première instruction de lecture instantanée start transaction with consistent snapshot, la vue de lecture sera créée en même temps. VueAfin d'expliquer le En cours d'exécution de MVCC en détail, voici un exemple, en supposant qu'il y a actuellement deux transactions (le niveau d'isolation des transactions est la lecture répétée par défaut de MySQL) :
Ce qui doit être noté ici, c'est le démarrage timing de la transaction Dans la discussion ci-dessus, nous savons déjà que la transaction créera une vue de lecture lorsqu'elle démarrera ( Read View) , et il existe deux façons de démarrer une transaction. , l'un est
begin/start transaction, l'autre eststart transaction with consistent snapshotGrâce à ces deux manières de démarrer une transaction, le moment de création d'une vue de lecture est également différent :
- Si la transaction est ouverte dans le Mode
begin/start transaction, la vue de lecture sera créée lors de l'exécution de la première instruction de lecture instantanée- Si la transaction est ouverte en mode
start transaction with consistent snapshot, la vue de lecture sera créée en même temps
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | start transaction with consistent snapshot; | |
| 2 | start transaction with consistent snapshot; | |
| 3 | update user set name='重塑' where id=1; | |
| 4 | select name from user where id=1;(N1) | |
| 5 | commit; | |
| 6 | select name from user where id=1;(N2) | |
| 7 | commit; |
Analysez ensuite le processus en cours d'exécution de MVCC en fonction de la chaîne de versions décrite ci-dessus et de la vue de lecture lorsque les deux transactions sont démarrées.
L'image ci-dessus est la vue de lecture lorsque deux transactions sont démarrées. Lorsque l'instruction de mise à jour de la transaction B est exécutée, la chaîne de versions de la ligne id=1 est la suivante.
Examinons d'abord l'instruction de requête en N1. Le 事务ID=2 actuel de la transaction B est égal au 下一个事务ID de la transaction A, comme indiqué ci-dessus. Selon le jugement de visibilité, la transaction B est invisible pour la transaction A et doit être récupérée en ligne le long de la chaîne de versions de la ligne actuelle.
Ensuite, nous avons suivi la chaîne de versions jusqu'à la version historique de DB_TRX_ID=1 transaction ID=1, qui s'est avérée être égale à la valeur de l'ID de transaction de la transaction A, c'est-à-dire la version de la ligne lorsque la transaction A a été démarré. Cette version est pour la transaction A. Bien sûr, elle est visible, donc le nom='hedgehog' dans la ligne id=1 est lu, c'est-à-dire le N1=hedgehog final.
Regardons l'instruction de requête en N2. À ce stade, la transaction B a été soumise et la chaîne de versions est toujours celle indiquée dans la figure ci-dessus puisque l'ID de transaction de la version actuelle est égal à <.> dans la vue lecture de la transaction A, la version actuelle de L'enregistrement n'est pas visible par la transaction A, donc encore une fois N2 = Hérisson. 下一个事务ID
Ce qui doit être noté ici, c'est que si l'instruction de temps 4 de la transaction A dans l'exemple est modifiée en une instruction de mise à jour pour la ligne, alors la transaction A attendra que la transaction B soit validée avant de s'exécuter. l'instruction de mise à jour. En effet, la transaction B n'a pas été validée, c'est-à-dire que le verrou en écriture de la transaction B sur la ligne id = 1 n'a pas été libéré et que la transaction A doit également mettre à jour la ligne, qui doit mettre à jour la dernière version (lecture actuelle). , la transaction A est donc bloquée, vous devez attendre que le verrou en écriture de la transaction B sur la ligne soit libéré avant de continuer à exécuter l'instruction de mise à jour.5.4 Comparaison du moment de génération des vues de lecture entre RC et RRLe processus d'exécution de MVCC décrit ci-dessus est entièrement destiné au niveau d'isolement de lecture répétable (RR, lecture répétable) If. c'est Qu'en est-il du niveau Lecture Commise (RC, Lecture Commise) ? La situation concernant les lectures non répétables dans le niveau d'isolement de lecture validée a été discutée ci-dessus. Nous ne donnerons pas d'exemples ici et donnerons simplement la conclusion directement.
DB_TRX_ID=2
Les requêtes sur N1 et N2 ont dû être très claires : ce sont tous des "hérissons". L'instruction de requête utilisée en N3 est for update. Son utilisation pour interroger ajoutera un "verrouillage au niveau de la ligne" à l'enregistrement cible. La signification du verrouillage au niveau de la ligne sera discutée plus tard. 🎜> peut verrouiller la cible. Enregistrez-la simplement. for update
pour interroger, for update sera utilisé pour lire la dernière version de l'enregistrement cible, de sorte que l'instruction de requête en N3 inclura également les enregistrements de la transaction B qui ne sont pas visibles par la transaction A. la requête sort, une lecture fantôme se produit. 当前读
Plus de liens gratuits recommandations d'apprentissage : tutoriel mysql(vidéo)
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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
