Parlons de l'isolation des transactions dans MySQL

Cet article vous amènera à comprendre l'isolation des transactions dans MySQL, à présenter les caractéristiques des transactions, les niveaux d'isolement, les méthodes de démarrage des transactions, etc. J'espère qu'il sera utile à tout le monde !

Une transaction consiste à garantir qu'un ensemble d'opérations de base de données réussissent toutes ou échouent toutes. Dans MySQL, la prise en charge des transactions est implémentée au niveau du moteur, mais tous les moteurs ne prennent pas en charge les transactions. Par exemple, le moteur natif MyISAM de MySQL ne prend pas en charge les transactions. [Recommandations associées : Tutoriel mysql (vidéo)]

1. Caractéristiques des transactions

• Atomicité : Toutes les opérations d'une transaction sont soit complètement terminées, soit non terminées, et ne se termineront pas quelque part dans le lien du milieu. Si une erreur se produit lors de l'exécution de la transaction, elle sera restaurée à l'état avant le début de la transaction, comme si la transaction n'avait jamais été exécutée. Cohérence : avant le début de la transaction et après la fin de la transaction, l'intégrité de la base de données. n'a pas été détruit.
Isolement : la base de données permet à plusieurs transactions simultanées de lire, d'écrire et de modifier des données en même temps.
• Persistance : une fois la transaction terminée. terminées, les modifications des données sont permanentes et ne seront pas perdues même en cas de panne du système. 2. Niveau d'isolement 1. Lorsque plusieurs transactions sont exécutées simultanément sur la base de données, des lectures sales, des lectures non répétables et des lectures fantômes peuvent survenir.
• Lecture sale : la transaction B lit les données non validées de la transaction A

Lecture non répétable : une transaction lit les données de mise à jour soumises dans une autre transaction

Lecture fantôme/lecture virtuelle : une transaction lit les données d'insertion soumises dans une autre transaction

2. Le niveau d'isolement de la transaction comprend : lecture non validée, lecture validée, lecture répétable et sérialisée

