Comment éviter mysql 'blocage trouvé en essayant d'obtenir le verrouillage; essayez de redémarrer la transaction'
J'ai une table innoDB qui enregistre les utilisateurs en ligne. Il est mis à jour à chaque actualisation de la page par un utilisateur pour garder une trace des pages sur lesquelles ils se trouvent et de leur dernière date d'accès au site. J'ai alors un cron qui s'exécute toutes les 15 minutes pour supprimer les anciens enregistrements.
J'ai trouvé un 'blocage trouvé en essayant d'obtenir un verrouillage; essayez de redémarrer la transaction' pendant environ 5 minutes la nuit dernière et cela semble être le cas lors de L'exécution D'insertions dans cette table. Quelqu'un peut-il suggérer comment éviter cette erreur?
=== Modifier = = =
Voici les requêtes en cours d'exécution:
Première Visite du site:
INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
Sur chaque actualisation de la page:
UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888
Cron toutes les 15 minutes:
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
Il fait ensuite quelques comptes pour enregistrer certaines statistiques (c'est-à-dire: membres en ligne, visiteurs en ligne).
6 réponses
Une astuce facile qui peut aider avec la plupart des blocages est de trier les opérations dans un ordre spécifique.
Vous obtenez un blocage lorsque deux transactions tentent de verrouiller deux verrous à des ordres opposés, c'est-à-dire:
- Connexion 1: serrures clé(1), serrures clé (2);
- connexion 2: serrures clé(2), serrures clé (1);
Si les deux s'exécutent en même temps, la connexion 1 verrouillera la clé (1), la connexion 2 verrouillera la clé(2) et chaque connexion attendra que l'autre libère la clé -> impasse.
Maintenant, si vous avez changé vos requêtes de sorte que les connexions verrouillent les clés dans le même ordre, c'est-à-dire:
- Connexion 1: serrures clé(1), serrures clé (2);
- connexion 2: serrures à clé(1), serrures à clé(2);
Il sera impossible d'obtenir une impasse.
Voici donc ce que je suggère:
Assurez-vous qu'aucune autre requête ne verrouille l'accès à plus d'une clé à la fois, à l'exception de l'instruction delete. si vous faites (et je soupçonne que vous faites), ordonnez leur où dans (k1, k2, .. kn) dans l'ordre croissant.
Fixer votre instruction delete pour travailler dans l'ordre croissant:
Modifier
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
À
DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
Une autre chose à garder à l'esprit est que la documentation mysql suggère qu'en cas de blocage, le client devrait réessayer automatiquement. vous pouvez ajouter cette logique à votre code client. (Disons, 3 tentatives sur cette erreur avant d'abandonner).
L'impasse se produit lorsque deux transactions attendent l'une sur l'autre pour acquérir un verrou. Exemple:
- Tx 1: verrouiller A, Puis B
- Tx 2: verrouiller B, puis a
Il existe de nombreuses questions et réponses sur les blocages. Chaque fois que vous insérez / mettez à jour/ou supprimez une ligne, un verrou est acquis. Pour éviter un blocage, vous devez ensuite vous assurer que les transactions simultanées ne mettent pas à jour la ligne dans un ordre qui pourrait entraîner un blocage. D'une manière générale, essayez d'acquérir lock toujours dans le même order même dans une transaction différente (par exemple, toujours la table a en premier, puis la table B).
Une autre raison de blocage dans la base de données peut être index manquants . Lorsqu'une ligne est insérée / mise à jour / suppression, la base de données doit vérifier les contraintes relationnelles, c'est-à-dire s'assurer que les relations sont cohérentes. Pour ce faire, la base de données doit vérifier les clés étrangères dans les tables associées. Cela peut entraîner l'acquisition d'un autre verrou que la ligne modifiée. Assurez-vous ensuite à toujours avoir un index sur les clés étrangères (et bien sûr les clés primaires), sinon cela pourrait entraîner un verrou de table au lieu d'un verrou de ligne. Si le verrou de table se produit, la contention de verrou est plus élevée et la probabilité de blocage augmente.
Il est probable que l'instruction delete affectera une grande fraction du total des lignes de la table. Finalement, cela pourrait conduire à l'acquisition d'un verrou de table lors de la suppression. S'accrocher à un verrou (dans ce cas, verrous de ligne ou de page) et acquérir plus de verrous est toujours un risque de blocage. Cependant, je ne peux pas expliquer pourquoi l'instruction insert conduit à une escalade de verrou - cela pourrait avoir à voir avec le fractionnement/l'ajout de pages, mais quelqu'un connaissant mieux MySQL devra remplir là-bas.
Pour commencer il peut être utile d'essayer d'acquérir explicitement un verrou de table tout de suite pour l'instruction delete. Voir verrouiller les TABLES et Problèmes de verrouillage de Table .
Vous pouvez essayer de faire fonctionner ce travail delete en insérant d'abord la clé de chaque ligne à supprimer dans une table temporaire comme ce pseudocode
create temporary table deletetemp (userid int);
insert into deletetemp (userid)
select userid from onlineusers where datetime <= now - interval 900 second;
delete from onlineusers where userid in (select userid from deletetemp);
Le briser comme ceci est moins efficace mais il évite la nécessité de tenir un verrou de plage de touches pendant le delete.
Modifiez également vos requêtes select pour ajouter une clause where excluant les lignes de plus de 900 secondes. Cela évite la dépendance au travail cron et vous permet de le rééchelonner pour l'exécuter moins souvent.
Théorie A propos des blocages: Je n'ai pas beaucoup D'expérience dans MySQL mais c'est parti... Le delete va contenir un verrou de plage de clés pour datetime, pour empêcher les lignes correspondant à sa clause where d'être ajoutées au milieu de la transaction, et comme il trouve des lignes à supprimer, il tentera d'acquérir un verrou sur chaque page qu'il modifie. Le insert va acquérir un verrou sur la page dans laquelle il est inséré, et puis tentent d'acquérir le verrou à clé. Normalement, le insert attendra patiemment ce verrou de clé s'ouvre mais cela bloquera si le delete essaie de verrouiller la même page que le insert utilise parce que ledelete a besoin de ce verrou de page et le insert a besoin de ce verrou de clé. Cela ne semble pas correct pour les insertions, les delete et insert utilisent des plages datetime qui ne se chevauchent pas, alors peut-être que quelque chose d'autre se passe.
Http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html
Pour les programmeurs Java utilisant Spring, j'ai évité ce problème en utilisant un aspect AOP qui réessaie automatiquement les transactions qui se heurtent à des blocages transitoires.
Voir @ RetryTransaction Javadoc pour plus d'informations.
J'ai une méthode, dont les internes sont enveloppés dans un MySqlTransaction.
Le problème de blocage est apparu pour moi quand j'ai couru la même méthode en parallèle avec elle-même.
Il n'y a pas eu de problème lors de l'exécution d'une seule instance de la méthode.
Quand J'ai supprimé MySqlTransaction, j'ai pu exécuter la méthode en parallèle avec elle-même sans problème.
Juste partager mon expérience, je ne préconise rien.