Un article présentant l'implémentation des verrous dans MySQL

Implémentation du verrouillage MySQL

MySQL est un système de gestion de base de données relationnelle très populaire qui prend en charge le traitement de nombreux utilisateurs et transactions simultanés. Dans le cas d'une concurrence élevée, le mécanisme de verrouillage de MySQL est un composant crucial. Comment utiliser le mécanisme de verrouillage de manière rationnelle et efficace pour améliorer les performances de concurrence est une partie importante de l'optimisation MySQL. Cet article présentera comment les verrous sont implémentés dans MySQL et comment choisir différents types de verrous pour maximiser les performances.

Présentation

Dans une base de données à accès simultané, lorsque plusieurs utilisateurs tentent de modifier des données partagées, divers problèmes de cohérence des données peuvent survenir. Les problèmes les plus courants sont les conflits de lecture et d’écriture et les conditions de course aux données. Pour résoudre ces problèmes, MySQL propose différents types de verrous pour permettre un accès simultané tout en garantissant la cohérence des données.

Les verrous MySQL sont principalement divisés en deux catégories : les verrous partagés et les verrous exclusifs. Les verrous partagés permettent à plusieurs utilisateurs d'accéder à une ressource en même temps. Ces utilisateurs peuvent uniquement lire la ressource mais ne peuvent pas la modifier. Par conséquent, lorsqu'un utilisateur détient déjà un verrou partagé, les autres utilisateurs peuvent utiliser le verrou partagé pour lire la même ressource, mais ne peuvent pas utiliser un verrou exclusif pour la modifier. Un verrou exclusif permet à un seul utilisateur d'accéder à la même ressource à la fois, et plusieurs utilisateurs ne peuvent pas détenir le verrou en même temps.

Implémentation des verrous MySQL

Les méthodes d'implémentation des verrous MySQL incluent : le verrouillage de table, le verrouillage de ligne, le verrouillage de page, le verrouillage de tuple et le verrouillage d'espace, etc.

1. Verrouillage de table

Le verrouillage de table est la méthode de verrouillage la plus basique Lors de l'opération de modification, la table entière est verrouillée. Par conséquent, les autres utilisateurs ne pourront pas accéder à la table, ce qui entraînera une réduction des performances de concurrence. Ce mécanisme de verrouillage peut fonctionner correctement si les données de la table sont petites ou s'il y a peu d'accès simultanés. Cependant, dans les bases de données volumineuses ou hautement concurrentes, les verrous de table peuvent entraîner des problèmes de concurrence et réduire les performances du système.

La syntaxe du verrouillage de table est la suivante :

• Verrouiller toutes les tables : LOCK TABLES nom_table [AS alias] type_verrou[,nom_table [AS alias] type_verrou] ...
• Déverrouiller : DÉVERROUILLER TABLES

Parmi eux, lock_type peut être le type suivant :

• READ : verrou de lecture partagé.
• WRITE : verrouillage en écriture exclusif.

2. Verrouillage de ligne

Le verrouillage de ligne est un mécanisme de verrouillage plus efficace dans MySQL. Pour les modifications apportées aux enregistrements de la table, MySQL verrouille uniquement les lignes de la table qui remplissent les conditions. Les autres accès aux lignes qui ne nécessitent pas de verrouillage ne sont pas affectés. Dans le cas d'une concurrence élevée et d'un volume de données important, l'utilisation de verrous de ligne peut réduire efficacement la portée et la durée du verrouillage et améliorer les performances du système.

Dans le moteur de stockage InnoDB, le verrouillage des lignes est le mécanisme de verrouillage par défaut.

Syntaxe de verrouillage de ligne :

• Transaction ouverte : START TRANSACTION
• Verrouillage de ligne : SELECT … FOR UPDATE ou UPDATE … WHERE …
• Commit transaction : COMMIT ou ROLLBACK

Parmi eux, SELECT … FOR UPDATE peut être verrouillé et ligne marquée pour empêcher la ligne d’être modifiée par d’autres transactions. L'instruction UPDATE ... WHERE ... peut également verrouiller une ou plusieurs lignes qui remplissent les conditions.

3. Verrouillage de page