• Lecture non validée : lorsqu'une transaction n'a pas encore été soumise, les modifications qu'elle apporte peuvent être vues par d'autres transactions
• Lire la validation : Une fois qu'une transaction est soumise, les modifications qu'elle apporte seront vues par les autres transactions (résoudre les lectures sales, le niveau d'isolement par défaut d'Oracle)
• Lecture répétable : Les données vues lors de l'exécution d'une transaction sont toujours cohérentes avec les données vues lorsque le la transaction est démarrée et les modifications non validées sont également invisibles pour les autres transactions (pour résoudre les lectures sales et les lectures non répétables, niveau d'isolement par défaut de MySQL)

Sérialisation : pour la même ligne d'enregistrements, l'écriture ajoutera un verrou en écriture et la lecture ajoutera un verrou en écriture. ajoutez un verrou de lecture. Lorsqu'un conflit de verrouillage en lecture-écriture se produit, la transaction accédée ultérieurement doit attendre la fin de la transaction précédente avant de pouvoir continuer à s'exécuter (Solution aux lectures sales, aux lectures non répétables et aux lectures fantômes)

. La sécurité est soumise dans l'ordre et les performances sont réduites dans l'ordre
• 3. Supposons qu'il n'y ait qu'une seule colonne dans le tableau de données T et que la valeur d'une ligne est 1

• create table T(c int) engine=InnoDB;
  insert into T(c) values(1);

Ce qui suit est exécuté dans l'ordre chronologique Le comportement de les deux transactions :

Si le niveau d'isolement est lu non validé, alors V1 est 2. À l'heure actuelle, bien que la transaction B n'ait pas encore été soumise, le résultat a été vu par A. V2 et V3 sont tous deux 2

Si le niveau d'isolement est en lecture-validation, alors V1 est 1 et V2 est 2. Les mises à jour de la transaction B ne peuvent être vues par A qu'après leur validation. V3 est également 2

Si le niveau d'isolement est une lecture répétable, alors V1 et V2 sont 1 et V3 est 2. La raison pour laquelle V2 est 1 s'ensuit que les données vues avant et après l'exécution de la transaction doivent être cohérentes

Si le niveau d'isolement est la sérialisation, les valeurs de V1 et V2 sont 1 et V3 est 2

En termes de mise en œuvre, la base de données Une vue sera créée à l'intérieur, et le résultat logique de la vue prévaudra lors de l'accès. Sous le niveau d'isolement de lecture répétable, cette vue est créée au démarrage de la transaction et est utilisée tout au long de la transaction. Sous le niveau d'isolement lecture-validation, cette vue est créée au début de chaque instruction SQL. Sous le niveau d'isolement lu non validé, la dernière valeur de l'enregistrement est directement renvoyée, sans concept de vue ; tandis que sous le niveau d'isolement sérialisé, le verrouillage est directement utilisé pour éviter l'accès parallèle
• 3. Implémentation de l'isolement des transactions (répétable lire comme exemple)
Dans MySQL, chaque enregistrement enregistrera également une opération de restauration lors de sa mise à jour. La dernière valeur de l'enregistrement peut être obtenue en annulant la valeur de l'état précédent. Supposons qu'une valeur soit modifiée de 1 à 2, 3 et 4 dans l'ordre, il y aura un enregistrement similaire au suivant dans le journal d'annulation.

La valeur actuelle est 4, mais lors de l'interrogation de cet enregistrement, les transactions démarrées à des moments différents auront des vues de lecture différentes. Comme vous pouvez le voir sur la figure, dans les vues A, B et C, les valeurs de cet enregistrement sont respectivement 1, 2 et 4. Le même enregistrement peut avoir plusieurs versions dans le système, ce qui correspond à la multi-version. contrôle de concurrence (MVCC) de la base de données). Pour read-viewA, pour obtenir 1, vous devez effectuer toutes les opérations de restauration de la figure sur la valeur actuelle en même temps pour obtenir

Même s'il y a une autre transaction changeant de 4 à 5, cette transaction est la même que read-view A, B, les transactions correspondant à C ne seront pas en conflit

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除

四、事务启动的方式

MySQL的事务启动方式有以下几种：

• 显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
• set autocommit=0，这个命令将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且不会自动提交事务。这个事务持续存在直到主动执行commit或rollback语句，或者断开连接

建议使用set autocommit=1，通过显示语句的方式来启动事务

可以在information_schema库中的innodb_trx这个表中查询长事务，如下语句查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

五、事务隔离还是不隔离

下面是一个只有两行的表的初始化语句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A、B、C的执行流程如下，采用可重复读隔离级别

begin/start transaction命令：不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图是在执行第一个快照读语句时创建的

start transaction with consistent snapshot命令：马上启动一个事务，一致性视图是在执行这条命令时创建的

按照上图的流程执行，事务B查到的k的值是3，而事务A查到的k的值是1

1、快照在MVCC里是怎么工作的？

在可重复读隔离级别下，事务启动的时候拍了个快照。这个快照是基于整个库的，那么这个快照是如何实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请，是按申请顺序严格递增的

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id

下图是一个记录被多个事务连续更新后的状态：

语句更新生成的undo log（回滚日志）就是上图中的是哪个虚线箭头，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所以已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前在启动了但还没提交的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位就组成了当前事务的一致性视图。而数据的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的

这个视图数组把所有的row trx_id分成了几种不同的情况

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1）如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

2）如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定不可见

3）如果落在黄色部分，那就包括两种情况

• 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
• 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力

2、为什么事务A的查询语句返回的结果是k=1？

假设：

1.事务A开始时，系统里面只有一个活跃事务ID是99

2.事务A、B、C的版本号分别是100、101、102

3.三个事务开始前，(1,1)这一行数据的row trx_id是90

De cette façon, le tableau de la transaction A est [99 100], le tableau de vues de la transaction B est [99 100 101] et le tableau de vues de la transaction C est [99 100 101 102]

Comme vous pouvez le voir sur l'image ci-dessus , la première mise à jour effective Il s'agit de la transaction C et les données sont passées de (1,1) à (1,2). À l'heure actuelle, la ligne trx_id de la dernière version de ces données est 102, et la version 90 est devenue une version historique

La deuxième mise à jour effective est la transaction B, qui modifie les données de (1,2) à (1,3 ) . À l'heure actuelle, la dernière version de ces données est 101, et 102 est devenue la version historique Lorsque la transaction A est interrogée, la transaction B n'a pas encore été soumise, mais la version (1,3) qu'elle a générée est devenue la version actuelle. . Mais cette version doit être invisible pour la transaction A, sinon elle deviendra une lecture sale. Maintenant, la transaction A veut lire des données et son tableau de vues est [99,100]. La lecture des données commence à partir de la version actuelle. Par conséquent, le processus de lecture des données de la transaction Une instruction de requête est le suivant :

Lors de la découverte de (1,3), il est jugé que la ligne trx_id=101, qui est plus grande que le niveau d'eau élevé, se trouve dans la zone rouge, et est invisible

Ensuite, trouvez la version historique supérieure A, à première vue, la ligne trx_id=102, qui est plus grande que le niveau d'eau élevé, est dans la zone rouge et est invisible

• En regardant plus loin, j'ai finalement trouvé ( 1,1), dont la ligne trx_id=90, qui est plus petite que le niveau d'étiage, est dans la zone verte. On voit que
• Bien que cette ligne de données ait été modifiée au cours de la période, peu importe quand. Transaction A requêtes, le résultat de la visualisation de cette ligne de données est cohérent. Nous appelons cela une lecture cohérente
• Une version de données, pour une vue de transaction, en plus de ses propres mises à jour étant toujours visibles, il existe trois situations :

version n'est pas soumis, la version invisible

est soumise, mais elle a été soumise après la création de la vue, la version invisible

• Elle a été soumise et elle a été soumise avant la création de la vue. On peut voir que le tableau de vues du. L'instruction de requête de la transaction A a été générée lors du démarrage de la transaction A. À ce moment :
• (1,3) n'a pas encore été soumis et appartient au cas 1, invisible
• (1,2) est soumis, mais il l'est. soumis après la création du tableau de vues, qui appartient au cas 2, invisible

(1,1) est soumis avant la création du tableau de vues, visible

• 3 Pourquoi l'instruction de requête de la transaction B renvoie-t-elle k=3. ?

Lorsque la transaction B souhaite mettre à jour les données, elle ne peut plus se mettre à jour sur la version historique, sinon la mise à jour de la transaction C sera perdue. Par conséquent, l'ensemble k=k+1 de la transaction B à ce moment est une opération basée sur (1,2)

Les données mises à jour sont d'abord lues puis écrites, et cette lecture ne peut lire que la valeur actuelle appelée actuelle. lire. En plus de l'instruction update, si l'instruction select est verrouillée, c'est également la lecture en cours. Supposons que la transaction C ne soit pas soumise immédiatement, mais devienne la transaction suivante C', que se passera-t-il ?

Dans l'image ci-dessus, la transaction C n'a pas été soumise immédiatement après avoir été mise à jour. Avant sa soumission, la déclaration de mise à jour de la transaction B a été initiée en premier. Bien que la transaction C n'ait pas encore été soumise, la version (1,2) a été générée et est la dernière version. À l'heure actuelle, un protocole de verrouillage en deux étapes est impliqué, et la transaction C n'a pas été soumise, c'est-à-dire. (1,2) Le verrou en écriture sur cette version n'a pas encore été libéré. La transaction B est la lecture en cours, elle doit lire la dernière version, et elle doit être verrouillée, elle est donc verrouillée. Elle doit attendre que la transaction C libère le verrou avant de pouvoir continuer sa lecture en cours

7. Transaction disponibilité Comment la capacité de répéter la lecture est-elle obtenue ?

Le cœur de la lecture répétable est la lecture cohérente ; lorsqu'une transaction met à jour les données, seule la lecture actuelle peut être utilisée. Si le verrou de ligne de l'enregistrement actuel est occupé par d'autres transactions, vous devez entrer le verrouillage en attente.

La logique de la validation de lecture est similaire à la logique de la lecture répétable. La principale différence entre elles est la suivante :

Sous le verrou de ligne répétable. niveau d'isolement de lecture, il vous suffit de créer une vue cohérente au début de la transaction, puis les autres requêtes de la transaction partageront cette vue cohérente

Sous le niveau d'isolement de lecture-validation, une nouvelle vue sera calculée à plusieurs reprises avant chaque l'instruction est exécutée

Plus Pour des connaissances liées à la programmation, veuillez visiter :

Vidéo de programmation

 ! !

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!