Introduction au mécanisme de verrouillage de la base de données MySQL

Cet article vous présente le mécanisme de verrouillage de la base de données MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Contrôle de concurrence

• La tâche du contrôle de concurrence dans le système de gestion de base de données est de garantir que lorsque plusieurs transactions accèdent aux mêmes données dans la base de données en même temps, non Détruire l'isolement et l'unité des transactions et l'unité de la base de données.

• Le blocage, l'horodatage, le contrôle de concurrence optimiste et le contrôle de concurrence pessimiste sont les principaux moyens techniques utilisés pour le contrôle de concurrence

Verrouillage

Lorsque des transactions simultanées accèdent à une ressource en même temps, cela peut entraîner une incohérence des données. Un mécanisme est donc nécessaire pour séquencer l'accès aux données afin de garantir la cohérence des données de la base de données. Les verrous sont l'un des mécanismes (Tutoriel recommandé : Tutoriel MySQL)

Classification des verrous

• Divisé selon les opérations, il peut être divisé en Verrouillage DML, verrouillage DDL

• Divisé selon la granularité du verrouillage, il peut être divisé en verrouillage au niveau de la table, verrouillage au niveau de la ligne, niveau de la page lock (mysql)

• Selon le niveau de verrouillage, il peut être divisé en verrouillage partagé et verrouillage exclusif

• Selon la méthode de verrouillage, il peut être divisé en verrouillage automatique et verrouillage de l'affichage

• Selon la méthode d'utilisation, il peut être divisé. en verrouillage optimiste et verrouillage pessimiste

Les verrous DML sont utilisés pour protéger l'intégrité des données, y compris les verrous au niveau de la ligne (verrous TX) et les verrous au niveau de la table ( serrures TM). Les verrous DDL sont utilisés pour protéger la structure des objets de base de données, tels que les définitions structurelles de tables, d'index, etc., y compris les verrous DDL exclusifs, les verrous DDL partagés et les verrous d'analyse disruptifs

Verrous au niveau des lignes

• Le verrouillage au niveau de la ligne est le verrouillage le plus détaillé de Mysql, ce qui signifie que seule la ligne actuellement utilisée est verrouillée. Les verrous au niveau des lignes peuvent réduire considérablement les conflits dans les opérations de base de données. Sa granularité de verrouillage est la plus petite, mais la surcharge de verrouillage est également la plus importante. Les verrous au niveau des lignes sont divisés en verrous partagés et verrous exclusifs

• Caractéristiques : une surcharge élevée et des blocages lents peuvent survenir ; Le plus petit, la plus faible probabilité de conflit de verrouillage et le degré de concurrence le plus élevé

Le verrouillage au niveau de la table

• Le verrouillage au niveau de la table est le plus grande granularité de verrouillage dans Mysql Une sorte de verrou signifie verrouiller la table entière de l'opération en cours. Il est simple à mettre en œuvre, consomme moins de ressources et est pris en charge par la plupart des moteurs Mysql. Les MYISAM et INNODB les plus couramment utilisés prennent en charge le verrouillage au niveau de la table. Le verrouillage au niveau de la table est divisé en verrouillage en lecture partagé de table (verrouillage partagé) et verrouillage en écriture exclusif de table (verrouillage exclusif)

• Caractéristiques : faible surcharge, le verrouillage est rapide ; aucun blocage ne se produira ; la granularité du verrouillage est grande, la probabilité d'émettre des conflits de verrouillage est la plus élevée et la concurrence est la plus faible

Verrouillage au niveau de la page

• Le verrouillage au niveau de la page est un verrou dans Mysql dont la granularité de verrouillage se situe entre le verrouillage au niveau de la ligne et le verrouillage au niveau de la table. Les verrous au niveau des tables sont rapides mais comportent de nombreux conflits. Les verrous au niveau des lignes présentent peu de conflits mais sont lents. Le verrouillage au niveau de la page est utilisé pour verrouiller un groupe d'enregistrements adjacent. BDB prend en charge les verrous au niveau de la page

Le mécanisme de verrouillage du moteur de stockage commun de MySQL

• MyISAM et MEMORY utilisent des verrous au niveau de la table