Le verrouillage de page est un mécanisme de verrouillage de MySQL, qui verrouille la table en unités de pages lors de l'analyse de la table. Le verrouillage de page est plus efficace que le verrouillage de ligne lorsque des modifications sont apportées à plusieurs enregistrements, car la page est composée de plusieurs lignes. Le verrouillage des lignes, en revanche, fonctionne de toute façon individuellement, ligne par ligne, et fonctionne donc plus lentement lors de la modification de plusieurs lignes.

Comme son nom l'indique, le verrouillage de page consiste à verrouiller la page Lors du processus de modification d'une certaine page, le verrouillage au niveau de la page protège uniquement la page entière et informe le système que le verrouillage des autres pages n'entravera pas l'exécution de. d'autres transactions. Verrouiller l'intégralité de la table ou de l'index d'une page évite de verrouiller trop de lignes, mais peut également entraîner des problèmes de concurrence.

Comment utiliser les verrous de page :

• Changez le mode du moteur de stockage InnoDB et définissez le mode de validation (commit_mode) sur 2, ce qui signifie utiliser des verrous au niveau de la page.
• Verrouillez et déverrouillez les enregistrements à chaque étape de l'exécution des commandes SQL.

Vous pouvez utiliser la commande suivante pour que le moteur InnoDB utilise des verrous au niveau de la page :

• SET GLOBAL innodb_locks_unsafe_for_binlog=1;
• SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Lorsque vous utilisez des verrous de page, vous devez être veillez à ne pas utiliser d'instructions SQL. Le LIMIT limite le nombre de lignes de requête, car cela pourrait entraîner le passage de la base de données de verrous au niveau des lignes à des verrous au niveau de la table.

4. Verrouillage Tuple

Le verrouillage Tuple, également connu sous le nom de verrouillage Tuple, est un verrou dans le moteur de stockage MySQL. Dans le moteur InnoDB, les verrous de tuple ne sont appliqués qu'aux index secondaires « non uniques » pour prendre en charge les opérations simultanées. Si le tuple est verrouillé par une autre transaction, l'autre transaction ne peut compléter que la modification de l'enregistrement avec la même valeur d'index, pas la modification de la ligne de données entière.

Syntaxe de base du verrouillage de tuple :

• SELECT … FROM table_name WHERE key_column=user_input FOR UPDATE;
• UPDATE … WHERE key_column=user_input

Parmi eux, user_input représente l'entrée de l'utilisateur final, qui peut être un une valeur unique ou une déclaration de condition ou une portée, etc.

N'oubliez pas d'être prudent lorsque vous utilisez des verrous de tuple. Une utilisation déraisonnable réduira considérablement les performances de simultanéité de la base de données.

5. Gap lock

Gap lock, également appelé Gap lock, est généralement utilisé pour empêcher les lectures non répétées. Apparaît souvent dans les opérations SQL qui s'étendent sur plusieurs lignes à la fois.

Par exemple :

SELECT c1 FROM tbl WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

À ce stade, la base de données doit verrouiller toutes les lignes avec des valeurs c1 comprises entre 10 et 20. Le type de verrouillage correspondant est appelé verrouillage d'espacement, qui verrouille les valeurs qui se situent dans l'espace mais qui n'existent pas.

Comment utiliser les verrous d'espacement :

• Utilisez la vue de lecture fantôme (MVCC) pour suivre les opérations de lecture en cours et fournir des verrous de lecture au niveau des lignes ou des verrous d'espacement pour les opérations de lecture futures.
• Gap Check : le verrouillage d'espacement de S vérifiera les lignes une par une pour garantir que ces lignes ne sont pas mises à jour ou supprimées tout en conservant des "lectures répétables".
• L'un des problèmes les plus importants liés aux verrous d'espacement et à leur accès est d'être très prudent quant aux opérations simultanées d'autres choses.

Résumé

Dans le développement réel, nous devons choisir le bon type de verrou et utiliser les verrous appropriés en fonction des besoins réels pour éviter les blocages, améliorer la simultanéité du système et essayer d'éviter la dégradation des performances du système. Les différentes fonctions de verrouillage fournies par MySQL peuvent résoudre le problème des opérations de transaction ou de l'accès simultané de plusieurs utilisateurs.

La sélection correcte du mécanisme de verrouillage MySQL approprié peut améliorer efficacement les performances et la concurrence du système, rendant nos applications plus robustes et fiables.

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!