Explication détaillée des problèmes impliquant les verrous dans MySQL

Comment accéder simultanément à la base de données ? La réponse est 加锁.

Recommandé : "Tutoriel vidéo MySQL"

Parlons ensuite du mécanisme de verrouillage de la base de données. Quels verrous y a-t-il dans la base de données ?

Tout d'abord, les verrous sont une technologie de contrôle de concurrence. Les verrous sont utilisés pour protéger les données lorsque plusieurs utilisateurs accèdent aux mêmes données en même temps.

Il existe 2 types de verrouillage de base :

Verrouillage partagé (S) : plusieurs transactions peuvent bloquer une page partagée ; aucune transaction ne peut modifier la page généralement ; est lu, le verrou S est immédiatement libéré. Lors de l'exécution de l'instruction select, vous devez ajouter un verrou partagé à l'objet opération (table ou certains enregistrements), mais avant d'ajouter le verrou, vous devez vérifier s'il existe un verrou exclusif. Sinon, vous pouvez ajouter un verrou partagé. (N verrous partagés peuvent être ajoutés à un objet), sinon cela ne fonctionnera pas. Le verrou partagé est généralement libéré après l'exécution de l'instruction select. Bien entendu, il peut également être libéré à la fin de la transaction (y compris la fin normale et la fin anormale), principalement en fonction du niveau d'isolement de la transaction défini par la base de données.

Verrou exclusif (X) : Une seule transaction est autorisée à bloquer cette page ; toute autre transaction doit attendre que le verrou X soit libéré avant d'accéder à la page ; . Lors de l'exécution des instructions insert, update et delete, vous devez ajouter un verrou exclusif à l'objet en cours d'utilisation. Avant d'ajouter un verrou exclusif, vous devez confirmer qu'il n'y a pas d'autres verrous sur l'objet. ne peut pas ajouter d'autres verrous à l'objet. Le verrou exclusif est généralement libéré à la fin de la transaction (bien sûr, il existe des exceptions, c'est-à-dire lorsque le niveau d'isolation des transactions de la base de données est défini sur Read Uncommit (lecture des données non validées), dans ce cas, le verrou exclusif sera libéré après la fin de la transaction. l'opération de mise à jour est terminée) libérée, pas à la fin de la transaction).

Mécanisme de verrouillage

Étant donné que des verrous sont utilisés, il existe une possibilité de blocage.

Quatre conditions nécessaires à une impasse :

Condition d'exclusion mutuelle : Une ressource ne peut être utilisée que par un seul processus à la fois.

Conditions de demande et de conservation : lorsqu'un processus est bloqué en raison d'une demande de ressources, il conservera les ressources obtenues.

Condition de non-privation : les ressources qu'un processus a obtenues ne peuvent pas en être privées de force avant qu'elles ne soient épuisées.

Condition d'attente de boucle : plusieurs processus forment une boucle tête-bêche en attente de relation de ressources.

Tant qu'un blocage se produit dans le système, ces conditions doivent être vraies, et tant que l'une des conditions ci-dessus n'est pas remplie, un blocage ne se produira pas.

Prévenir les impasses

Prévenir les impasses ne nécessite que la destruction de l'une des quatre conditions nécessaires à l'impasse.

1) Détruire la condition d'exclusion mutuelle

Si toutes les ressources système sont autorisées à être partagées, le système n'entrera pas dans un état de blocage. Cependant, certaines ressources ne sont pas accessibles en même temps. Les ressources critiques telles que les imprimantes ne peuvent être utilisées que de manière mutuellement exclusive. Il n’est donc pas possible de détruire les conditions d’exclusion mutuelle pour éviter une impasse et, dans certains cas, cette exclusivité mutuelle devrait être protégée.

2) Destruction des conditions inaliénables

Lorsqu'un processus qui a conservé des ressources inaliénables demande de nouvelles ressources mais ne peut être satisfait, il doit libérer toutes les ressources qu'il a retenues, puis réappliquer quand. nécessaire à l'avenir. Cela signifie que les ressources déjà occupées par un processus seront temporairement libérées, ou privées, ou violeront ainsi la condition inaliénable.

Cette stratégie est complexe à mettre en œuvre. La libération des ressources acquises peut entraîner l'échec de l'étape de travail précédente. Une application répétée et la libération des ressources augmenteront la surcharge du système et réduiront le débit du système. Cette méthode est souvent utilisée pour les ressources dont l'état est facile à sauvegarder et à restaurer, telles que les registres du processeur et les ressources mémoire. Elle ne peut généralement pas être utilisée pour des ressources telles que les imprimantes.

3) Détruisez les conditions de demande et de rétention

