Revisiter le mécanisme de verrouillage MySQL

Répertoire d'articles

• Verrouillage
• • Catégorie de verrouillage
  • Verrouillage partagé (verrouillage en lecture, lecture et écriture s'excluant mutuellement, la lecture et la lecture ne s'affectent pas)
  • Verrouillage exclusif (verrouillage en écriture, verrouillage exclusif)
  • La différence entre le verrouillage partagé et le verrouillage exclusif
• Verrouillage de table
• • Verrouillage d'intention : verrouillage partagé d'intention et verrouillage exclusif d'intention
  • • Pourquoi vous devez ajouter un verrouillage d'intention
  • Verrouillage de table unique
  • Verrouillage de table global
• Verrouillage de ligne
• Le verrouillage de ligne est mis à niveau vers le verrouillage de table (ligne Le verrou verrouille en fait l'index. Si la table entière est analysée sans utiliser l'index, la table entière sera verrouillée)
• Lorsqu'une certaine ligne d'enregistrements est modifiée ou supprimée, et la transaction n'est pas soumise, la ligne sera toujours verrouillée Verrouillage
• Verrouillage de l'enregistrement
• Gap Locks (Gap Locks)
• • Le rôle. de verrouillage d'espacement : empêcher la lecture fantôme
  • innodb automatique Conditions d'utilisation du verrouillage d'espacement
  • Zone verrouillée par verrouillage d'espacement
  • verrouillage à clé suivante [verrouillage pro-key]

• Verrouillage d'enregistrement, écart La différence entre les verrous et les verrous temporaires

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

Lock

Lock est un mécanisme permettant aux ordinateurs de coordonner plusieurs threads accédant à la même ressource partagée. Il est principalement utilisé. pour résoudre le problème de sécurité simultanée de plusieurs threads accédant à la même ressource partagée.

Classification des verrous

(1) Du point de vue des performances, elle est divisée en : verrou optimiste et verrou pessimiste
Utilisations de MySQL contrôle du numéro de version, JVM CAS est utilisé dans

(2) Le type d'opération de base de données est divisé en : verrouillage en lecture (verrou partagé), verrouillage en écriture (verrou exclusif) [Le verrouillage en lecture et le verrouillage en écriture sont tous deux des verrous pessimistes]

• Verrouillage en lecture (verrouillage partagé) : Pour le même enregistrement de ligne, plusieurs opérations de lecture peuvent être effectuées en même temps, mais aucune transaction ne peut être effectuée
• Verrouillage en écriture (verrouillage exclusif ) : Les transactions qui obtiennent des verrous exclusifs peuvent à la fois lire des données et modifier les données. Jusqu'à ce que la transaction qui acquiert le verrou en écriture soit terminée, elle empêchera les autres transactions d'acquérir des verrous en écriture ou en lecture.

(3) À partir du niveau de granularité, il est divisé en verrous de table et verrous de ligne

Remarque :
(1) Lire verrou, verrou en écriture Les verrous sont tous des verrous au niveau de la ligne, c'est-à-dire que la transaction 1 acquiert le verrou en écriture sur le produit A et que la transaction 2 acquiert le verrou en écriture sur le produit B et ne se bloqueront pas.
(2) Si l'instruction SQL utilise des verrous de ligne, lorsque SQL n'utilise pas d'index mais utilise des analyses de table complètes, les verrous au niveau des lignes deviendront des verrous de table.

(3) Le verrou ne sera libéré que lorsque la validation ou la restauration sera exécutée, et tous les verrous seront libérés en même temps.
(4) Lorsque le moteur Innodb exécute des instructions SQL ordinaires de modification et de suppression, il ajoutera des verrous de ligne aux lignes modifiées et supprimées.

