Explication détaillée du mécanisme de verrouillage MySQL

J'ai appris la base de données mysql pendant cette période. L'équipe du projet utilise oracle, donc la compréhension de mysql n'est pas approfondie. Cet article est principalement un résumé des verrous mysql. Les verrous

Mysql sont principalement divisés en 3 catégories :

Verrouillage au niveau de la table : le moteur de stockage est Myisam. Le verrouillage de la table entière se caractérise par une faible surcharge, un verrouillage rapide, un verrouillage fort, la probabilité la plus élevée de conflits de verrouillage et la concurrence la plus faible.

Verrouillage au niveau de la page : Le moteur de stockage est BDB. Verrouillez une certaine page de données (16 Ko environ), fonctionnalités : la surcharge et le temps de verrouillage se situent entre le niveau de la table et le niveau de la ligne ; un blocage se produira, la force de verrouillage se situe entre le niveau de la table. Entre les verrous et les verrous de ligne, la concurrence est moyenne.

Verrouillage au niveau des lignes : Le moteur de stockage est innodb. Verrouillage d'une certaine ligne de données, caractéristiques : la mise en œuvre du verrouillage est plus compliquée, plus coûteuse et la vitesse de verrouillage est lente.

Sur la base des caractéristiques ci-dessus, du point de vue du verrouillage uniquement : les verrous au niveau des tables sont plus adaptés aux applications qui sont principalement basées sur des requêtes et n'ont qu'une petite quantité de données mises à jour en fonction de les conditions d'index, telles que les applications Web  ; tandis que les verrous au niveau des lignes sont plus adaptés aux applications qui ont un grand nombre de mises à jour simultanées d'une petite quantité de données différentes basées sur des conditions d'index et des requêtes simultanées, comme certaines système de traitement des transactions en ligne (OLTP).

Ensuite, nous donnerons une explication détaillée des verrous au niveau des lignes. Les verrous au niveau des lignes sont principalement divisés dans les 7 catégories suivantes : partagés /Verrouillage exclusif, verrouillage d'intention, verrouillage d'enregistrement, verrouillage d'espace, verrouillage temporaire, verrouillage d'intention d'insertion, verrouillage à incrémentation automatique.

Partagé/Verrouillage exclusif :

Verrouillage partagé : aussi appelé verrouillage en lecture, il permet de lire mais pas d'écrire. Les verrous partagés peuvent être utilisés avec des verrous partagés. Déclaration :

select ... lock in share mode

Verrou exclusif : également appelé verrouillage en écriture, ni la lecture ni l'écriture ne sont autorisées. Le verrouillage exclusif ne peut pas être utilisé avec d'autres verrous. Déclaration :

select ... for update

dans mysql, mettre à jour, supprimer, insert, alter Ces opérations d'écriture ajouteront des verrous exclusifs par défaut. Sélectionner n'ajoutera aucun type de verrouillage par défaut. Une fois que la tâche d'écriture des données n'est pas terminée, les données ne peuvent pas être lues par d'autres tâches, ce qui a un plus grand impact sur les opérations simultanées.

Verrouillage d'intention : innoDB Afin de prendre en charge les verrous multi-granularité, c'est-à-dire de permettre aux verrous de niveau ligne et aux verrous de niveau table de coexister, les verrous d'intention sont introduits. Les verrous d'intention signifient qu'à un moment donné dans le futur, les transactions devront peut-être ajouter des verrous exclusifs / partagés, alors déclarez une intention à l'avance. De cette façon, si quelqu'un essaie de modifier la table entière, il n'est pas nécessaire de déterminer si les données de la table sont verrouillées. Il lui suffit d'attendre que le verrouillage mutex intentionnel soit libéré.

Verrou partagé d'intention (IS) : Si une transaction souhaite obtenir un verrou partagé pour certains enregistrements de la table, elle doit obtenez d'abord un verrou partagé sur la table. Ajoutez un verrou de partage d'intention.

Verrouillage mutex d'intention (IX) : Si une transaction souhaite obtenir un verrouillage mutex pour certains enregistrements de la table, elle doit Ajoutez d’abord un verrou mutex d’intention.

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

  记录锁（RS）：单个行记录上的锁。记录锁总是会锁住索引记录，如果innoDB存储引擎表

在建立的时候没有设置任何一个索引，那么innoDB存储引擎会使用隐式的主键来进行锁定。

间隙锁（GR）：间隙锁锁住记录中的间隔，即范围查询的记录。

 Select * From user where id between 1 and 10 for update

这个脚本会锁住1到10 的数据，以防止其他事务修改该区间的记录；

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效

临建锁（next-key Locks）：临建锁是记录锁和间隙锁的组合，锁的范围既包含记录又包含索引区间。默认情况下，innoDB使用临建锁来锁定记录。但当查询的索引含有唯一属性的时候，临建锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

插入意向锁(insert intention locks):对已有数据行的修改和删除，必须加互斥锁，对于数据的插入，加插入意向锁。是专门针对于insert操作的。

自增锁（auto-inc locks）：是一种特殊的表级别的锁，专门针对事务插入auto-increment类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

---------------------------------------------------------分界线--------------------------------------------------------------

接下看讲一下其他的锁：

死锁：产生是因为线程锁之间交替等待产生的。值两个或两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。

Mysql处理死锁的方法：根据数据写的数据量的大小来回滚小事务。

乐观/悲观锁：

乐观锁：乐观的假定大概率不会发生并发更新冲突，访问，处理数据的过程中不加锁，只在更新数据时根据版本号或时间戳判断是否有冲突，有则处理，无责提交事务。

如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁

悲观锁:悲观的假定大概率会发生并发更新冲突，访问，处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。

优点：

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

缺点：

（a）在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；

(b) Dans le traitement des transactions en lecture seule, il n'y a pas de conflit et il n'est pas nécessaire d'utiliser des verrous. Cela ne fera qu'augmenter la charge du système, cela réduira également le parallélisme. une transaction verrouille une ligne de données, les autres transactions doivent attendre que la transaction soit traitée avant de traiter ce nombre de lignes

Suggestion :

1. Contrôler la taille de la transaction (opération Quantité de données écrites)
2. Lorsque vous utilisez des verrous, essayez de les utiliser en conjonction avec des champs qui portent des index, pour éviter de passer aux verrous de table
3. Requêtes de plage et essayez de réduire la taille de transactions basées sur des requêtes de plage
4. Si l'entreprise doit utiliser des verrous et que le conflit de verrouillage est particulièrement élevé, passez aux verrous de table
5. Vous pouvez ajuster la transaction en fonction des propres conditions du projet innodb_flush_log_at_trx_commit

Apprentissage recommandé : 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!