Que signifie le verrouillage au niveau des lignes Mysql ?

La première chose que nous devons savoir est que les verrous MySQL sont implémentés par un moteur de stockage spécifique. Par conséquent, il existe des différences dans les mécanismes d'implémentation du verrouillage du moteur par défaut de MySQL, MyISAM, et du moteur de plug-in tiers InnoDB.

Mysql a trois niveaux de verrouillage : verrouillage au niveau de la table, verrouillage au niveau de la page, verrouillage au niveau de la ligne

1. Définition

Le mécanisme de verrouillage qui verrouille une ligne de données à chaque fois est le verrouillage au niveau de la ligne ( niveau ligne). Le verrouillage au niveau des lignes n'est pas une méthode de verrouillage implémentée par MySQL lui-même, mais implémentée par d'autres moteurs de stockage. 2. Avantages et inconvénients

1 Avantages

En raison de la petite granularité du verrouillage, du faible taux de contention et de la concurrence élevée.
2. Inconvénients

Mise en œuvre complexe et frais généraux élevés.
Le verrouillage est lent et des blocages sont susceptibles de se produire
3. Moteurs de stockage pris en charge

• 4. -level Lock type

Les verrous au niveau de la ligne d'InnoDB sont également divisés en deux types : les verrous partagés et les verrous exclusifs. Dans la mise en œuvre du mécanisme de verrouillage, afin de permettre la coexistence des verrous au niveau de la ligne et des verrous au niveau de la table, InnoDB également. utilise des verrous d'intention (verrouillage de niveau de table), il existe deux types de verrous partagés d'intention et de verrous exclusifs d'intention.

La fonction du verrouillage d'intention est que lorsqu'une transaction doit obtenir un verrou de ressource, si la ressource dont elle a besoin est déjà occupée par un verrou exclusif, la transaction peut ajouter un verrou d'intention approprié à la table qui doit verrouiller la ligne. Si vous avez besoin d'un verrou partagé, ajoutez un verrou partagé d'intention sur la table. Si vous devez ajouter un verrou exclusif sur une certaine ligne (ou plusieurs lignes), ajoutez d'abord un verrou exclusif d'intention sur la table.

Plusieurs verrous partagés d'intention peuvent coexister en même temps, mais un seul verrou exclusif d'intention peut exister en même temps. Par conséquent, on peut dire que les modes de verrouillage d'InnoDB peuvent en fait être divisés en quatre types : le verrouillage partagé (S), le verrouillage exclusif (X), le verrouillage partagé intentionnel (IS) et le verrouillage exclusif intentionnel (IX)

Compatibilité des modes de verrouillage :

5. Implémentation du verrouillage au niveau des lignes

Le verrouillage des lignes InnoDB est implémenté en verrouillant les éléments d'index sur l'index. Par conséquent, InnoDB utilise des verrous au niveau des lignes uniquement lorsque les données sont récupérées via des conditions d'index. Sinon, InnoDB utilisera des verrous de table. Autres notes :

Lors d'une requête sans conditions d'index, InnoDB utilise des verrous de table au lieu de verrous de ligne.
Étant donné que le verrouillage de ligne de MySQL est destiné à l'index, pas à l'enregistrement, même si vous accédez à des enregistrements dans des lignes différentes, si la même clé d'index est utilisée, un conflit de verrouillage se produira.
Lorsqu'une table a plusieurs index, différentes transactions peuvent utiliser différents index pour verrouiller différentes lignes. De plus, que ce soit en utilisant un index de clé primaire, un index unique ou un index ordinaire, InnoDB utilisera des verrous de ligne pour verrouiller les données.
Même si un champ d'index est utilisé dans la condition, l'utilisation ou non de l'index pour récupérer des données est déterminée par MySQL en jugeant le coût des différents plans d'exécution si MySQL estime qu'une analyse complète de la table est plus efficace, comme par exemple. pour certaines petites tables, il n'utilisera pas d'index, auquel cas InnoDB utilisera des verrous de table au lieu de verrous de ligne. Par conséquent, lors de l'analyse des conflits de verrouillage, n'oubliez pas de vérifier le plan d'exécution SQL pour confirmer si l'index est réellement utilisé.
Verrouillage implicite :

