Parlons du niveau d'isolement atteint par les transactions MySQL et MVCC

Cet article vous pose quelques questions sur le niveau d'isolation des transactions MySQL et comment MVCC l'implémente. J'espère qu'il vous sera utile.

Introduction aux transactions de base de données

Quatre caractéristiques majeures des transactions (ACID)

1. Atomicité (atomicité) : La plus petite unité de travail d'une transaction, soit tout succès, soit tout échec.

2. Cohérence : Après le début et la fin de la transaction, l'intégrité de la base de données ne sera pas détruite.

3. Isolement : Différentes transactions ne s'affectent pas les unes les autres. Les quatre niveaux d'isolement sont RU (Lecture non validée), RC (Lecture validée), RR (Lecture répétable), SERIALIZABLE (Changement en série).

4. Durabilité : Une fois la transaction soumise, la modification des données est permanente et ne sera pas perdue même en cas de panne du système.

Niveau d'isolement des transactions

Lecture non validée/RU

Également connue sous le nom de lecture sale, une transaction peut lire les données non validées d'une autre transaction. Ce niveau d'isolement est le moins sécurisé car les transactions non validées sont sujettes à une restauration.

Lecture validée/RC

Également connue sous le nom de Lecture non répétable, une transaction lit les données modifiées qui ont été soumises par une autre transaction, ce qui entraîne la lecture du même élément à des moments différents dans la transaction en cours. Résultats de l'acquisition de données sont incohérents.

Par exemple, dans l'exemple suivant, vous constaterez que SessionA interroge deux fois des données différentes au cours d'une transaction. La raison en est que le niveau d'isolement actuel est RC et que la transaction de SessionA peut lire les dernières données soumises par SessionB.

Heure d'occurrence	SessionA	SessionB
1	begin;
2	sélectionnez * de l'utilisateur où id=1 ;( Zhang San)
3		mettre à jour le nom de l'ensemble d'utilisateurs = '李思' où id=1 (validation de transaction implicite par défaut)
4	sélectionner * de l'utilisateur où id = 1;(李思)
5		mettre à jour le nom de l'ensemble d'utilisateurs='王二' où id=1;(transaction de validation implicite par défaut)
6	select * from user où id=1;(王二)

Lecture répétable/RR)

également connue sous le nom delecture fantôme, une lecture de transaction peut lire d'autres validations de transaction. Cependant, sous le niveau d'isolement RR, ces données ne peuvent être lues qu'une seule fois.Dans la transaction en cours, quel que soit le nombre de lectures, les données sont toujours la valeur lue pour la première fois.Après une lecture, d'autres transactions modifient et valident les données pour produire des modifications. Par conséquent, cela devient également une lecture fantôme, car les données lues ne sont pas nécessairement les données les plus récentes.

Par exemple : lorsque les données sont lues pour la première fois dans SessionA, les transactions ultérieures qui modifient les données soumises n'affecteront pas la valeur des données lues par SessionA. C'est reproductible.

Heure d'occurrence	SessionA	SessionB
1	begin;
2	sélectionnez * de l'utilisateur où id=1 ;( Zhang San)
3		mettre à jour le nom de l'ensemble d'utilisateurs='李思' où id=1 (soumission de transaction implicite par défaut)
4	sélectionnez * de l'utilisateur où id = 1;(Zhang San)
5		mettre à jour le nom de l'ensemble d'utilisateurs = '王二' où id=1;(Validation de transaction implicite par défaut)
6	sélectionner * de l'utilisateur où id=1;(Zhang San)

Sérialisable

Toutes les opérations de lecture ou d'écriture de la base de données sont exécutées en série, et une seule est prise en charge sous le niveau d'isolement actuel. Les requêtes sont exécutées simultanément, et toutes les opérations nécessitent une exécution en file d'attente. Par conséquent, toutes les données sous ce niveau d’isolement sont les plus stables, mais les performances sont également les pires. L'implémentation du verrouillage de la base de données est une version à plus petite granularité de ce niveau d'isolement.

Heure d'occurrence	SessionA	SessionB
1	begin;
2		commencer;
3		mettre à jour le nom de l'utilisateur ='李思' où id=1;
4	sélectionnez * de l'utilisateur où id=1;(attendez, attendez)
5		commit;
6	sélectionnez * de l'utilisateur où id=1;(李思)

Transaction et principe MVCC

Problèmes causés par différentes transactions exploitant les mêmes données en même temps

Exemple :

Vérifier le solde = 1000 yuans 5Le montant du dépôt est de 100 yuans et le solde modifié est de 1100 yuans68Soumettre la transaction (Solde = 1100) 9Heure d'occurrenceSessionA1

