Explication détaillée de l'utilisation des blocages MySQL et des méthodes de détection et d'évitement

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement l'explication détaillée de l'utilisation des blocages MySQL et des méthodes de détection et d'évitement. L'article présente le contenu détaillé autour du thème, qui a une certaine valeur de référence. écoutez, j'espère que cela aidera tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Lorsque nous utilisons des verrous, il y a un problème auquel il faut prêter attention et éviter. Nous savons que les verrous exclusifs ont des caractéristiques mutuellement exclusives. Lorsqu'une transaction ou un thread détient un verrou, cela empêchera les autres threads d'acquérir le verrou. Cela entraînera une attente de blocage. Si vous attendez dans une boucle, cela peut provoquer un blocage.

Nous devons analyser ce problème sous plusieurs aspects. Le premier est la raison pour laquelle le verrou n'est pas libéré, le deuxième est que faire s'il est bloqué et le troisième est la manière dont le blocage se produit et comment l'éviter.

Libération et blocage du verrou

Révision : Quand le verrou sera-t-il libéré ?
Fin de la transaction (validation, restauration)﹔
Connexion client déconnectée.

Si une transaction n'a pas libéré le verrou, combien de temps les autres transactions seront-elles bloquées ? Vont-elles attendre indéfiniment ?
Si tel est le cas, lorsque l'accès simultané est relativement élevé, si un grand nombre de transactions ne peuvent pas obtenir immédiatement les verrous requis, le blocage sera suspendu. occupent beaucoup de ressources informatiques, provoquent de graves problèmes de performances et même traînent dans la base de données.

Avez-vous peur de cette erreur en ligne ?

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL a un paramètre pour contrôler le temps d'attente pour acquérir le verrou, la valeur par défaut est de 50 secondes.

show VARIABLES like "innodb_lock_wait_timeout";

En cas de blocage, peu importe combien de temps vous attendez, vous ne pouvez pas obtenir le verrou. Dans ce cas, devez-vous attendre 50 secondes, n'est-ce pas 50 secondes perdues en vain ?

L'apparition et la détection d'un blocage ?

Faisons une démonstration et ouvrons deux séances :

Pour faciliter le retrait de la timeline, des images sont utilisées ici Si vous êtes intéressé, vous pouvez les imiter

Châtaigne une :

Châtaigne deux. :

Dans la première transaction, un blocage a été détecté et sorti immédiatement. La deuxième transaction a obtenu le verrou sans attendre 50 secondes :

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

Pourquoi peut-il être détecté directement ? . Certaines conditions doivent être remplies. Pour nous, programmeurs, avoir des conditions claires signifie être capable de déterminer. Par conséquent, lorsqu'un blocage se produit, InnoDB peut généralement le détecter automatiquement via l'algorithme (graphique d'attente).

Alors, quelles conditions doivent être remplies pour qu'un blocage se produise, car le verrou lui-même s'exclut mutuellement :

• (1) Une seule transaction peut détenir ce verrou en même temps ; 2) Les autres transactions doivent obtenir le verrou après que cette transaction a libéré le verrou et ne peuvent pas en être privées de force
• (3) Lorsque plusieurs transactions forment une boucle d'attente, une impasse se produit ;
• Le salon de coiffure compte deux directeurs. Il y a le professeur Tony qui est responsable de la coupe des cheveux et le professeur Kelvin qui est responsable du lavage des cheveux. Le professeur Tony ne peut pas couper les cheveux de deux personnes en même temps. C'est ce qu'on appelle mutuellement exclusif.

Quand Tony coupe les cheveux de quelqu'un, vous ne pouvez pas lui demander de s'arrêter et de vous couper les cheveux. C'est ce qu'on appelle ne peut pas être enlevé de force. 互斥。

Tony在给别人在剪头的时候，你不能让他停下来帮你剪头，这个叫不能强行剥夺。
如果Tony的客户对Kelvin说:你不帮我洗头我怎么剪头? Kelvin 的客户对Tony说:你不帮我剪头我怎么洗头?这个就叫形成等待环路Si le client de Tony dit à Kelvin : Comment puis-je me couper les cheveux si vous ne les lavez pas pour moi ? Le client de Kelvin dit à Tony : Comment puis-je me laver les cheveux si vous ne me coupez pas les cheveux ? Cela s'appelle former une boucle d'attente code>. <br>En fait, il existe de nombreuses situations où une impasse se produit, mais elles remplissent toutes les trois conditions ci-dessus. <br>C'est aussi pourquoi <strong>les verrous de table ne provoqueront pas de blocage, car les ressources des verrous de table sont acquises en une seule fois</strong>.

Si le verrou n'a pas été libéré, cela peut provoquer de nombreux blocages ou blocages, entraînant une diminution du débit du système. À ce stade, vous devez vérifier quelles transactions détiennent le verrou.

Afficher les informations de verrouillage (log)

Tout d'abord, la commande SHow STATUS inclut des informations de verrouillage de ligne :

show status like 'innodb_row_lock_%';

lnnodb_row_lock_current_waits : le nombre de verrous actuellement en attente de verrous ;
lnnodb_row_lock_time : depuis le démarrage du système ; au temps de verrouillage total actuel, en ms ;
Innodb_row_lock_time_avg : le temps moyen passé à attendre à chaque fois ;
Innodb_row_lock_time_max : le temps d'attente le plus long depuis le démarrage du système jusqu'à maintenant ; fréquence. La commande

SHOW est une information récapitulative. InnoDB fournit également trois tables pour analyser les transactions et les verrous :

select * from information_schema.INNODB_TRX; --当前运行的所有事务﹐还有具体的语句

select* from information_schema.INNODB_LOCKS; --当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS; --锁等待的对应关系

更加详细的锁信息，开启标准监控和锁监控:

额外的监控肯定会消耗额外的性能

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

通过分析锁日志，找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX表中的trx_mysql_thread_id，例如执行kill 4，kill 7， kill 8。
当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。
有哪些可以避免死锁的方法呢?

死锁的避免

• 1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路)
• 2、批量操作单张表数据的时候，先对数据进行排序(避免形成等待环路);
• 3、申请足够级别的锁，如果要操作数据，就申请排它锁;
• 4、尽量使用索引访问数据，避免没有where条件的操作，避免锁表;
• 5、如果可以，大事务化成小事务;
• 6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

推荐学习：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!