Utilisez la méthode d'allocation pré-statique, c'est-à-dire que le processus s'applique à toutes les ressources dont il a besoin une fois avant de s'exécuter avant que ses ressources ne soient satisfaites. , il ne le mettra pas en service. Une fois mises en service, ces ressources lui appartiendront toujours et aucune autre demande de ressources ne sera effectuée, garantissant ainsi que le système ne se bloquera pas.

Cette méthode est simple à mettre en œuvre, mais les inconvénients sont également évidents. Les ressources système sont sérieusement gaspillées. Certaines de ces ressources ne peuvent être utilisées qu'au début ou vers la fin de l'exécution, voire même. pas utilisé du tout. Cela entraînera également un phénomène de « famine ». Lorsqu'une certaine ressource est occupée par d'autres processus pendant une longue période, le démarrage du processus en attente de la ressource sera retardé.

4) Détruire la condition d'attente de boucle

Afin de détruire la condition d'attente de boucle, la méthode d'allocation séquentielle de ressources peut être utilisée. Tout d'abord, numérotez les ressources dans le système et stipulez que chaque processus doit demander des ressources par ordre croissant de nombres, et que toutes les ressources similaires peuvent être demandées en même temps. En d’autres termes, tant qu’un processus s’applique pour allouer des ressources Ri, le processus ne peut s’appliquer qu’aux ressources dont les nombres sont supérieurs à Ri dans les futures applications de ressources.

Le problème de cette méthode est que la numérotation doit être relativement stable, ce qui limite l'ajout de nouveaux types d'équipements même si l'ordre dans lequel la plupart des travaux utilisent réellement ces ressources est pris en compte lors de la numérotation des ressources, cela arrive souvent si ; l'ordre dans lequel les tâches utilisent les ressources est différent de l'ordre spécifié par le système, les ressources seront gaspillées ; de plus, cette méthode de demande de ressources dans l'ordre spécifié causera inévitablement des problèmes aux utilisateurs lors de la programmation.

Libérer l'impasse

1) Priver les ressources du processus de blocage

2) Mettre fin à tout ou partie des processus

