Travail autour d'erreur MySQL “Blocage trouvé lorsque vous essayez d'obtenir le verrou; essayez de redémarrer transaction”
J'ai une table MySQL avec environ 5 000 000 de lignes qui sont constamment mis à jour dans de petits moyens en parallèle Perl processus de connexion via DBI. La table a environ 10 colonnes et plusieurs index.
Un assez commune de l'opération donne lieu à l'erreur suivante parfois:
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.
L'instruction SQL qui déclenche l'erreur est quelque chose comme ceci:
UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47
L'erreur est déclenchée seulement parfois. J'avais estimation de 1% des appels ou moins. Cependant, il n'est jamais arrivé avec une petite table et il est devenu plus commun que la base de données a augmenté.
Noter que je suis en utilisant le a_lock champ dans file_table pour s'assurer que les quatre quasi-identiques processus, je suis en cours d'exécution n'essayez pas de travailler sur la même ligne. La limite est conçu pour briser leur travail en petits morceaux.
Je n'ai pas fait beaucoup de tuning sur MySQL ou DBD::mysql. MySQL est un standard Solaris déploiement, et la connexion de base de données est défini comme suit:
my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
my $dbh = DBI->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;
J'ai vu en ligne que plusieurs autres personnes ont signalé des erreurs similaires et que cela peut être un véritable situation de blocage.
J'ai deux questions:
-
Ce qu'est exactement à ma situation est à l'origine de l'erreur ci-dessus?
-
Est-il un moyen simple de le contourner ou de diminuer sa fréquence? Par exemple, comment dois-je faire à propos de "redémarrage de la transaction à la Db.pm de la ligne 276"?
Merci d'avance.
- Pour voir ce que la table est verrouillée et certaines des données qui en est la cause, exécuter
SHOW ENGINE INNODB STATUS
.
Vous devez vous connecter pour publier un commentaire.
Si vous utilisez InnoDB ou de toute ligne de niveau transactionnel SGBDR, alors il est possible que tout écrire transaction peut provoquer un blocage, même parfaitement dans les situations normales. Des tables plus grandes, plus écrit, et à long transaction de blocs d'augmenter la probabilité de blocages se produisent. Dans votre situation, c'est probablement une combinaison de ces.
La seule façon de véritablement gérer les blocages est d'écrire votre code d'attendre d'eux. Ce n'est généralement pas très difficile si votre base de données de code est bien écrit. Souvent, vous pouvez simplement mettre un
try/catch
autour de l'exécution de la requête de la logique et de chercher un blocage lorsque des erreurs se produisent. Si vous attrapez un, la chose normale à faire est de simplement tenter d'exécuter la requête ayant échoué à nouveau.Je vous recommande vivement de lire cette page dans le manuel MySQL. Il y a une liste de choses à faire pour aider à surmonter les blocages et réduire leur fréquence.
InnoDB or any row-level transactional RDBMS
n'ont pas ces problèmes?La réponse est correcte, mais la documentation perl sur la façon de gérer les blocages est un peu clairsemée et peut-être confusion avec PrintError, RaiseError et HandleError options. Il semble que, plutôt que d'aller avec HandleError, l'utilisation de l'Impression et de créer et d'utiliser ensuite Essayer quelque chose comme:Minuscule pour envelopper votre code et vérifier les erreurs. Le code ci-dessous donne un exemple où la db code est à l'intérieur d'une boucle while qui va ré-exécuter une erreur de l'instruction sql toutes les 3 secondes. Le bloc catch obtient $_ qui est l'err message. Je passe ce à une fonction de gestionnaire "dbi_err_handler" qui vérifie $_ à l'encontre d'une foule d'erreurs et retourne 1 si le code doit se poursuivre (brisant ainsi la boucle) ou 0 si c'est un blocage et doit être retenté...
dbi_err_handler doit avoir au moins les éléments suivants:
Vous devriez inclure d'autres erreurs que vous souhaitez traiter et de régler $retval selon que vous souhaitez ré-exécuter ou de continuer..
Espère que cela aide quelqu'un -
Notez que si vous utilisez
SELECT FOR UPDATE
pour effectuer un contrôle d'unicité avant de l'insérer, vous obtiendrez un blocage pour chaque condition de course, sauf si vous activez lainnodb_locks_unsafe_for_binlog
option. Un blocage sans méthode de vérification de l'unicité est aveugle d'insérer une ligne dans une table avec un index unique à l'aide deINSERT IGNORE
, puis de vérifier le nombre de lignes affectées.ajouter en dessous de la ligne de
my.cnf
fichierinnodb_locks_unsafe_for_binlog = 1
#
1
0 - DÉSACTIVER
#
innodb_locks_unsafe_for_binlog
peut provoquer de faux problèmes parce que d'autres sessions peuvent insérer de nouvelles lignes dans les lacunes lors de l'écart de verrouillage est désactivé.L'idée de renvoyer la requête en cas de Blocage exception est bon, mais il peut être extrêmement lente, depuis requête mysql va garder en attente pour les serrures à être libéré. Et en cas de blocage de mysql est d'essayer de trouver si il y a un blocage, et même après avoir découvert qu'il y a un blocage, il attend un certain temps avant de coups de pied sur un fil dans le but de sortir de l'impasse de la situation.
Ce que j'ai fait quand j'ai fait face à cette situation est à mettre en œuvre de verrouillage dans votre propre code, puisque c'est le mécanisme de verrouillage de mysql est un échec en raison d'un bug. Donc, j'ai mis en place mon propre verrouillage de niveau ligne dans mon code java: