Comment éviter le message MySQL "Deadlock found when trying to get lock ; try restarting transaction" (blocage lors de la tentative d'obtenir un verrou ; essayez de relancer la transaction)

Question

J'ai une table innoDB qui enregistre les utilisateurs en ligne. Elle est mise à jour à chaque rafraîchissement de page par un utilisateur pour garder la trace des pages sur lesquelles il se trouve et de sa dernière date d'accès au site. J'ai ensuite un cron qui s'exécute toutes les 15 minutes pour SUPPRIMER les anciens enregistrements.

La nuit dernière, j'ai eu un message d'erreur "Deadlock found when trying to get lock ; try restarting transaction" pendant environ 5 minutes. Il semble que ce soit lors de l'exécution d'INSERTS dans cette table. Quelqu'un peut-il me suggérer comment éviter cette erreur ?

\=== EDIT ===

Voici les requêtes en cours d'exécution :

Première visite sur le site :

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3

A chaque rafraîchissement de 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 effectue ensuite quelques comptages pour enregistrer certaines statistiques (par exemple : membres en ligne, visiteurs en ligne).

Answer 1

Une astuce simple qui peut aider à résoudre la plupart des blocages consiste à 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 : verrouille la clé(1), verrouille la clé(2) ;
• connexion 2 : verrouille la clé (2), verrouille la clé (1) ;

Si les deux fonctionnent 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é -> deadlock.

Maintenant, si vous modifiez vos requêtes de façon à ce que les connexions verrouillent les clés dans le même ordre, c'est-à-dire :

• connexion 1 : verrouille la clé(1), verrouille la clé(2) ;
• connexion 2 : clé de verrouillage( 1 ), la clé de verrouillage ( 2 ) ;

il sera impossible d'obtenir une impasse.

Voici donc ce que je propose :

1. Vérifiez que vous n'avez pas d'autres requêtes qui verrouillent l'accès à plus d'une clé à la fois, à l'exception de l'instruction delete. Si c'est le cas (et je soupçonne que c'est le cas), ordonnez leur WHERE dans (k1,k2,..kn) par ordre croissant.

2. Corrigez votre déclaration de suppression pour qu'elle fonctionne par ordre croissant :

Changement

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

A

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ère qu'en cas de blocage, le client doit réessayer automatiquement. (Disons, 3 tentatives sur cette erreur particulière avant d'abandonner).

Answer 2

Un blocage se produit lorsque deux transactions attendent l'une de l'autre pour acquérir un verrou. Exemple :

• Tx 1 : verrouiller A, puis B
• Tx 2 : verrouillage B, puis A

Il existe de nombreuses questions et réponses sur les blocages. Chaque fois que vous insérez/mettez à jour/supprimez une ligne, un verrou est acquis. Pour éviter les blocages, vous devez alors vous assurer que les transactions concurrentes ne mettent pas à jour les lignes dans un ordre qui pourrait entraîner un blocage. De manière générale, essayer d'acquérir le verrou toujours dans le même ordre même dans des transactions différentes (par exemple, toujours la table A en premier, puis la table B).

Une autre raison pour laquelle la base de données est bloquée peut être indices manquants . Lorsqu'une ligne est insérée, mise à jour ou supprimée, 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 liées. Il s'agit de pourrait entraîne l'acquisition d'un autre verrou que celui de la ligne modifiée. Assurez-vous donc de toujours avoir un index sur les clés étrangères (et bien sûr sur les clés primaires), sinon cela pourrait entraîner un serrure de table au lieu d'un verrouillage des rangs . Si un verrouillage de table se produit, la contention de verrouillage est plus élevée et la probabilité d'un blocage augmente.

Answer 3

Il est probable que l'instruction delete affectera une grande partie du nombre total de lignes de la table. Cela peut éventuellement conduire à l'acquisition d'un verrou de table lors de la suppression. S'accrocher à un verrou (dans ce cas, un verrou de ligne ou de page) et acquérir d'autres verrous constitue toujours un risque de blocage. Cependant, je ne peux pas expliquer pourquoi l'instruction d'insertion conduit à une escalade de verrous - cela pourrait avoir un rapport avec le fractionnement/ajout de pages, mais quelqu'un qui connaît mieux MySQL devra le faire.

Pour commencer, il peut être intéressant d'essayer d'acquérir explicitement un verrou de table immédiatement pour l'instruction delete. Voir VERROUILLER LES TABLES y Problèmes de verrouillage des tables .

Answer 4

Vous pourriez essayer d'avoir ce delete fonctionne en insérant d'abord la clé de chaque ligne à supprimer 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);

Ce découpage est moins efficace, mais il permet d'éviter d'avoir à maintenir un verrouillage de la gamme des clés pendant la période de transition. delete .

En outre, modifiez votre select des requêtes pour ajouter un where excluant les rangs plus anciens que 900 secondes. Cela évite de dépendre de la tâche cron et vous permet de la reprogrammer pour qu'elle soit exécutée moins souvent.

Théorie sur les blocages : Je n'ai pas beaucoup d'expérience en MySQL, mais voilà... Le site delete va détenir un verrou de plage de clés pour datetime, afin d'éviter que les lignes correspondant à sa propriété where d'être ajoutée au milieu de la transaction, et au fur et à mesure qu'il trouve des lignes à supprimer, il tente d'acquérir un verrou sur chaque page qu'il modifie. Le site insert va acquérir un verrou sur la page dans laquelle il est inséré, et puis tenter d'acquérir la serrure à clé. Normalement, le insert attendra patiemment que la serrure s'ouvre, mais cela entraînera un blocage si le delete essaie de verrouiller la même page que le insert est utilisé car le delete a besoin de ce verrouillage de page et le insert a besoin de cette serrure. Cela ne semble pas approprié pour les inserts cependant, les delete y insert utilisent des plages de dates qui ne se chevauchent pas, il y a peut-être autre chose qui se passe.

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

Answer 5

Pour les programmeurs Java utilisant Spring, j'ai évité ce problème en utilisant un aspect AOP qui relance automatiquement les transactions qui se trouvent dans des impasses transitoires.

Ver @RetryTransaction Javadoc pour plus d'informations.