Introduction aux verrous dans MYSQL-tutoriel mysql-php.cn

Comme introduit dans le mécanisme de verrouillage de la base de données, dans le SGBD, les verrous de base de données peuvent être divisés en verrous au niveau de la ligne (moteur INNODB), verrous au niveau de la table (moteur MYISAM) et verrous au niveau de la page (moteur BDB) en fonction du verrou. granularité.

Verrouillage au niveau de la ligne

Le verrouillage au niveau de la ligne est le verrouillage le plus granulaire de Mysql, ce qui signifie que seule la ligne de l'opération en cours 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 un verrouillage lent peuvent se produire ; la granularité du verrouillage est la plus faible, la probabilité de conflit de verrouillage est la plus faible et la concurrence est la plus élevée.

Verrouillage au niveau de la table

Le verrouillage au niveau de la table est le verrou avec la plus grande granularité dans MySQL, ce qui signifie verrouiller la table entière de l'opération en cours. Il est simple à mettre en œuvre et consomme moins de ressources. , et est utilisé 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. Les verrous au niveau de la table sont divisés en verrous de lecture partagés de table (verrous partagés) et verrous d'écriture exclusifs de table (verrous exclusifs).

Caractéristiques

Faible surcharge, verrouillage rapide ; pas de blocage ; grande granularité de verrouillage, probabilité la plus élevée de conflits de verrouillage et concurrence 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. Un niveau de page compromis a donc été adopté, verrouillant un groupe d'enregistrements adjacents à la fois. BDB prend en charge les verrous au niveau de la page

Caractéristiques

La surcharge et le temps de verrouillage sont limités entre les verrous de table et les verrous de ligne ; des blocages se produiront ; la granularité du verrouillage est limitée entre les verrous de table et les verrous de ligne. généralement

Le mécanisme de verrouillage du moteur de stockage couramment utilisé par MySQL

MyISAM et MEMORY utilisent le verrouillage au niveau de la table (verrouillage au niveau de la table)

BDB utilise le verrouillage de page (page - verrouillage au niveau) ou le verrouillage au niveau de la table, la valeur par défaut est le verrouillage de la page

InnoDB prend en charge le verrouillage au niveau de la ligne (verrouillage au niveau de la ligne) et le verrouillage au niveau de la table, la valeur par défaut est le verrouillage au niveau de la ligne

Les verrous de ligne et les verrous de table dans Innodb

Comme mentionné précédemment, le moteur Innodb prend en charge à la fois les verrous de ligne et les verrous de table. être verrouillé ? Quand une table verrouille-t-elle une seule ligne ?

Le verrouillage des lignes InnoDB est implémenté en verrouillant les entrées d'index sur l'index. Ceci est différent de MySQL et Oracle, qui sont implémentés en verrouillant les lignes de données correspondantes dans le bloc de données. La fonctionnalité d'implémentation du verrouillage de ligne d'InnoDB signifie qu'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 !

Dans les applications pratiques, une attention particulière doit être accordée à cette fonctionnalité des verrous de ligne InnoDB, sinon elle peut provoquer un grand nombre de conflits de verrouillage, affectant ainsi les performances de concurrence.

Lors d'une requête sans conditions d'index, InnoDB utilise des verrous de table au lieu de verrous de ligne.
Étant donné que le verrouillage de ligne de MySQL est destiné à l'index, pas à l'enregistrement, bien que les enregistrements de lignes différentes soient accessibles, si la même clé d'index est utilisée, il y aura un conflit de verrouillage . Veuillez y prêter attention lors de la conception de votre application.
Lorsqu'une table a plusieurs index, différentes transactions peuvent utiliser différents index pour verrouiller différentes lignes. De plus, que vous utilisiez un index de clé primaire, un index unique ou un index normal, InnoDB. utilisera des verrous de ligne pour verrouiller les données.
Même si un champ d'index est utilisé dans la condition, l'utilisation ou non de l'index pour récupérer des données est déterminée par MySQL en jugeant le coût des différents plans d'exécution si MySQL estime que le plein. L'analyse des tables est plus efficace. Élevée, par exemple, pour certaines très petites tables, elle n'utilisera pas d'index. Dans ce cas, InnoDB utilisera des verrous de table au lieu de verrous de ligne. Par conséquent, lors de l'analyse des conflits de verrouillage, n'oubliez pas de vérifier le plan d'exécution SQL pour confirmer si l'index est réellement utilisé.