Heure d'occurrence	SessionA	SessionB
1	begin "
	Retirez 100 yuans en espèces, et le solde modifié est de 900 yuans en ce moment
	Soumettre la transaction (Solde = 900)
		SessionB
		début ;

2Vérifier le solde = 1000 yuans 4Vérifier le solde = 1000 yuans5Le montant du dépôt est de 100 yuans, et le le solde est de 1 100 yuans6Retirez 100 yuans en espèces, et le solde modifié est de 900 yuans à ce moment-là89 Annuler la transaction ( solde rétabli à 1000 yuans)

Les deux situations ci-dessus sont des problèmes qui peuvent survenir lorsque plusieurs transactions opèrent sur une donnée en même temps. L'opération d'une certaine transaction peut être écrasée, entraînant une perte de données.

LBCC résout la perte de données

LBCC, Lock Based Concurrency Control.

En utilisant le mécanisme de verrouillage, lorsque la transaction en cours doit modifier les données, la transaction en cours est verrouillée. Une seule transaction est autorisée à modifier les données en cours en même temps, et les autres transactions doivent attendre que le verrou soit libéré. avant de pouvoir fonctionner.

MVCC résout la perte de données

MVCC, contrôle de concurrence multi-version, contrôle de concurrence multi-version.

Utilisez la version pour contrôler les problèmes de données en cas de concurrence. Lorsque la transaction B commence à modifier le compte et que la transaction n'est pas soumise, lorsque la transaction A doit lire le solde du compte, le solde du compte avant l'opération de modification de la transaction B sera lu. . Copiez les données, mais si la transaction A doit modifier les données du solde du compte, elle doit attendre que la transaction B valide la transaction.

MVCC effectue la lecture de la base de données sans verrouiller les données, et les requêtes SELECT ordinaires ne seront pas verrouillées, ce qui améliore la capacité de traitement simultané de la base de données. Avec l'aide de MVCC, la base de données peut implémenter des niveaux d'isolement tels que READ COMMITTED et REPEATABLE READ. Les utilisateurs peuvent afficher les versions historiques précédentes ou précédentes des données actuelles, garantissant ainsi la fonctionnalité I (isolation) dans ACID.

Logique d'implémentation MVCC d'InnoDB

Les données MVCC enregistrées par le moteur de stockage InnoDB

Le MVCC d'InnoDB est implémenté en enregistrant deux colonnes cachées derrière chaque ligne d'enregistrements. Un ID de transaction (DB_TRX_ID) qui enregistre la ligne et un pointeur de restauration (DB_ROLL_PT) qui enregistre la ligne. Chaque fois qu'une nouvelle transaction est démarrée, un nouvel identifiant de transaction sera automatiquement incrémenté. Au début de la transaction, l'ID de transaction sera placé dans la ligne ID de transaction affectée par la transaction en cours. Lors de l'interrogation, l'ID de transaction actuelle doit être comparé à l'ID de transaction enregistré dans chaque ligne.

Jetons un coup d'œil à la façon dont MVCC fonctionne sous le niveau d'isolement REPEATABLE READ.

SELECT

InnoDB vérifiera chaque enregistrement de ligne selon les deux conditions suivantes :

