Comprendre le problème de verrouillage de mysql insert into ... select

La colonne

tutoriel mysql présente le problème de verrouillage de l'insertion mysql dans...select.

Citation :

J'ai récemment rencontré un problème de blocage de base de données. Voici un enregistrement du processus de résolution.

Le problème se pose :

Il y a plusieurs événements dans MySQL dans le système, qui seront exécutés toutes les quelques minutes pour des fonctions telles que les données statistiques, puis cet événement ajoutera des données à une table Écrivez les données à l'intérieur. Contenu général : remplacer par a à partir de sélectionner les champs obligatoires de b ; La structure générale est la suivante. Le champ requis par select from b est l'abréviation ici. Il est en fait très compliqué et implique de nombreuses opérations de jointure de table. Ensuite, cet événement est exécuté toutes les minutes, lorsque la quantité de données est importante Il se peut que cela ne soit pas terminé en une minute. Ensuite, nous aurons diverses autres opérations d’insertion et de mise à jour à opérer sur la table b. À l’heure actuelle, vous constaterez qu’il y a souvent des erreurs de blocage et d’expiration du délai de verrouillage d’attente dans les journaux backend. Le test final a révélé que la désactivation de l'événement éliminait le problème, et il a été essentiellement confirmé que le problème venait de cet événement.

Analyse du problème :

En fait, la chose la plus longue est de découvrir qu'il s'agit d'un problème événementiel. Il ne faut pas beaucoup de temps pour interroger les données pour résoudre le problème. 1. Tout d'abord, sur la base des informations d'erreur dans le journal principal, localisez quelle table a provoqué le blocage et quelle table a attendu l'expiration du délai de verrouillage
2. Ensuite, sur la base de ces noms de table et du SQL imprimé, découvrez où cela peut être. Il a été approximativement confirmé que le problème était dû au SQL dans l'événement
3. Vérifiez à nouveau notre idée, et après avoir désactivé l'événement, nous avons constaté qu'il n'y avait aucun problème de verrouillage dans le journal
. 4. Vérifiez les déclarations de l'événement et constatez qu'il a probablement été remplacé par a from select require materials from b;

La raison principale ici est qu'il n'est pas clair dans quelles circonstances mysql sera verrouillé en théorie. , l'opération de sélection ne prendra qu'un verrou partagé, pour l'insertion et la mise à jour de la table b, etc. L'opération consiste à verrouiller le verrou exclusif. Ces deux-là sont compatibles, on lit et on écrit, et il n'y a pas de conflit. Mais à en juger par le phénomène de timeout, il semble que les champs requis par select from b verrouillent également la table b. Ainsi, les insertions et les mises à jour attendent le verrou.

Enfin, j'ai trouvé des informations intéressantes et une adresse de lien dans Stack Overflow. Il est dit ici qu'il doit être réglé au niveau read-commit. Ensuite, un paramètre de configuration mysql est également introduit : innodb_locks_unsafe_for_binlog.

Nous avons donc suivi cette information et l'avons vérifiée sur le site officiel et avons trouvé ce paragraphe :

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.复制代码

Cela signifie que pour INSERT INTO T SELECT ... FROM S WHERE ... cette situation d'abord Surtout, on saura quel verrouillage d'enregistrement (verrouillage au niveau de la ligne) se trouve sur la table T, et il n'a pas de verrouillage d'espacement. Pour la table S, il existe deux situations dans lesquelles aucun verrouillage ne se produira :
1. Si le niveau d'isolement des transactions est READ COMMITTED
2 Ou innodb_locks_unsafe_for_binlog est activé et le niveau d'isolement des transactions n'est pas SERIALIZABLE

. Sinon, InnoDB définit la clé suivante partagée sur la ligne de S. Si vous n'êtes pas sûr de la clé suivante, vous pouvez lire cette introduction et l'adresse du lien sur le site officiel.

Par conséquent, la façon dont nous voulons résoudre le délai d'attente est relativement claire, à savoir empêcher le verrouillage de la table S. Cependant, pour éviter d'être verrouillé, nous pouvons utiliser les deux méthodes mentionnées sur le site officiel. . Les deux sont possibles, mais selon l'introduction du paramètre innodb_locks_unsafe_for_binlog, il est préférable d'utiliser la méthode 1 et de définir le niveau d'isolement des transactions sur read-commit.

Les raisons sont les suivantes :
1. Le paramètre innodb_locks_unsafe_for_binlog est statique Vous devez ajouter une ligne innodb_locks_unsafe_for_binlog = 1 à my.cnf puis redémarrer la base de données pour prendre effet. Entrez la commande dans mysql :

show variables like "%innodb_locks_unsafe_for_binlog%"复制代码

S'il s'avère activé, cela signifie qu'il a été ouvert avec succès.
2. Le niveau d'isolement d'une transaction est relativement fin et peut être défini pour une certaine session. Différentes sessions peuvent utiliser différents niveaux d'isolement, et ce paramètre est dynamique et peut être modifié directement sur la ligne de commande mysql.
3. L'introduction des paramètres de mysql5.7 indique que le paramètre innodb_locks_unsafe_for_binlog sera abandonné dans les versions ultérieures de mysql. C'est vrai. J'ai vérifié l'explication détaillée des paramètres de mysql8.0 et j'ai constaté que ce paramètre n'existe plus.

Il est donc recommandé d'utiliser le niveau d'isolement des transactions pour le contrôle.

Plus de recommandations d'apprentissage gratuites connexes : tutoriel MySQL(vidéo)

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!