• BDB utilise des verrous au niveau de la page ou des verrous au niveau de la table.

• InnoDB prend en charge les verrous au niveau de la ligne et les verrous au niveau de la table. La valeur par défaut est les verrous au niveau de la ligne

Les verrous de ligne et les verrous de table InnoDB

Le moteur InnoDB prend en charge à la fois les verrous de ligne et les verrous de table. Alors, quand la table entière sera-t-elle verrouillée, et quand une ligne sera-t-elle verrouillée ? ?

• Le verrouillage des lignes InnoDB est obtenu en verrouillant les éléments d'index sur l'index. Ceci est différent de Mysql et Oracle, qui verrouillent les lignes de données correspondantes dans le bloc de données réalisé. Cette fonctionnalité d'implémentation du verrouillage de ligne d'InnoDB signifie : InnoDB utilise des verrous au niveau de la ligne uniquement lorsque les données sont récupérées via des conditions d'index, sinon InnoDb utilisera des verrous de table

• Application pratique , vous devez faire attention à cette fonctionnalité du verrouillage de ligne InnoDB, sinon cela entraînera facilement un grand nombre de conflits de verrouillage, affectant ainsi les performances de concurrence

• Lors d'une requête sans conditions d'indexation , InnoDB utilise C'est un verrou de table, pas un verrou de ligne

• Puisque le verrou de ligne de MySQL est un verrou pour l'index, pas un verrou pour l'enregistrement, bien que les enregistrements de différentes lignes soient accessibles, if L'utilisation de clés du même index provoquera des conflits de verrouillage.

• Lorsqu'une table a plusieurs index, différentes transactions peuvent utiliser différents index pour verrouiller différentes lignes. De plus, que ce soit en utilisant un index de clé primaire, un index unique ou un index ordinaire, InnoDB utilisera des verrous de ligne. sont utilisés pour verrouiller les données

• Même si le champ d'index est utilisé dans la condition, l'utilisation ou non de l'index pour récupérer les données est déterminée par Mysql en jugeant le coût des différents plans de lignes. Si MySQL pense qu'une analyse complète des tables est plus efficace, comme pour certaines très petites tables, il n'utilisera pas d'index. Dans ce cas, InnoDB utilisera des verrous de table au lieu de verrous de lignes. Par conséquent, lors de l'analyse des conflits de verrouillage, n'oubliez pas de vérifier le plan d'exécution SQL

Verrous et blocages au niveau des lignes

• MyISAM ne produira pas d'interblocages car MyISAM obtient toujours tout ce dont il a besoin en même temps. Verrouiller, soit tous satisfaits, soit tout le monde attend. Dans InnoDB, les verrous sont acquis progressivement, ce qui entraîne la possibilité d'un blocage

• Dans MySQL, les verrous au niveau des lignes ne verrouillent pas directement les enregistrements, mais verrouillent les index. Les index sont divisés en index de clé primaire et index de clé non primaire. Si une instruction SQL opère sur l'index de clé primaire, MySQL verrouillera l'index de clé primaire. Si une instruction opère sur un index de clé non primaire, MySQL verrouillera d'abord l'index de clé primaire. index de clé non primaire. Verrouillez ensuite l'index de clé primaire concerné. Lors des opérations de mise à jour et de suppression, MySQL verrouille non seulement tous les enregistrements d'index analysés par la condition Where, mais verrouille également les valeurs de clé adjacentes, ce qu'on appelle le verrouillage de la clé suivante

• Deadlock : Lorsque deux transactions sont exécutées en même temps, l'une verrouille l'index de clé primaire et attend d'autres index associés. L'autre verrouille l'index de clé non primaire et attend l'index de clé primaire. Une impasse se produit.

• Après un blocage, InnoDB peut généralement le détecter et faire en sorte qu'une transaction libère le verrou et annule, et qu'une autre acquière le verrou pour terminer la transaction

Évitez les blocages

• Si différents programmes accèdent à plusieurs tables simultanément, essayez de convenir d'accéder aux tables dans le même ordre, ce qui peut réduire considérablement le risque de blocage

