Maîtrisez facilement les principes pertinents du mécanisme de verrouillage de la base de données MySQL

MySQL 5.1 prend en charge le verrouillage au niveau des tables pour les tables MyISAM et MEMORY, le verrouillage au niveau des pages pour les tables BDB et InnoDB. Verrouillage au niveau des lignes. Dans de nombreux cas, il est possible de deviner, grâce à la formation, quel type de verrou est le meilleur pour une application, mais il est généralement difficile de dire si un type de verrou donné est meilleur qu'un autre. Tout dépend de l'application, différentes parties de l'application peuvent nécessiter différents types de verrous. Pour déterminer si vous souhaitez utiliser un moteur de stockage verrouillable au niveau des lignes, vous devez examiner ce que fait votre application et la combinaison d'instructions select et update qu'elle utilise. Par exemple, la plupart des applications Web effectuent de nombreuses sélections et peu de suppressions, uniquement des mises à jour des valeurs clés et seulement quelques insertions de tableaux spécifiques. La configuration de base de MySQL MyISAM est bien réglée.

Dans MySQL, pour les moteurs de stockage qui utilisent le verrouillage au niveau de la table, il n'y aura pas de blocage lorsque la table est verrouillée. Ceci est géré en demandant toujours tous les verrous nécessaires immédiatement au début d'une requête et en verrouillant toujours la table dans le même ordre.

Pour WRITE, la méthode de verrouillage de table utilisée par MySQL fonctionne comme suit :

◆ S'il n'y a pas de verrou sur la table, placez-y un verrou en écriture.

◆ Sinon, placez la demande de verrouillage dans la file d'attente de verrouillage en écriture.

Pour READ, la méthode de verrouillage utilisée par MySQL fonctionne comme suit :

◆S'il n'y a pas de verrou en écriture sur la table, placez-y un verrou en lecture.

◆ Sinon, placez la demande de verrouillage dans la file d'attente des verrous de lecture.

Lorsqu'un verrou est libéré, le verrou peut être acquis par les threads de la file d'attente de verrouillage en écriture, puis par les threads de la file d'attente de verrouillage en lecture.

Cela signifie que si vous avez de nombreuses mises à jour sur une table, l'instruction SELECT attendra qu'il n'y ait plus de mises à jour.

Si les instructions INSERT n'entrent pas en conflit, vous pouvez librement mélanger des instructions INSERT et SELECT parallèles pour une table MyISAM sans verrouillage.

InnoDB utilise le verrouillage de ligne et BDB utilise le verrouillage de page. Avec les deux moteurs de stockage, des blocages sont possibles. En effet, InnoDB obtient automatiquement les verrous de ligne et BDB obtient les verrous de page pendant le traitement des instructions SQL, plutôt que lorsque la transaction est démarrée.

Avantages du verrouillage au niveau des lignes :

· Il n'y a que quelques conflits de verrouillage lorsque différentes lignes sont accédées dans de nombreux threads.

· Restaurer avec seulement un petit nombre de modifications.

· Une seule ligne peut être verrouillée pendant une longue période.

Inconvénients du verrouillage au niveau des lignes :

· Prend plus de mémoire que le verrouillage au niveau de la page ou de la table.

· Lorsqu'il est utilisé sur de grandes parties d'une table, plus lent que le verrouillage au niveau de la page ou de la table car vous devez acquérir plus de verrous.

· Si vous effectuez fréquemment des opérations GROUP BY sur la plupart de vos données ou si vous devez fréquemment analyser la table entière, cela sera nettement plus lent que les autres verrous.

· Avec le verrouillage de haut niveau, vous pouvez également facilement faire évoluer votre application en prenant en charge différents types de verrouillage, car le coût du verrouillage est inférieur à celui du verrouillage au niveau des lignes.

Les verrous de table ont priorité sur les verrous au niveau de la page ou de la ligne lorsque :

· La plupart des instructions de la table sont utilisées pour des lectures.

à

jour avec des clés strictes, vous pouvez mettre à jour ou supprimer une ligne qui peut être extraite avec une seule clé de lecture :

• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;

• DELETE FROM tbl_name WHERE unique_key_col = key_value ;

Des instructions INSERT parallèles et très peu d'instructions UPDATE ou DELETE.

· Il existe de nombreuses analyses ou opérations GROUP BY sur la table entière sans aucune opération d'écriture.

Options qui diffèrent du verrouillage au niveau des lignes ou des pages :

· Gestion des versions (par exemple, la technique utilisée dans MySQL pour les insertions parallèles), où il peut y avoir une opération d'écriture et plusieurs opérations de lecture en même temps. Cela signifie que la base de données ou la table prend en charge différentes vues des données, en fonction du moment où l'accès commence. D'autres termes courants sont « suivi du temps », « copie en écriture » ou « copie à la demande ».

· La réplication à la demande est prioritaire sur le verrouillage au niveau de la page ou de la ligne dans de nombreux cas. Cependant, dans le pire des cas, cela peut utiliser plus de mémoire que l’utilisation d’un verrouillage classique.

