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
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)
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
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 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
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
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
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
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
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 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
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
. 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é
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
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!
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)