InnoDB ajoute automatiquement des verrous d'intention.
Pour les instructions UPDATE, DELETE et INSERT, InnoDB ajoutera automatiquement des verrous exclusifs (X) aux ensembles de données impliqués
Pour les instructions SELECT ordinaires, InnoDB n'ajoutera aucun verrou ; :
Verrouillage partagé (S) : SELECT * FROM nom_table OÙ ... VERROUILLAGE EN MODE PARTAGE

Verrouillage exclusif (X) : SELECT * FROM nom_table OÙ ... POUR LA MISE À JOUR
Utilisez SELECT . .. IN SHARE MODE obtient un verrou partagé et est principalement utilisé pour confirmer si une certaine ligne d'enregistrements existe lorsque des dépendances de données sont requises, et pour garantir que personne n'effectue d'opérations UPDATE ou DELETE sur cet enregistrement.

• Cependant, si la transaction en cours doit également mettre à jour l'enregistrement, cela est susceptible de provoquer un blocage. Pour les applications qui doivent mettre à jour l'enregistrement de ligne après l'avoir verrouillé, la méthode SELECT... FOR UPDATE doit être utilisée pour obtenir un. serrure exclusive.

Comment verrouiller la table dans InnoDB :

Lorsque vous utilisez LOCK TABLES pour verrouiller la table InnoDB, veillez à définir AUTOCOMMIT sur 0, sinon MySQL ne verrouillera pas la table avant le verrouillage de la table ; La transaction se termine. , car UNLOCK TABLES validera implicitement la transaction ; COMMIT ou ROLLBACK ne peuvent pas libérer le verrou au niveau de la table ajouté avec LOCK TABLES, et le verrou de table doit être libéré avec UNLOCK TABLES.

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

Puisque nous avons tous utilisé des verrous de table, pourquoi ne pas choisir le moteur MyISAM ?

六、间隙锁（Next-Key锁）

1. 间隙锁定义：

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

• 当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

• 防止幻读，以满足相关隔离级别的要求。

• 为了数据恢复和复制的需要。

4. 注意

• 在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

• InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

• 回滚较小的那个事务

• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

• 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

• Que toutes les récupérations de données soient effectuées via l'index autant que possible, afin d'éviter qu'InnoDB ne soit mis à niveau vers un verrouillage au niveau de la table car il ne peut pas se verrouiller via la clé d'index

• Concevez l'index de manière raisonnable et laissez InnoDB se verrouiller ; sur la clé d'index. Soyez aussi précis que possible et réduisez autant que possible la portée du verrouillage pour éviter les verrouillages inutiles qui affectent l'exécution d'autres requêtes.

• Réduisez autant que possible les conditions de filtrage de récupération de données basées sur la plage pour éviter les problèmes. impact négatif causé par les verrouillages d'espacement Enregistrements verrouillés qui ne doivent pas être verrouillés

• Essayez de contrôler la taille des transactions, réduisez la quantité de ressources verrouillées et la durée du verrouillage

• Lorsque l'environnement commercial le permet, essayez ; utiliser l'isolation des transactions de niveau inférieur pour réduire les coûts supplémentaires de MySQL causés par la mise en œuvre des niveaux d'isolation des transactions.

(2) En raison du verrouillage au niveau des lignes et de la nature transactionnelle d'InnoDB, des blocages se produiront certainement. Voici quelques conseils couramment utilisés pour réduire la probabilité de blocages :

• Dans des modules métier similaires, faites de votre mieux. accéder selon la même séquence d'accès pour éviter les blocages ;

• Dans la même transaction, essayez de verrouiller toutes les ressources requises en même temps pour réduire la probabilité de blocage ;

• Pour les cas où un blocage est très facile à se produire ; Pour la partie commerciale du verrou, vous pouvez essayer d'améliorer la granularité du verrouillage et de réduire la probabilité de blocage grâce au verrouillage au niveau de la table.

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!