Verrou partagé (verrouillage en lecture, exclusion mutuelle en lecture et en écriture, la lecture et la lecture ne s'affectent pas)

La transaction A a utilisé le verrou partagé pour obtenir un certain ( ou certains) enregistrement Lorsque la transaction B peut lire ces enregistrements, elle peut continuer à ajouter des verrous partagés, mais elle ne peut pas modifier ces enregistrements (lorsque la transaction C modifie ou supprime ces données, elle entre dans un état de blocage jusqu'à ce que le délai d'attente du verrou expire ou que la transaction A commits)

• Comment utiliser les verrous partagés et libérer les verrous partagés

# 加锁SELECT ... LOCK IN SHARE MODE# 释放锁commit;rollback;

• Fonction
  SELECT … VERROUILLAGE EN MODE PARTAGE ajoute des verrous partagés à plusieurs lignes d'enregistrements lus (share lock), les autres transactions ne peuvent interroger que ces lignes mais ne peuvent pas modifier ces enregistrements. Plusieurs transactions peuvent ajouter des verrous partagés au même enregistrement de ligne, de sorte que la transaction qui acquiert le verrou partagé ne modifie pas nécessairement les données de ligne

• Scénario d'utilisation : Lecture des résultats La dernière version de l'ensemble, tout en empêchant d'autres transactions de mettre à jour l'ensemble de résultats
  Par exemple : opérations simultanées sur l'inventaire des produits

Verrouillage exclusif (verrouillage en écriture, verrouillage exclusif )

select ... for update ajoute un verrou exclusif à l'enregistrement de ligne lu et autorise uniquement la transaction qui obtient le verrou exclusif à modifier l'enregistrement de ligne, empêchant d'autres transactions de modifier la ligne, tout comme l'instruction de mise à jour ordinaire ajoutera des verrous de ligne une fois exécutée.

• Comment utiliser les verrous exclusifs

# 加排他锁select ... for update //排他锁 # 释放锁commit;rollback;

La différence entre les verrous partagés et les verrous exclusifs

(1) Une fois la transaction acquiert Une fois le verrou exclusif acquis, les autres transactions ne peuvent plus acquérir le verrou exclusif.
Plusieurs transactions peuvent ajouter des verrous partagés à la même ligne de données.
(2) Une transaction qui ajoute un verrou partagé à une ligne spécifiée ne peut pas nécessairement modifier les données de cette ligne, car d'autres transactions peuvent également ajouter un verrou partagé ou un verrou exclusif à la ligne ; verrouiller sur la ligne spécifiée, Vous pouvez ensuite modifier les données de la ligne avec un verrouillage exclusif

Verrouillage de table

Le verrouillage au niveau de la table est principalement utilisé par certains non transactionnels moteurs de stockage tels que MyISAM, MEMORY et CSV.
Les verrous de table sont généralement utilisés lors de la migration des données.

Verrouillage d'intention : verrouillage partagé d'intention et verrouillage exclusif d'intention

Le principe de l'ajout d'un verrou partagé à une ligne est le suivant : la table où se trouvent les données de la ligne sera d'abord obtenir le verrou partagé intentionnel.
La condition préalable pour ajouter un verrou exclusif à une ligne est que la table où se trouvent les données de la ligne obtienne d'abord le verrou exclusif prévu.
Remarque : les verrous partagés d'intention et les verrous exclusifs d'intention sont des verrous de table et ne peuvent pas être créés manuellement.

Pourquoi devez-vous ajouter un verrouillage d'intention

意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。

单个表锁定

lock tables saas.goods read,saas.account write;　　// 给saas库中的goods表加读锁,account表加写锁unlock tables;   //解锁

全局表锁定

FLUSH TABLES WITH READ LOCK; 　　// 所有库所有表都被锁定只读unlock tables;         //解锁

注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

行锁

• 行锁是锁一行或者多行记录
• MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
  
  如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
  

行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）

上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。

修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定

窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。

记录锁

• 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
• 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）。

间隙锁（Gap Locks）

经典参考文章

间隙锁的作用：防止幻读

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止止间隙内有新数据被插入
（2）防止范围内已存在的数据被更新

innodb自动使用间隙锁的条件

（1）数据隔离级别必须为可重复读
（2）检索条件必须使用索引（没有使用索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。下图中，where number=5的话，那么间隙锁的区间范围为[4,11]；

session 1:start  transaction ;触发间隙锁的方式1：select  * from news where number=4 for update ;触发间隙锁的方式2：update news set number=3 where number=4; session 2:start  transaction ;insert into news value(2,4);#（阻塞）insert into news value(2,2);#（阻塞）insert into news value(4,4);#（阻塞）insert into news value(4,5);#（阻塞）insert into news value(7,5);#（执行成功）insert into news value(9,5);#（执行成功）insert into news value(11,5);#（执行成功）

next-key锁【临键锁】

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。InnoDB默认加锁方式是next-key 锁。

select * from news where number=4 for update ;

next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。

记录锁、间隙锁、临间锁的区别

update news  set number=0 where id>15

sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。

相关免费学习推荐：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!