Comment éviter mysql 'Impasse trouvé lorsque vous essayez d'obtenir le verrou; essayez de redémarrer l'opération"

J'ai une table innoDB qui enregistre des utilisateurs en ligne. Il est mis à jour à chaque actualisation de la page par un utilisateur de garder une trace des pages qui ils sont et leur dernière date d'accès au site. J'ai alors d'un cron qui s'exécute toutes les 15 minutes pour SUPPRIMER les anciens enregistrements.

J'ai eu un "Blocage trouvé lorsque vous essayez d'obtenir le verrou; essayez de redémarrer l'opération" pendant environ 5 minutes la nuit dernière et il apparaît lors de l'exécution d'Insertions dans ce tableau. Quelqu'un peut-il suggérer comment éviter cette erreur?

=== EDIT ===

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 chiffres pour vous connecter certaines stats (c'est à dire: les membres en ligne, les visiteurs en ligne).

InformationsquelleAutor David | 2010-02-25
  1. 255

    Un truc facile qui peut vous aider avec la plupart des blocages est le tri des opérations dans un ordre spécifique.

    Vous obtenez un blocage lors de deux opérations d'essayer de verrouiller les deux serrures à l'opposé des ordres, c'est à dire:

    • connexion 1: serrures à clé(1), serrures à clé(2);
    • connexion 2: serrures à clé(2), serrures à clé(1);

    Si les deux fonctionnent en même temps, la connexion 1 touche de verrouillage(1), connexion 2 sera la touche de verrouillage(2) et chaque connexion attendre que l'autre relâchez la touche -> blocage.

    Maintenant, si vous avez changé vos requêtes telles que les connexions verrouiller les touches du 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 de faire l'impasse.

    C'est donc ce que je propose:

    1. Assurez-vous que vous n'avez pas d'autres requêtes de verrouillage de l'accès plus d'une touche à la fois, sauf pour l'instruction delete. si vous n' (et je soupçonne que vous faites), afin de leur OÙ dans (k1,k2,..kn) dans l'ordre croissant.

    2. Fixer votre instruction delete pour travailler dans l'ordre croissant:

    Changement 

    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 de mysql suggèrent que dans le cas d'une impasse, le client doit recommencer automatiquement. vous pouvez ajouter cette logique à votre code client. (Disons, 3 tentatives sur cette erreur avant d'abandonner).

    • Si j'ai de la Transaction(autocommit=false), blocage de l'exception levée. Est-il juste assez pour recommencer la même déclaration.executeUpdate() ou sur la totalité de la transaction est maintenant gimped et devrait être rollbacked+réexécuter tout ce qui était en cours d'exécution en elle?
    • si vous avez les transactions sont activées, c'est tout ou rien. si vous avez eu une exception d'aucune sorte, il est garanti que l'ensemble de la transaction n'a eu aucun effet. dans ce cas, vous voulez redémarrer le tout.
    • Un à supprimer, après une sélection sur une immense table est très inférieure à une simple suppression
    • Merci, merci beaucoup, mec. Le tri des états extrémité est fixée mes morts de verrouillage de questions.
    • De ce que je sais, en MySQL, vous ne pouvez pas sélectionner dans une sous-requête de la même table dans laquelle vous faites la mise à JOUR. dev.mysql.com/doc/refman/5.7/en/update.html
    • N'était pas en mesure d'utiliser cette réponse bc de ce que @artaxerxe mentionné, mais dasunhegoda.com/... il fixe.
    • Lors de l'exécution d'une requête de mise à jour vous pouvez inner join la sous-requête avec la table using(id), alors vous êtes à la sélection par la sous-requête, mais la mise à jour de la table UPDATE mytable t INNER JOIN (SELECT id FROM mytable WHERE datetime <= NOW() ORDER BY id) t2 USING(id) SET t.datetime = NOW(). Je pense que la même chose peut être appliquée aux suppressions DELETE t FROM mytable t INNER JOIN (SELECT id FROM mytable WHERE datetime <= NOW() ORDER BY id) t2 USING(id)
    • comment trier les éléments de la requête de suppression fixe impasse?

    InformationsquelleAutor Omry Yadan
  2. 65

    Blocage se produire lorsque deux transactions attendre les uns les autres pour acquérir un verrou. Exemple:

    • Tx 1: verrou A, puis B
    • Tx 2: serrure à B, alors A

    Il y a de nombreuses questions et des réponses sur les blocages. Chaque fois que vous insert/update/ou supprimer une ligne, un verrou est acquis. Pour éviter un blocage, vous devez assurez-vous que les transactions simultanées ne pas mettre à jour rangée dans un ordre qui pourrait entraîner un blocage. Généralement parlant, essayer d'acquérir de verrouillage toujours dans le même ordre même dans les différentes transactions (par exemple, toujours à la table d'Un premier, puis le tableau B).

    Une autre raison de l'impasse 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 a besoin de vérifier les contraintes relationnelles, qui est, assurez-vous que les relations sont compatibles. Pour ce faire, la base de données doit permettre de vérifier les clés étrangères des tables liées. Il pourrait résultat dans d'autres verrouiller les acquis de la ligne qui est modifié. Être sûr de toujours avoir des index sur les clés étrangères (et bien sûr les clés primaires), sinon il pourrait en résulter un verrou de table au lieu d'un verrou de ligne. Si le verrou de table de passe, le verrouillage est plus élevé et la probabilité de blocage augmente.

    • Alors peut-être mon problème est que l'Utilisateur a mis à jour la page et donc le déclenchement d'une mise à JOUR d'un enregistrement en même temps le cron essaie de SUPPRIMER le dossier. Cependant, je suis de l'erreur sur les plaquettes, de sorte que le cron ne serait pas SUPPRIMER les enregistrements qui ont simplement été créé. Alors, comment peut un blocage arriver sur un dossier qui n'est pas encore inséré?
    • Pouvez-vous fournir un peu plus d'informations sur la / les table(s), ainsi que les transactions exactement faire?
    • J'ai édité le post initial avec les informations de requête.
    • Je ne vois pas comment un blocage peut se produire si il y a une seule instruction par la transaction. Pas d'autres opérations sur d'autres tables? Aucune des clés étrangères ou à des contraintes uniques? Pas d'effacer en cascade les contraintes?
    • nope, rien d'autre de spécial...je suppose que c'est vers le bas à la nature de l'utilisation de la table. une ligne est insérée/mis à jour à chaque actualisation de la page à partir d'un visiteur. Autour de+ de 1000 visiteurs sont à tout moment.
    InformationsquelleAutor ewernli
  3. 9

    Il est probable que la suppression de la déclaration d'affecter une fraction importante du total des lignes dans la table. A terme, cela pourrait conduire à un verrou de table acquis lors de la suppression. Holding pour un verrou (dans ce cas, des lignes ou des verrous de page) et l'acquisition de plus de verrous est toujours un risque de blocage. Cependant, je ne peux pas expliquer pourquoi l'instruction insert conduit à une escalade de verrous - il pourrait avoir à faire avec la page de fractionnement/ajout d', mais quelqu'un en sachant MySQL mieux devront remplir là.

    Pour commencer, il peut être la peine d'essayer explicitement acquérir un verrou de table tout de suite pour l'instruction delete. Voir LOCK TABLES et Table de problèmes de verrouillage.

    InformationsquelleAutor Anders Abel
  4. 6

    Vous pourriez essayer de les avoir que delete travail fonctionnent en insérant d'abord la clé de chaque ligne à être supprimés dans une table temporaire, comme ce pseudo-code

    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);

    Rupture comme cela est moins efficace, mais il évite la nécessité de tenir une plage de clés de verrouillage lors de la delete.

    Également, de modifier votre select requêtes pour ajouter un where clause excluant les lignes âgés de plus de 900 secondes. Cela permet d'éviter la dépendance à la tâche cron et vous permet de vous remettre à courir de moins en moins souvent.

    Théorie sur les blocages: je n'ai pas beaucoup d'expérience dans MySQL mais voilà... Le delete va tenir une plage de clés de verrouillage pour le type datetime, afin d'éviter les lignes correspondant à sa where clause d'être ajouté dans le milieu de l'opération, et comme il trouve les lignes à supprimer, il va tenter d'acquérir un verrou sur chaque page qu'il est en train de modifier. Le insert va acquérir un verrou sur la page, c'est de l'insérer dans, et puis tenter d'acquérir la clé de verrouillage. Normalement, le insert va attendre patiemment que la clé de verrouillage pour ouvrir, mais cela fera l'impasse si le delete tente de verrouiller la même page le insert est à l'aide parce que ledelete besoins que la page de verrouillage et le insert besoins que la clé de verrouillage. Cela ne semble pas juste pour les insertions cependant, le delete et insert utilisez datetime des plages qui ne se chevauchent pas, donc peut-être quelque chose d'autre se passe.

    http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

    InformationsquelleAutor Brian Sandlin
  5. 3

    Pour les programmeurs Java à l'aide de Printemps, j'ai évité ce problème à l'aide d'un AOP aspect automatiquement les tentatives de transactions en transitoire blocages.

    Voir @RetryTransaction Javadoc pour plus d'info.

    • pouvez-vous mettre à jour votre lien? c'est mort
    • Mise à jour - merci!
    InformationsquelleAutor Archie
  6. 1

    Au cas où quelqu'un est toujours aux prises avec ce problème:

    J'ai fait face à problème similaire où les 2 demandes ont été frapper le serveur en même temps. Il n'y a pas de situation comme ci-dessous:

    T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

    Donc, j'ai été intrigué pourquoi impasse qui se passe.

    Ensuite, j'ai trouvé qu'il était parent de l'enfant relation entre les 2 tables en raison de clé étrangère. Lorsque j'ai été l'insertion d'un enregistrement dans la table enfant, l'opération a été l'acquisition d'un verrou sur la table parent de la ligne. Immédiatement après que j'ai essayé de mettre à jour la ligne parent qui a été le déclenchement de l'élévation de verrou EXCLUSIF. La 2ème transaction simultanée tenait déjà un verrou PARTAGÉ, il était à l'origine du blocage.

    Se référer à: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

    • Dans mon cas aussi, il semble que le problème a été une clé étrangère de la relation. Thanks1
    InformationsquelleAutor chatsap
  7. 0

    J'ai une méthode, à l'intérieur de laquelle sont enveloppés dans un MySqlTransaction.

    L'impasse de la question ont montré pour moi quand j'ai couru la même méthode, en parallèle avec lui-même.

    Il n'y a pas un problème dans l'exécution d'une instance unique de la méthode.

    Quand j'ai enlevé MySqlTransaction, j'ai été en mesure d'exécuter la méthode en parallèle avec lui-même sans aucun problème.

    Juste partager mon expérience, je ne dis pas n'importe quoi.

    InformationsquelleAutor CINCHAPPS