Comment implémenter des verrous de ligne, des verrous de table et des blocages dans le mécanisme de verrouillage Mysql

1. Qu'est-ce que le verrouillage MySQL ? Quels types de serrures existe-t-il ?

Définition du verrouillage :
La même ressource n'est accessible que par un seul thread à la fois
Dans la base de données, en plus de la traditionnelle compétition pour les ressources informatiques (telles que le CPU, les E/S, etc.) , les données sont également une ressource accessible par plusieurs threads en même temps. Ressources partagées par les utilisateurs. Comment garantir la cohérence et l'efficacité de l'accès simultané aux données est un problème que toutes les bases de données doivent résoudre. Les conflits de verrouillage sont également un facteur important affectant les performances de l'accès simultané aux bases de données.

Le verrouillage optimiste utilise le plus grand nombre d'enregistrements de données pour refléter la version, qui est en fait un identifiant.

Par exemple :update test set a=a-1 where id=100 and a> 0 ; La version correspondante est le champ a. Il ne nécessite pas nécessairement un champ appelé version. En même temps, il ne sera déclenché que lorsque cette condition sera remplie.

Classification des verrous :
De la classification des types d'opérations de données (lecture ou écriture)Verrou de lecture (verrou partagé) : Pour les mêmes données, plusieurs opérations de lecture peuvent être effectuées en même temps sans affecter l'un l'autre.
Verrouillage en écriture (verrouillage exclusif) : avant que l'opération d'écriture en cours ne soit terminée, il bloquera les autres verrous en écriture et en lecture.

De la classification de granularité des opérations de donnéesVerrouillage au niveau de la table : le verrouillage au niveau de la table est le verrou avec la plus grande granularité dans MySQL, ce qui signifie verrouiller la table entière de l'opération en cours (le moteur MyISAM utilise par défaut le verrouillage au niveau de la table , seuls les verrous au niveau de la table sont également pris en charge).
Par exemple, si vous mettez à jour une donnée dans une table de 100 000 tables, les autres transactions seront exclues avant que cette mise à jour ne valide la transaction. La granularité est très grande. Verrouillage au niveau de la ligne : le verrouillage au niveau de la ligne est le verrouillage le plus granulaire dans Mysql, ce qui signifie que seule la ligne de l'opération en cours est verrouillée (
Il est basé sur l'index, donc une fois qu'une opération de verrouillage n'utilise pas l'index , alors le verrou dégénérera en un verrou de table)Verrouillage au niveau de la page : le verrouillage au niveau de la page est un verrou dans MySQL avec une granularité de verrouillage entre les verrous au niveau de la ligne et les verrous au niveau de la table. un moment

De la distribution du point de vue de la concurrence -- En fait, le verrouillage optimiste et le verrouillage pessimiste ne sont que des idéesVerrouillage pessimiste : une attitude conservatrice (pessimiste) à l'égard des données modifiées par le monde extérieur (y compris d'autres transactions courantes du système et traitement des transactions à partir de systèmes externes), par conséquent, les données sont verrouillées pendant tout le processus de traitement des données.
Verrouillage optimiste : le verrouillage optimiste suppose que les données ne provoqueront généralement pas de conflits. Ainsi, lorsque les données sont soumises pour mise à jour, le conflit de données sera officiellement détecté, si un conflit est détecté, un message d'erreur sera renvoyé avant de continuer. réessayez

Autres verrous : Verrouillage des espaces : dans les requêtes conditionnelles, telles que : où id>100, InnoDB verrouillera les éléments d'index des enregistrements de données existants qui remplissent les conditions pour les valeurs clés dans la plage de conditions mais les enregistrements ; qui n'existent pas sont appelés « GAP ». Le but de l'écart est d'empêcher la lecture fantôme.
Le verrouillage d'intention est divisé en verrouillage partagé d'intention (IS) et verrouillage exclusif d'intention (IX). est d'indiquer qu'une transaction verrouille ou verrouillera des lignes dans une table

2. La différence entre les verrous de ligne et les verrous de table

Les verrous au niveau de la table sont les verrous avec la plus grande granularité dans MySQL, qui représentent la table entière de l'opération actuelle de verrouillage, elle est simple à mettre en œuvre. Les MYISAM et INNODB les plus couramment utilisés prennent en charge le verrouillage au niveau de la table. Caractéristiques :
Faible surcharge, verrouillage rapide ; pas de blocage ; grande granularité de verrouillage, probabilité la plus élevée de conflits de verrouillage et concurrence la plus faible.

Le verrouillage au niveau de la ligne est le verrouillage le plus fin de Mysql, ce qui signifie que seule la ligne actuellement utilisée est verrouillée. Les verrous au niveau des lignes peuvent réduire considérablement les conflits dans les opérations de base de données. Sa granularité de verrouillage est la plus petite, mais la surcharge de verrouillage est également la plus importante. Caractéristiques : surcharge élevée, verrouillage lent ; des blocages peuvent survenir ; la granularité du verrouillage est la plus faible, la probabilité de conflits de verrouillage est la plus faible et la concurrence est la plus élevée
Utilisation : le verrouillage des lignes InnoDB est obtenu en verrouillant les éléments d'index sur l'index. . Uniquement lorsque
les données sont récupérées via des conditions d'index, InnoDB utilisera des verrous au niveau de la ligne, sinon, InnoDB utilisera des verrous de table