1. InnoDB recherche uniquement les lignes de données dont la version est antérieure à la version actuelle de la transaction (c'est-à-dire que le numéro de transaction de la ligne est inférieur ou égal au numéro de version actuel de la transaction), cela garantit que les lignes lues par la transaction soit existent déjà avant le démarrage de la transaction, soit ont été insérées ou modifiées par la transaction elle-même.

2. Les lignes supprimées doivent être jugées par l'ID de transaction et la version de l'état avant la lecture de la transaction. Seuls les enregistrements qui remplissent les deux conditions ci-dessus peuvent être renvoyés en tant que résultats de requête.

INSERT

InnoDB enregistre le numéro de transaction actuel en tant que numéro de version de ligne pour chaque ligne nouvellement insérée.

DELETE

InnoDB enregistre le numéro de transaction actuel en tant qu'identification de suppression de ligne pour chaque ligne supprimée.

UPDATE

InnoDB insère une nouvelle ligne d'enregistrements, enregistre le numéro de transaction actuel comme numéro de version de la ligne et enregistre le numéro de transaction actuel dans la ligne d'origine comme identifiant de suppression de ligne.

Enregistrez ces deux numéros de transaction supplémentaires afin que la plupart des opérations de lecture puissent être effectuées sans verrouillage. Cette conception rend l'opération de lecture des données très simple, les performances sont très bonnes et garantit également que seules les lignes répondant aux normes sont lues. Les inconvénients sont que chaque ligne d'enregistrements nécessite un espace de stockage supplémentaire, davantage de vérifications de lignes et des travaux de maintenance supplémentaires.

MVCC ne fonctionne que sous deux niveaux d'isolement : REPEATABLE READ et READ COMMITIED. Les deux autres niveaux d'isolement sont incompatibles avec MVCC car READ UNCOMMITIED lit toujours la dernière ligne de données, et non la ligne de données conforme à la version actuelle de la transaction. SERIALIZABLE verrouillera toutes les lignes lues.

L'implémentation de MVCC dans MySQL repose sur l'annulation du journal et la vue en lecture.

UNDO LOGAGE

JUSSE CONTRACTION DES MODÈRES DES DÉCHETS, DIDAGE LOR journal des opérations, car l'enregistrement de l'opération d'insertion concerne uniquement la transaction en cours elle-même et n'est pas visible par les autres transactions, de sorte que le journal d'annulation d'insertion peut être supprimé directement après la soumission de la transaction sans avoir besoin d'une opération de purge. La tâche principale de la purge est de supprimer les données qui ont été marquées comme supprimées dans la base de données. De plus, elle recyclera également les pages d'annulation par lots.

L'état initial des données lors de l'insertion de la base de données :

• .

  journal d'annulation de mise à jour :

mise à jour ou journal d'annulation généré lors de l'opération de suppression. Parce qu'il affectera les enregistrements existants, afin de fournir le mécanisme MVCC, le journal d'annulation de mise à jour ne peut pas être supprimé lorsque la transaction est soumise. Au lieu de cela, il est placé dans la liste de l'historique lorsque la transaction est soumise, en attendant que le thread de purge soit exécuté. l'opération de suppression finale.

Lorsque les données sont modifiées pour la première fois :

• Lorsqu'une autre transaction modifie les données actuelles pour la deuxième fois :

  Afin de garantir que les transactions n'entrent pas en conflit lors de l'écriture de leurs journaux d'annulation respectifs pendant opérations simultanées, InnoDB Utiliser des segments d'annulation pour maintenir l'écriture simultanée et la persistance des journaux d'annulation. Le segment de restauration est en fait un moyen d'organiser les fichiers d'annulation.

  ReadView

  Pour le niveau d'isolement RU(READ UNCOMMITTED), toutes les transactions peuvent lire directement la dernière valeur de la base de données, et pour le niveau d'isolement SERIALIZABLE, toutes les requêtes seront verrouillées et exécutées de manière synchrone. Donc dans ces deux cas, il n'est pas nécessaire d'utiliser le contrôle de version de Read View.

  Pour RC(READ COMMITTED) et RR(REPEATABLE READ), la mise en œuvre du niveau d'isolement est complétée via le contrôle de version ci-dessus. La logique de traitement de base sous les deux secteurs d'isolation consiste à déterminer quelle version parmi toutes les versions est visible pour la transaction en cours. En réponse à ce problème, InnoDB a ajouté une conception ReadView à la conception. ReadView contient principalement les autres transactions de lecture et d'écriture actives dans le système actuel et place leurs identifiants de transaction dans une liste. Nous avons nommé cette liste pour . m_ids.

  La logique de jugement permettant de savoir si les données de la chaîne de versions sont visibles lors de la requête :

  • Si la valeur de l'attribut trx_id de la version consultée est inférieure au plus petit identifiant de transaction dans la liste m_ids, cela signifie que la transaction qui a généré cette version a été soumis avant la génération du ReadView, cette version est donc accessible par la transaction en cours.

  • Si la valeur de l'attribut trx_id de la version consultée est supérieure au plus grand identifiant de transaction dans la liste m_ids, cela indique que la transaction qui a généré cette version a été générée après la génération du ReadView, cette version n'est donc pas accessible par la transaction en cours.

  • Si la valeur de l'attribut trx_id de la version consultée se situe entre le plus grand identifiant de transaction et le plus petit identifiant de transaction dans la liste m_ids, alors vous devez déterminer si la valeur de l'attribut trx_id est dans la liste m_ids. signifie qu'il a été généré lors de la création du ReadView. La transaction de cette version est toujours active, et cette version n'est pas accessible sinon, cela signifie que la transaction qui a généré cette version lors de la création du ReadView a été validée, et cette version peut être accédé.

  Par exemple :

  ReadView sous le niveau d'isolement READ COMMITTED

  Générer un ReadView (liste m_ids) à chaque fois avant de lire les données

	commencer;	3
		Soumettre la transaction (solde = 1100)

T6commit;T7MISE À JOUR du nom de l'utilisateur SET = 'Dybala ' OÙ id = 1;T10commit;T11

Voici une analyse de ReadView dans la situation ci-dessus

L'instruction SELECT au point temporel T5 :

La chaîne de versions au point temporel actuel :

L'instruction SELECT est exécutée à ce moment, et la chaîne de versions de les données actuelles sont comme ci-dessus, car la transaction actuelle 777 et la transaction 888 n'ont pas été soumises, donc la liste ReadView des transactions actives à ce moment m_ids : [777, 888] , donc l'instruction de requête sera basée sur la plus grande donnée de version dans la chaîne de versions actuelle qui est inférieure à m_ids C'est-à-dire que la requête est Mbappe.

L'instruction SELECT au moment T8 :

La situation de la chaîne de versions à l'heure actuelle :

L'instruction SELECT est exécutée à ce moment, et la chaîne de versions des données actuelles est comme ci-dessus, car la transaction en cours 777 a été soumise et la transaction 888 n'a pas été soumise, donc la liste de ReadView des transactions actives à ce moment m_ids : [888], donc l'instruction de requête sera basée sur les données de version les plus importantes de la chaîne de versions actuelle. c'est moins que m_ids , c'est-à-dire que Messi est interrogé.

Instruction SELECT au point temporel T11 :

Informations sur la chaîne de versions au point temporel actuel :

À ce moment, l'instruction SELECT est exécutée et la chaîne de versions des données actuelles est comme ci-dessus, car la transaction en cours 777 et la transaction 888 ont été soumises. Par conséquent, la liste de ReadView pour les transactions actives à ce moment est vide, donc l'instruction de requête interrogera directement les dernières données de la base de données actuelle, c'est-à-dire que Dybala est interrogée.

Résumé : Une transaction utilisant le niveau d'isolement READ COMMITTED générera un ReadView indépendant au début de chaque requête.

ReadView sous le niveau d'isolement REPEATABLE READ

Génère un ReadView (liste m_ids) lorsque les données sont lues pour la première fois après le début de la transaction

Time	Trans action 777	Transaction 888	Trasaction 999
T1	begin;
T2		commencer;	commencer;
T3	METTRE À JOUR le nom de l'utilisateur SET = 'CR7' OÙ id = 1; DATE nom de l'utilisateur SET = 'Messi ' OÙ id = 1;		SELECT * FROM utilisateur où id = 1;
		MISE À JOUR du nom de l'utilisateur SET = 'Neymar' OÙ id = 1;
T8			SELECT * FROM utilisateur où id = 1;
T9
			SELECT * FROM utilisateur où id = 1;

T6commit;T7 ECT * FROM utilisateur où id = 1;Instruction SELECT de point temporel dans le cas de T5 : Chaîne de version actuelle : Une ReadView est générée lorsque l'instruction select est actuellement exécutée. À ce moment, le contenu de Instruction SELECT au moment T8 : Chaîne de la version actuelle : , donc les données de requête à ce moment sont toujours Mbappe. Chaîne de la version actuelle : m_ids à T5 sont toujours utilisés à ce moment : [777,999]Résumé MVCC : Le soi-disant MVCC (Multi-Version Concurrency Control, contrôle de concurrence multi-version) fait référence à l'utilisation des deux niveaux d'isolement de READ COMMITTD et pour exécuter des transactions ordinaires. L'opération SEELCT est le processus d'accès à la chaîne de versions enregistrées, qui permet Apprentissage recommandé :

Time	Transaction 777	Trans action 888	Trasaction 999
T1	begin;
T2		commencer;	commencer;
T3	METTRE À JOUR le nom de l'utilisateur SET = 'CR7' OÙ id = 1; Nom de l'utilisateur ATE = 'Messi' OÙ id = 1;		SELECT * FROM utilisateur où id = 1;
		UPDATE nom de l'utilisateur SET = ' Neymar' OÙ id = 1;
T8			SELECT * FROM utilisateur où id = 1;
T9		UP DATE nom de l'utilisateur SET = 'Dybala ' OÙ identifiant = 1;
		m_ids est : [777,888], donc le. les données interrogées selon la version visible de ReadView sont Mbappe.
	Dans la transaction en cours 999 à ce moment. Étant donné que ReadView a été généré au moment T5, ReadView ne sera généré qu'une seule fois dans la transaction en cours, donc les m_ids à T5 sont toujours utilisés à ce moment : [777,999]		Instruction SELECT au moment T11 :
La situation à ce moment est exactement la même que celle de T8. Étant donné que ReadView a été généré au moment T5, ReadView ne sera généré qu'une seule fois dans la transaction en cours, donc les	, donc les données de requête à ce moment sont toujours Mbappe.
REPEATABLE READ	aux opérations de différentes transactions d'être exécutées simultanément, améliorant ainsi les performances du système.	Tutoriel vidéo mysql

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!