MySQLRevoir le passé et découvrir le nouveau--Verrous dans le moteur de stockage Innodb

J'ai rencontré beaucoup de problèmes de serrures récemment, donc après les avoir résolus, j'ai lu attentivement les livres sur les serrures. Ils sont organisés comme suit :

. 1 , type de serrure

Innodb implémentation du moteur de stockage Les 2 verrous standards au niveau des lignes sont fournis :

Verrou partagé (S ? lock) , permet à la transaction de lire une ligne de données.

? Verrouillage exclusif (X lock), permettant à une transaction de supprimer ou de mettre à jour une ligne de données.

Lorsqu'une transaction acquiert le verrou partagé sur la ligne r, puis une autre La transaction peut également acquérir immédiatement le verrou partagé sur la ligne r car la lecture ne modifie pas les données sur la ligne r, qui est la compatible avec le verrouillage du boîtier. Mais si une transaction veut obtenir un verrou exclusif sur la ligne r, elle doit attendre que la transaction libère le verrou partagé sur la ligne r—Dans ce cas, les serrures sont incompatibles. La compatibilité entre les deux est indiquée dans le tableau suivant :

ConflitS Conflit

Compatibilité des verrous exclusifs et des verrous partagés

🎜>

X Serrure exclusive

Conflit

Verrouillage partagé

Compatible

2, extension de serrure

InnodbLe moteur de stockage prend en charge le verrouillage multi-granularité, qui permet aux verrous au niveau des lignes et aux verrous au niveau de la table d'exister en même temps. Afin de prendre en charge les opérations de verrouillage à différentes granularités, le moteur de stockage InnoDB prend en charge une méthode de verrouillage supplémentaire, qui est le verrouillage intentionnel. Les verrous d'intention sont des verrous au niveau de la table conçus principalement pour révéler le type de verrou qui sera demandé pour la ligne suivante d'une transaction. Il est également divisé en deux types :

 ? Intention de verrouillage partagé (IS Lock), la transaction souhaite obtenir certaines lignes dans un verrouillage partagé de la table.

? Verrouillage exclusif intentionnel (IX Lock), la transaction veut obtenir un verrouillage exclusif sur certaines lignes d'une table.

Puisque InnoDB prend en charge les verrous au niveau des lignes, les verrous d'intention ne fonctionnent pas réellement L'assembly bloque toute requête sauf le scan de la table complète. Les verrous partagés, les verrous exclusifs, les verrous partagés d'intention et les verrous exclusifs d'intention sont tous compatibles les uns avec les autres / relations mutuellement exclusives, qui peuvent être représentées par une matrice de compatibilité (y signifie compatible, n signifie incompatible ), comme indiqué ci-dessous :

IX IS Conflit S Conflit

	🎜>	Verrou exclusif Intention	Verrouillage partagé intentionnel	X Verrouillage exclusif
Conflit	Conflit	Conflit		Verrouillage partagé
	Compatible	Conflit	Compatible avec
IX Verrouillage exclusif Intention	Conflit	Conflit	Compatible	Compatible avec
IS Verrouillage partagé par intention	Conflit	Compatible	Compatible	Compatible

  Analyse : La relation de compatibilité mutuelle entre X et SÉtape 1 a été décrite. Les relations mutuelles entre IX et IS sont toutes compatibles, ce qui est aussi bien. Comprenez, car ils ne sont que "intentionnels" et sont encore au stade YY Ils n'ont vraiment rien fait, donc ils sont compatibles

.

gauche Les suivants sont X et IX, >IS, S et IX, S et IS, on peut déduire ces quatre ensembles de relations à partir des relations de X et S.

En termes simples : X et IX de La relation entre 🎜> et X . Pourquoi? Car après qu'une transaction ait acquis le verrou IX, elle a le droit d'acquérir le verrou X. Si X et IX sont compatibles, les deux transactions obtiendront une situation de verrouillage X , cela est contradictoire avec ce que nous savons sur X et X étant mutuellement exclusifs, donc X et IX ne peuvent avoir qu'une relation mutuellement exclusive. Les trois autres ensembles de relations sont similaires et peuvent être dérivés de la même manière.

3

, scène de verrouillage simulée

Avant InnoDB Plugin, nous ne pouvions passer que SHOW FULL PROCESSLIS et SHOW ENGINE INNODB STATUS pour afficher la demande de base de données actuelle, puis déterminer la situation de verrouillage dans la transaction. Dans la nouvelle version de InnoDB Plugin, 3 sont ajoutés dans le information_schema Table de bibliothèque, INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS. Grâce à ces 3 tableaux, vous pourrez suivre plus facilement les transactions en cours et analyser d'éventuels problèmes de verrouillage. Si la base de données fonctionne normalement, ces 3 tables seront vides sans aucun enregistrement.

3.1, transaction ouverte t1, t2, Simulez le verrouillage

pour ouvrir les fenêtres 2session et ouvrez 2transactions t1 et t2.

Ouvrez la transaction dans la première fenêtre t1Effectuez une opération de verrouillage, comme suit t1Interface de la fenêtre de transaction :

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

mysql>

这个时候，事务t1已经锁定了表t1的所有ac045d482ca1836bf84c16e94b3401ef0 update t1 set a=111 Where a=1;ERREUR 1205 (HY000) : Délai d'attente du verrouillage dépassé ; essayez de redémarrer la transaction

mysql>

À ce moment, session

B

2 est terminée

mise à jour t1 set a=111 où a=1; 'sdmlOpération de requête. 6

, incrémentation et verrouillage automatiques

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

 

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

 

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

 

mysql>

 

7，MySQL外键和锁

在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比oracle做的较好一些，oracle需要自己手动添加外键锁。

 以上就是MySQL 温故而知新--Innodb存储引擎中的锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！