Dans l'instruction de mise à jour ci-dessous, si b est un champ général et non une colonne d'index, alors la ligne. Les verrous au niveau seront remplacés par des verrous au niveau de la table.

update from test set a=100 where b='100';

Prenons maintenant un exemple pratique pour voir comment innnodb utilise les verrous de ligne.

Données dans la table actuelle :

Ouvrez d'abord deux fenêtres de session, puis définissez le niveau de transaction mysql sur le niveau sans engagement :

Fenêtre de session 1 :

Fenêtre de session 2 :

其中会话2的update一直都在Running中，一直到超时结束，或者会话1提交事务后才会Running结束。

可以通过show VARIABLES like "%innodb_lock_wait_timeout%" 查询当前mysql设置的锁超时时间,默认是50秒。

可以通过set innodb_lock_wait_timeout = 60; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了innodb用了锁。

那怎么确定是用了行锁呢？

总结:会话一更新id=125的时候，给这条数据add lock了，那么在会话2中再次更新id=125的时候，这条数据是locked中的。这个lock加的是id=125这条记录。证明默认情况下id=125这条记录会加上行锁，除了这条记录之外的其它记录都可以成功地操作。

三、InnoDB死锁概念和死锁案例

发生死锁是因为多个事务相互持有和请求锁，并形成了一个循环依赖关系。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。

自动检测事务死锁并回滚一个事务，同时返回错误信息的功能由InnoDB自动实现。它根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

死锁场景一之select for update:

产生场景：两个transaction都有两个select for update，transaction a先锁记录1，再锁记录2；而transaction b先锁记录2，再锁记录1

写锁：for update，读锁：for my share mode show engine innodb status

验证下死锁的场景:

第一步更新会话一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新会话二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新会话一:

select * from wnn_test where a=101 for update;

第四步更新会话二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的时候，已经发生了死锁。

来看下执行的日志:

show engine innodb status;最后一个锁的时间，锁的表，引起锁的语句。其中session1被锁 14秒(ACTIVE 14)，session 2被锁了10秒(Active 10)

死锁场景二之两个update

产生场景：两个transaction都有两个update，transaction a先更新记录1，再更新记录2；而transaction b先更新记录2，再更新记录1

 产生日志：

 注意：仔细查看上面2个例子可以发现一个现象，当2条资源锁住后，再执行第三个会执行成功，但是第四个会提示死锁。在mysql5.7中，执行第三个的时候就会一直在Running状态了，本博文使用的是mysql8.0 ，其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。

 那么这个innodb_deadlock_detect参数，到底要不要启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

Ce n'est que lorsque vous confirmez que la détection des blocages affecte les performances du système et que la désactivation de la détection des blocages n'aura pas d'effets négatifs, vous pouvez essayer de désactiver l'option innodb_deadlock_detect. De plus, Si la détection de blocage InnoDB est désactivée, la valeur du paramètre innodb_lock_wait_timeout doit être ajustée pour répondre aux besoins réels.

4. Comment éviter les impasses lors du développement du programme

L'essence des verrous est que les ressources se font concurrence et s'attendent, souvent deux (ou plus) ) L'ordre de verrouillage de session est incohérent

Comment éviter efficacement :

Dans le programme , il existe de nombreuses opérations Lors de l'ouverture d'une table, essayez d'y accéder dans le même ordre (pour éviter de former une boucle d'attente) Former une boucle d'attente) Un identifiant de thread : 1, 10, 20 est verrouillé dans l'ordre B identifiant de thread : 20, 10, 1 Dans ce cas, il est facile de verrouiller.

Si possible, transformez les grosses transactions en petites transactions, ou même ne pas ouvrir la transaction, sélectionnez pour update==>insert==>update = insérer dans la mise à jour sur clé en double# 🎜🎜#

Pour éviter les verrous de table, il est recommandé d'utiliser des index pour accéder aux données autant que possible et d'éviter les opérations sans conditions Where, car l'utilisation d'index peut enregistrer des verrous de ligne sans provoquer de verrous de table

Utilisez une requête à valeur égale au lieu d'une requête par plage pour interroger les données, accéder aux enregistrements et éviter l'impact des verrouillages d'espacement sur la concurrence 1, 10, 20 égale valeurs où id dans (1, 10,20) Range query id>1 et id

Évitez d'exécuter plusieurs scripts qui lisent et écrivent la même table à en même temps, accordez une attention particulière aux instructions de verrouillage et d'exploitation contenant des quantités de données relativement importantes ; nous avons souvent des scripts de synchronisation pour les empêcher de s'exécuter au même moment

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!