Quelles sont les causes et les solutions aux blocages MySQL

Un blocage se produit lorsque plusieurs transactions détiennent et demandent des verrous sur la même ressource en même temps, ce qui entraîne des dépendances circulaires. La solution est la suivante : 1. Utilisez un niveau d'isolement inférieur. 2. Accédez à vos ressources dans un ordre fixe. rows; 3. Ajoutez des index soigneusement sélectionnés aux tables; 4. Utilisez moins de verrous.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Une impasse se produit lorsque plusieurs transactions détiennent et demandent des verrous sur la même ressource en même temps, ce qui entraîne des dépendances circulaires. Un blocage se produit lorsqu'une transaction tente de verrouiller les ressources dans un ordre différent. Prenons comme exemple les deux transactions de la table StockPrice :

Transaction 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

Transaction #2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET;
COMMIT;

Si vous n'avez pas de chance, chaque transaction peut terminer l'exécution de la première instruction et verrouiller la ressource dans le processus. Chaque transaction a ensuite tenté d'exécuter la deuxième ligne d'instructions, pour constater qu'elle était verrouillée. Les deux transactions attendront indéfiniment que l’autre se termine, à moins qu’il n’y ait d’autres raisons pour sortir de l’impasse.

Afin de résoudre ce problème, la base de données implémente divers mécanismes de détection de blocage et de délai d'attente. Les moteurs de stockage complexes comme InnoDB indiqueront les dépendances circulaires et renverront immédiatement les erreurs. Sinon, un blocage entraînera une requête très lente. Une autre mauvaise pratique consiste à attendre un temps mort, puis à abandonner. La manière actuelle dont InnoDB gère les blocages consiste à annuler la transaction détenant le verrou au niveau de la ligne le moins exclusif. (Presque l'indicateur de référence le plus simple pour le rollback)

Le comportement des verrous est déterminé par le moteur de stockage dans l'ordre. Par conséquent, certains moteurs de stockage peuvent se bloquer lors d’une séquence d’opérations spécifique, d’autres non. Il existe deux types de blocages : certains sont inévitables en raison de conflits de données réels, et d'autres sont provoqués par le fonctionnement du moteur de stockage.

Seule l’annulation partielle ou complète de l’une des transactions peut sortir de l’impasse. Les blocages sont un fait objectif dans les systèmes de transaction, et votre conception doit prendre en compte la gestion des blocages. Certains systèmes d'entreprise peuvent réessayer les transactions depuis le début.

Comment gérer les blocages
Les blocages sont un problème typique dans les bases de données transactionnelles, mais à moins qu'ils ne surviennent si fréquemment que vous ne puissiez pas exécuter de transaction du tout, ils ne sont généralement pas dangereux. Normalement, vous devez écrire vos applications de manière à ce qu'elles soient toujours prêtes à réémettre une transaction si elle est annulée en raison d'un blocage.

InnoDB utilise le verrouillage automatique au niveau des lignes. Même dans le cas d'une transaction qui insère ou supprime uniquement une seule ligne, vous pouvez rencontrer un blocage. En effet, ces opérations ne sont pas vraiment « minuscules » et elles définissent automatiquement un verrou sur (éventuellement plusieurs) enregistrements d'index de la ligne insérée ou supprimée.

Vous pouvez utiliser les techniques suivantes pour gérer les blocages et réduire la probabilité qu'ils se produisent :

Utilisez SHOW INNODB STATUS pour déterminer la cause du dernier blocage. Cela peut vous aider à affiner votre application pour éviter les blocages.

Soyez toujours prêt à réémettre une transaction si elle échoue en raison d'une impasse. L’impasse n’est pas dangereuse, réessayez.

Soumettez souvent vos transactions. Les petites questions sont moins sujettes aux conflits.

Si vous utilisez des lectures verrouillées (SELECT ... FOR UPDATE ou ... LOCK IN SHARE MODE), essayez d'utiliser un niveau d'isolement inférieur, tel que READ COMMITTED.

Accédez à vos tableaux et lignes dans un ordre fixe. Ensuite, les transactions forment des requêtes bien définies et il n'y a pas de blocages.

Ajoutez des index soigneusement sélectionnés à vos tables. Votre requête devra alors analyser moins d'enregistrements d'index et donc définir moins de verrous. Utilisez EXPLAIN SELECT pour déterminer quel index MySQL considère comme le plus approprié pour votre requête.

Utilisez moins de cadenas. Si vous pouvez accepter d'autoriser un SELECT à renvoyer des données d'un instantané plus ancien, n'y ajoutez pas de clause FOR UPDATE ou LOCK IN SHARE MODE. Il est préférable d'utiliser ici le niveau d'isolement READ COMMITTED car chaque lecture soutenue au sein de la même transaction lit à partir de son propre instantané.

Si rien d'autre ne vous aide, sérialisez vos transactions avec le verrouillage au niveau de la table. La bonne façon d'utiliser LOCK TABLES sur des tables transactionnelles (telles que InnoDB) est de définir AUTOCOMMIT = 0 et de ne pas appeler UNLOCK TABLES tant que vous n'avez pas explicitement validé la transaction. Par exemple, si vous devez écrire dans la table t1 et lire dans la table t, vous pouvez le faire comme suit :

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[do something with tables t1 and t2 here];

COMMIT;

UNLOCK TABLES;

Le verrouillage au niveau de la table permet à vos transactions de bien se mettre en file d'attente et d'éviter les blocages.

La façon d'obtenir une transaction sérialisée est de créer une table "sémaphore" auxiliaire, qui ne contient qu'une seule ligne. Laissez chaque transaction mettre à jour cette ligne avant d'accéder à d'autres tables. De cette manière, toutes les transactions se déroulent de manière séquentielle. Notez que l'algorithme de détection de blocage à la volée d'InnoDB fonctionne également dans ce cas car le verrouillage sérialisé est un verrouillage au niveau de la ligne. Les méthodes de délai d'attente, avec verrouillage au niveau de la table MySQL, doivent être utilisées pour résoudre les blocages.

Utilisez la commande LOCK TABLES dans l'application. Si AUTOCOMMIT=1, MySQL ne définit pas le verrouillage des tables InnoDB.

Recommandations associées : "Tutoriel mysql"

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!