• Dans la même transaction, essayez de verrouiller toutes les ressources nécessaires en même temps pour réduire la probabilité de blocage

• Pour les parties métiers très sujettes aux blocages, vous pouvez essayer d'utiliser une granularité de verrouillage améliorée pour réduire les blocages grâce au verrouillage au niveau de la table

Les verrous partagés et les verrous exclusifs

• Les verrous au niveau des lignes sont dans MySQL La granularité de verrouillage la plus fine, les verrous au niveau des lignes peuvent réduire considérablement les conflits dans les opérations de base de données. Les verrous au niveau des lignes sont divisés en verrous partagés et verrous exclusifs

1. Les verrous partagés

• les verrous partagés sont également appelés verrous en lecture. , est le verrou créé par l'opération de lecture. D'autres utilisateurs peuvent lire les données simultanément, mais aucune transaction ne peut modifier les données tant que tous les verrous partagés n'ont pas été libérés.

• Si la transaction T ajoute un verrou partagé aux données A, les autres transactions ne peuvent ajouter que des verrous partagés à A et ne peuvent pas ajouter de verrous exclusifs. Les transactions bénéficiant de verrous partagés peuvent uniquement lire les données et ne peuvent pas modifier les données

• Si la transaction T ajoute un verrou partagé aux données A puis modifie les données, les autres transactions ne pourront pas obtenir le partage. De même, si plusieurs transactions acquièrent des verrous partagés sur les mêmes données, aucune transaction ne peut modifier les données

• Utilisation : SSELECT ... VERROUILLER EN MODE PARTAGE

  Ajoutez LOCK IN SHARE MODE après l'instruction de requête, Mysql ajoutera un verrou partagé à chaque ligne du résultat de la requête lorsqu'aucun autre thread ne se concentrera sur le résultat de la requête. Lorsqu'une ligne utilise un verrou exclusif, elle peut demander avec succès un verrou partagé, sinon elle sera bloquée. D'autres threads peuvent également lire des tables à l'aide de verrous partagés, et ces threads lisent la même version des données

Verrou exclusif

• . Le verrouillage exclusif est également appelé verrouillage en écriture. Si la transaction T ajoute un verrou exclusif aux données A, les autres transactions ne peuvent ajouter aucun type de blocage à A. Une transaction qui acquiert un verrou exclusif peut à la fois lire et modifier les données

• Utilisation : SELECT ... FOR UPDATE. Ajoutez FOR UPDATE après l'instruction de requête et MySQL ajoutera un verrou exclusif à chaque ligne du résultat de la requête. Lorsqu'aucun autre thread n'utilise un verrou exclusif sur une ligne du jeu de résultats de la requête, vous pouvez l'appliquer avec succès. pour un verrou exclusif. Sinon, il sera bloqué

3. Verrouillage d'intention

• Le verrouillage d'intention est un verrou au niveau de la table, qui est conçu. pour révéler les éléments suivants dans une transaction Le type de verrou qui sera demandé pour une ligne. Deux verrous de table dans InnoDB :

• Intention Shared Lock (IS) : indique que la transaction se prépare à ajouter un verrou partagé à la ligne de données, c'est-à-dire un verrou partagé est ajouté à une ligne de données Le verrou IS de la table doit être obtenu avant

• Intention verrou exclusif (IX) : Indique que la transaction se prépare à ajouter un verrou exclusif à la ligne de données , indiquant que la transaction doit d'abord ajouter un verrou exclusif à une ligne de données. Le verrou IX de la table

• Le verrou d'intention est automatiquement ajouté par InnoDB et ne nécessite pas d'utilisateur. intervention

Résumé

Pour l'insertion, la mise à jour, la suppression, InnoDB ajoutera automatiquement des verrous exclusifs aux données impliquées Pour les instructions Select générales, InnoDB ; n'ajoutera aucun verrou et les transactions peuvent être explicitement ajoutées via les instructions suivantes Verrouillage partagé ou verrouillage exclusif

• Verrouillage partagé : sélectionnez ... VERROUILLER EN MODE PARTAGE

• Verrouillage exclusif : SÉLECTIONNER... POUR LA MISE À JOUR

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!