Granarité du verrouillage MySQL (c'est-à-dire le niveau de verrouillage)

Les moteurs de stockage MySQL utilisent trois types (niveaux) de mécanismes de verrouillage : le verrouillage au niveau de la ligne, le verrouillage au niveau de la page et le verrouillage au niveau de la table.

1. Verrouillage au niveau de la table : verrouillez directement la table entière pendant votre période de verrouillage, les autres processus ne peuvent pas écrire dans la table. Si vous disposez d'un verrou en écriture, les autres processus ne sont pas autorisés à lire. Caractéristiques : faible surcharge, verrouillage rapide ; aucun blocage ; granularité de verrouillage la plus élevée, probabilité de conflits de verrouillage la plus élevée et concurrence la plus faible.

Le moteur de stockage MyISAM utilise des verrous au niveau de la table.

Il existe deux modes : le verrouillage en lecture partagé de la table et le verrouillage en écriture exclusif de la table. La commande pour ajouter un verrou en lecture : lock table table name read ; La commande pour supprimer le verrou : déverrouiller les tables.

Prise en charge de l'insertion simultanée : prise en charge des opérations de requête et d'insertion à exécuter simultanément (insertion simultanée à la fin de la table).

Mécanisme de planification des verrouillages : priorité du verrouillage en écriture. Un processus demande un verrou en lecture sur une table MyISAM, et en même temps un autre processus demande également un verrou en écriture sur la même table. Comment MySQL le gère-t-il ? La réponse est que le processus d’écriture acquiert le verrou en premier.

2. Verrouillage au niveau de la ligne : verrouillez uniquement l'enregistrement spécifié, afin que d'autres processus puissent toujours opérer sur d'autres enregistrements de la même table. Caractéristiques : surcharge élevée, blocages lents pouvant survenir ; la granularité du verrouillage est la plus faible, la probabilité de conflits de verrouillage est la plus faible et la concurrence est la plus élevée.

Le moteur de stockage InnoDB prend en charge à la fois les verrous au niveau des lignes et les verrous au niveau des tables, mais les verrous au niveau des lignes sont utilisés par défaut.

3. Verrouillage au niveau de la page : verrouillez un groupe d'enregistrements adjacents à la fois. La surcharge et le temps de verrouillage se situent entre les verrous de table et les verrous de ligne ; des blocages se produiront ; la granularité du verrouillage se situe entre les verrous de table et les verrous de ligne, et la concurrence est moyenne.

La méthode la plus couramment utilisée pour gérer les accès simultanés de plusieurs utilisateurs est le verrouillage. Lorsqu'un utilisateur verrouille un objet dans la base de données, les autres utilisateurs ne peuvent plus accéder à l'objet. L'impact du verrouillage sur les accès concurrents se reflète dans la granularité du verrouillage. Par exemple, (verrouillage de table) un verrou placé sur une table limite l'accès simultané à la table entière ; (verrouillage de page) un verrou placé sur une page de données limite l'accès à la page de données entière (verrouillage de ligne) un verrou placé sur une ligne ; Le verrou limite l'accès simultané à la ligne uniquement.

Les concepts, méthodes de mise en œuvre et scénarios d'utilisation du verrouillage optimiste et du verrouillage pessimiste

Il existe deux mécanismes de verrouillage : le verrouillage pessimiste et le verrouillage optimiste.

Le verrouillage pessimiste, comme son nom l'indique, est pessimiste à l'égard du monde. Il estime que la probabilité que d'autres personnes accèdent aux données changeantes est très élevée, il verrouille donc les données lorsque les données commencent à changer jusqu'au changement. est terminé seulement ensuite libéré.

Un appel de verrouillage pessimiste typique dépendant de la base de données :

sélectionnez * depuis le compte où name="Erica" ​​​​​​pour la mise à jour

Cette instruction SQL verrouille tous les comptes du compte table Enregistrements correspondant aux critères de recherche (name="Erica"). Avant que cette transaction ne soit validée (le verrou pendant la transaction sera libéré lorsque la transaction sera validée), le monde extérieur ne peut pas modifier ces enregistrements. Cette instruction est utilisée pour verrouiller des lignes spécifiques (s'il existe une clause Where, ce sont les lignes qui remplissent la condition Where). Lorsque ces lignes sont verrouillées, d'autres sessions peuvent sélectionner ces lignes, mais elles ne peuvent pas modifier ou supprimer ces lignes tant que la transaction de l'instruction n'est pas terminée par une instruction de validation ou une instruction rollback. Il est à noter que select...for update doit être placé dans le type de transaction MySQL, à savoir start et commit, sinon cela ne fonctionnera pas.

Le pessimisme peut entraîner de longs temps de verrouillage et de mauvaises émissions, en particulier des transactions longues, affectant les performances globales du système.

Méthode de mise en œuvre du verrouillage pessimiste :

Le verrouillage pessimiste est également implémenté sur la base du mécanisme de verrouillage de la base de données. De nombreux mécanismes de verrouillage de ce type sont utilisés dans les bases de données relationnelles traditionnelles, tels que les verrous de ligne, les verrous de table, les verrous de lecture, les verrous d'écriture, etc., qui sont tous verrouillés avant les opérations.

Le verrouillage optimiste, qui est relativement optimiste quant au monde, estime que la probabilité que d'autres personnes accèdent aux données changeantes est très faible, de sorte que les données ne seront pas verrouillées tant que la modification n'est pas terminée et que la modification n'est pas prête à être soumis à la base de données. Vous ne verrouillez pas l'objet lorsque vous le lisez et le modifiez, et vous ne le relâchez pas une fois les modifications terminées. Le verrouillage optimiste ne peut pas résoudre le problème des lectures sales.

Le temps de verrouillage du verrouillage optimiste est plus court que celui du verrouillage pessimiste, ce qui améliore considérablement les performances globales du système dans des conditions de grande concurrence.

Comment mettre en œuvre le verrouillage optimiste :

1. La plupart d'entre eux sont basés sur le mécanisme d'enregistrement de la version des données (version), qui nécessite l'ajout d'un identifiant de version à chaque ligne de données (c'est-à-dire, un champ supplémentaire pour chaque ligne de données) version), à chaque mise à jour des données, le numéro de version correspondant +1 doit être mis à jour.

Principe de fonctionnement : lors de la lecture des données, lisez cette version ensemble, et lors de la mise à jour ultérieure, ajoutez-en un à ce numéro de version. À ce stade, les informations de version des données soumises sont comparées aux informations de version actuelle de l'enregistrement correspondant dans la table de base de données. Si le numéro de version des données soumises est supérieur au numéro de version actuelle de la table de base de données, il sera. mis à jour. Sinon, elles sont considérées comme des données expirées et doivent être relues. Récupérez l'objet et apportez des modifications.

2. Utilisez l'horodatage pour implémenter

Il s'agit également d'ajouter un champ à la table qui nécessite un contrôle de verrouillage optimiste. Le type de champ utilise l'horodatage, qui est similaire à. la version ci-dessus. , également lors de la soumission de la mise à jour, l'horodatage des données dans la base de données actuelle est vérifié et comparé à l'horodatage obtenu avant la mise à jour. S'ils sont cohérents, c'est OK, sinon c'est un conflit de version.

Scénarios applicables de verrouillage pessimiste et de verrouillage optimiste :

Si le degré de concurrence n'est pas important, vous pouvez utiliser le verrouillage pessimiste pour résoudre le problème de concurrence ; La quantité de concurrence dans le système est très importante, le pessimisme entraînera de très gros problèmes de performances, nous devons donc choisir la méthode de verrouillage optimiste. La plupart des applications devraient désormais être verrouillées avec optimisme.

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!