· En plus du verrouillage au niveau des lignes, vous pouvez utiliser le verrouillage au niveau de l'application, tel que GET_LOCK() et RELEASE_LOCK() dans MySQL. Il s'agit de verrous consultatifs qui ne fonctionneront que dans les applications qui fonctionnent correctement.

Pour atteindre une vitesse de verrouillage maximale, MySQL utilise le verrouillage de table (plutôt que le verrouillage de page, de ligne ou de colonne) pour tous les moteurs de stockage à l'exception d'InnoDB et BDB. Pour les tables InnoDB et BDB, MySQL n'utilise le verrouillage de table que si vous verrouillez explicitement la table avec LOCK TABLES si vous n'utilisez pas LOCK TABLES, car InnoDB utilise le verrouillage automatique au niveau de la ligne et BDB utilise le verrouillage au niveau de la page pour garantir l'isolation des transactions.

Mais pour les grandes tables, le verrouillage des tables est meilleur que le verrouillage des lignes pour la plupart des applications, mais présente certains inconvénients. Le verrouillage de table permet à plusieurs threads de lire simultanément une table, mais si un thread souhaite écrire dans la table, il doit d'abord obtenir un accès exclusif. Pendant la mise à jour, tous les autres threads souhaitant accéder à la table doivent attendre la fin de la mise à jour.

Les mises à jour des tables sont généralement considérées comme plus importantes que les extractions de tables, elles reçoivent donc une priorité plus élevée. Cela devrait garantir que l'activité de mise à jour d'une table ne peut pas mourir de faim, même s'il y a une forte activité SELECT sur la table.

Le verrouillage de table peut provoquer des problèmes dans des situations telles que lorsqu'un thread est en attente parce que le disque dur est plein et qu'il doit y avoir de l'espace libre avant que le thread puisse traiter. Dans ce cas, tous les threads souhaitant accéder à la table en question sont également mis en attente jusqu'à ce que davantage d'espace disque soit disponible.

Le verrouillage des tables est également problématique dans les situations suivantes :

· Un client émet une requête de longue durée.

· Ensuite, un autre client met à jour la même table. Le client doit attendre la fin du SELECT.

· Un autre client émet une autre instruction SELECT sur la même table. Étant donné que UPDATE a une priorité plus élevée que SELECT, l'instruction SELECT attend la fin de UPDATE et attend la fin du premier SELECT.

Certaines méthodes sont décrites ci-dessous pour éviter ou réduire les conflits provoqués par les verrous de table :

· Essayez d'accélérer l'exécution de l'instruction SELECT. Vous devrez peut-être créer des tableaux récapitulatifs pour ce faire.

· Démarrez mysqld avec --low-priority-updates. Cela donnera à toutes les instructions qui mettent à jour (modifient) une table une priorité inférieure à celle des instructions SELECT. Dans ce cas, la deuxième instruction SELECT de la situation précédente sera exécutée avant l'instruction UPDATE sans attendre la fin du premier SELECT.

· Vous pouvez utiliser l'instruction SET LOW_PRIORITY_UPDATES=1 pour spécifier que toutes les mises à jour dans une connexion spécifique doivent utiliser une faible priorité.

· Vous pouvez utiliser l'attribut LOW_PRIORITY pour attribuer une priorité inférieure à une instruction INSERT, UPDATE ou DELETE spécifique.

· Vous pouvez utiliser l'attribut HIGH_PRIORITY pour attribuer une priorité plus élevée à une instruction SELECT spécifique.

· Démarrez mysqld en spécifiant une valeur faible pour la variable système max_write_lock_count pour forcer MySQL à augmenter temporairement la priorité de toutes les instructions SELECT en attente d'une table après un nombre spécifique d'insertions. Cela permet d'attribuer un verrou READ après un certain nombre de verrous WRITE.

· Si vous rencontrez des problèmes avec INSERT combiné avec SELECT, utilisez les nouvelles tables MyISAM car elles prennent en charge SELECT et INSERT simultanés.

· Si vous mélangez des insertions et des suppressions sur la même table, INSERT DELAYED vous sera d'une grande aide.

· Si vous rencontrez des problèmes pour mélanger les instructions SELECT et DELETE sur la même table, l'option LIMIT de DELETE peut vous aider.

· L'utilisation de SQL_BUFFER_RESULT pour les instructions SELECT peut aider à réduire les temps de verrouillage des tables.

· Le code de verrouillage dans mysys/thr_lock.c peut être modifié pour utiliser une seule file d'attente. Dans ce cas, les verrous en écriture et les verrous en lecture auront la même priorité, ce qui peut être utile pour certaines applications.

Voici quelques conseils liés au verrouillage de table dans MySQL :

· Si vous ne mélangez pas les mises à jour avec des sélections qui nécessitent de vérifier plusieurs lignes dans la même table, vous pouvez effectuer des opérations parallèles.

· Vous pouvez utiliser LOCK TABLES pour améliorer la vitesse, car de nombreuses mises à jour avec un seul verrou sont beaucoup plus rapides que les mises à jour sans verrous. Diviser le contenu du tableau en plusieurs tableaux peut également aider.

· Si vous rencontrez des problèmes de vitesse lors du verrouillage des tables dans MySQL, vous pouvez convertir la table en table InnoDB ou BDB pour améliorer les performances.