Verrous et blocages au niveau des lignes

Aucun blocage ne se produira dans MyISAM, car MyISAM obtient toujours tous les verrous dont il a besoin en même temps. ou attendez-les tous. Dans InnoDB, les verrous sont acquis progressivement, entraînant 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, puis verrouillez-le. Index de clé primaire associé. Pendant les opérations UPDATE et DELETE, 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.

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 produira.

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.

Il existe de nombreuses façons d'éviter les blocages. En voici seulement trois courantes

1 Si différents programmes accèdent à plusieurs tables simultanément, essayez de vous mettre d'accord sur le même ordre d'accès. table peut réduire considérablement le risque de blocage.

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

;

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

Les verrous au niveau des lignes ont la granularité de verrouillage la plus fine. dans Mysql, une sorte de verrou au niveau de la ligne peut 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 deux types : les verrous partagés et les verrous exclusifs. Cet article présentera en détail les concepts, l'utilisation et les précautions des verrous partagés et des verrous exclusifs.

Verrou partagé (Share Lock)

Le verrou partagé, également connu sous le nom de verrou de lecture, est un verrou créé par une opération de lecture. D'autres utilisateurs peuvent lire les données simultanément, mais aucune transaction ne peut modifier les données (acquérir un verrou exclusif sur 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 des données et ne peuvent pas les modifier.

Utilisation

SELECT ... LOCK IN SHARE MODE ;

Ajoutez LOCK IN SHARE MODE après l'instruction de requête, Mysql partagera chaque ligne dans le résultat de la requête Lock, lorsque aucun autre thread n'utilise de verrou exclusif sur aucune ligne du jeu de résultats de la requête, il peut demander avec succès un verrou partagé, sinon il sera bloqué. D'autres threads peuvent également lire des tables qui utilisent des verrous partagés, et ces threads lisent la même version des données.

Verrouillage exclusif (eXclusive Lock)

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 plus ajouter aucun type de blocage à A. . Les transactions bénéficiant de verrous exclusifs peuvent à la fois lire et modifier des données.

Utilisation

SELECT ... FOR UPDATE;

Ajouter FOR UPDATE après l'instruction de requête Mysql ajoutera un verrou exclusif à chaque ligne du résultat de la requête s'il y en a. is no Lorsque d'autres threads utilisent un verrou exclusif sur n'importe quelle ligne du jeu de résultats de la requête, ils peuvent demander avec succès un verrou exclusif, sinon ils seront bloqués.

Verrouillage d'intention

Le verrouillage d'intention est un verrou au niveau de la table, qui est principalement conçu pour révéler le type de verrou qui sera demandé pour la ligne suivante dans une transaction. 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, ce qui signifie qu'avant d'ajouter un verrou partagé à une ligne de données, l'IS le verrou de la table doit d'abord être obtenu

Verrou exclusif d'intention (IX) : similaire à ce qui précède, cela signifie que la transaction se prépare à ajouter un verrou exclusif à la ligne de données, indiquant que la transaction doit d'abord obtenir le verrou IX de la table avant d'ajouter un verrou exclusif à une ligne de données.

Les verrous d'intention sont automatiquement ajoutés par InnoDB et ne nécessitent aucune intervention de l'utilisateur.

Pour l'insertion, la mise à jour et la suppression, InnoDB ajoutera automatiquement des verrous exclusifs (X) aux données impliquées ; pour les instructions Select générales, InnoDB n'ajoutera aucun verrou et les transactions peuvent être affichées avec des verrous partagés via le déclarations suivantes ou verrouillage exclusif.

Verrouillage partagé : SÉLECTIONNER... VERROUILLER EN MODE PARTAGE ;

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

Recommandations associées :

La connexion entre les verrous et les index MySQL

Interblocage MySQL et analyse des journaux

Méthodes pour implémenter le verrouillage des instructions 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!