MySQLRevoir le passé et découvrir le nouveau--Verrous dans le moteur de stockage Innodb-tutoriel mysql-php.cn

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.

, 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)
Copier après la connexion

mysql>
这个时候，事务t1已经锁定了表t1的所有a<5的数据行，然后去第二个窗口开启第二个事务t2，如下，会看到update语句一直在等待事务t1释放锁资源，过了几秒后，会有报错信息，如下t2事务窗口界面：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test.t1 set b='t2' where a=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
Copier après la connexion

3.2，通过3个系统表来查看锁信息
l 1，INNODB_TRX表
先看下表的重要字段以及记录的信息
a) trx_id：innodb存储引擎内部事务唯一的事务id。
b) trx_state : Le statut de la transaction en cours.
c) trx_started : L'heure à laquelle la transaction a commencé.
d) trx_requested_lock_id : En attente du verrouillage de transaction id, tel que Le statut de trx_state est LOCK WAIT, alors cette valeur représente l'id< qui a occupé le verrou ressource avant la transaction en cours. 🎜>, si trx_state n'est pas LOCK WAIT, cette valeur est nulle. .
e) trx_wait_started : L'heure à laquelle la transaction attend de commencer.
f) trx_weight : Le poids d'une transaction reflète le nombre de lignes modifiées et verrouillées par une transaction. Dans le moteur de stockage de innodb, lorsqu'un blocage se produit et qu'une restauration est requise, le moteur de stockage innodb sélectionnera la transaction avec le plus petite valeur Effectuer une restauration.
g) trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。
h) trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值
……
因为前面模拟了事务锁场景，开启了t1和t2事务，现在去查看这个表信息，会有2条记录如下：
mysql> select * from INNODB_TRX\G *************************** 1. row *************************** trx_id: 3015646 trx_state: LOCK WAIT trx_started: 2014-10-07 18:29:39 trx_requested_lock_id: 3015646:797:3:2 trx_wait_started: 2014-10-07 18:29:39 trx_weight: 2 trx_mysql_thread_id: 18 trx_query: update test.t1 set b='t2' where a=1 trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 376 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 *************************** 2. row *************************** trx_id: 3015645 trx_state: RUNNING trx_started: 2014-10-07 18:29:15 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 17 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 2 trx_lock_memory_bytes: 376 trx_rows_locked: 4 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 2 rows in set (0.00 sec) mysql>
Copier après la connexion

Voici juste un enregistrement de certaines transactions en cours d'exécution, telles que la transaction t2En cours d'exécutiontrx_query : update test.t1 set b=' t2' où l'instruction sql de a=1, t1 est exécutée en premier, donc c'est trx_state : RUNNINGLa ressource appliquée en premier a été en cours d'exécution et la ressource appliquée après t2run est ainsi trx_state: LOCK WAIT attend t1 pour libérer les ressources après l'exécution. Cependant, nous ne pouvons pas juger soigneusement certains détails du verrou. Nous devons examiner les données de la table INNODB_LOCKS.

l 2, INNODB_LOCKSTable
a) lock_id : verrouiller Le id et l'espace verrouillé idnuméro, numéro de page, numéro de ligne
b) lock_trx_id : Verrouiller la transaction id.
c) lock_mode : Mode de verrouillage.
d) lock_type：锁的类型，表锁还是行锁
e) lock_table：要加锁的表。
f) lock_index：锁的索引。
g) lock_space：innodb存储引擎表空间的id号码
h) lock_page：被锁住的页的数量，如果是表锁，则为null值。
i) lock_rec：被锁住的行的数量，如果表锁，则为null值。
j) lock_data：被锁住的行的主键值，如果表锁，则为null值。
mysql> select * from INNODB_LOCKS\G *************************** 1. row *************************** lock_id: 3015646:797:3:2 lock_trx_id: 3015646 lock_mode: X lock_type: RECORD lock_table: `test`.`t1` lock_index: PRIMARY lock_space: 797 lock_page: 3 lock_rec: 2 lock_data: 1 *************************** 2. row *************************** lock_id: 3015645:797:3:2 lock_trx_id: 3015645 lock_mode: X lock_type: RECORD lock_table: `test`.`t1` lock_index: PRIMARY lock_space: 797 lock_page: 3 lock_rec: 2 lock_data: 1 2 rows in set (0.00 sec) mysql>
Copier après la connexion

Ici, nous pouvons voir les informations de verrouillage actuelles, 2 les transactions sont verrouillées, voir les mêmes donnéeslock_space : 797, lock_page : 3, lock_rec : 2On peut conclure que la transaction t1 et la transaction t2 accèdent au même bloc de données innodb, puis transmettent lock_dataInformations sur le terrainlock_data : 1 On peut voir que les lignes de données verrouillées sont toutes des enregistrements de données avec la clé primaire de 1<🎜. >. 2 transactions t1 et t2 ont toutes deux été appliquées pour les mêmes ressources, il sera donc verrouillé et la transaction est en attente.
Les transactions t1 et peuvent également être vues via le lock_mode : X la valeur >t2 s'applique aux serrures exclusives.

PS : Lors de la mise à jour de la requête de plage, la valeur de lock_data n'est pas complètement exacte. Lorsque nous effectuons une mise à jour de plage, lock_data renvoie uniquement la valeur de clé primaire de la première ligne trouvée en premier id en plus, si ; La ressource actuelle est verrouillée. En même temps, la page verrouillée est remplacée en raison de la capacité du pool de tampons du moteur de stockage InnoDB Vérifiez à nouveau INNODB_LOCKS<. 🎜> table, cette lock_data affichera uneNULL valeur, ce qui signifie InnoDBLe moteur de stockage n'effectuera pas une autre recherche à partir du disque.

l 3, INNODB_LOCK_WAITSTable
Lorsque le volume de transaction est relativement faible, nous pouvons le vérifier visuellement. Lorsque le volume de transaction est très important et que l'attente de verrouillage se produit souvent, à ce moment-là, nous pouvons passer . Les principaux champs de la table INNODB_LOCK_WAITS sont les suivants :
La table INNODB_LOCK_WAITS est la suivante :
1) requesting_trx_id：申请锁资源的事务id。
2) requested_lock_id：申请的锁的id。
3) blocking_trx_id：阻塞的事务id。
4) blocking_lock_id：阻塞的锁的id。
去看下当前锁等待信息，如下所示：
mysql> select * from INNODB_LOCK_WAITS\G *************************** 1. row *************************** requesting_trx_id: 3015646 requested_lock_id: 3015646:797:3:2 blocking_trx_id: 3015645 blocking_lock_id: 3015645:797:3:2 1 row in set (0.00 sec) mysql>
Copier après la connexion

这里我们可以看到事务t1(3015646)申请了锁资源，而事务t2(3015645)则阻塞了事务t1的申请。我们管理其他表，得到更直观的详细信息，如下所示：
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query, it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`; +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ | 3015647 | 18 | update test.t1 set b='t2' where a>2 | 3015645 | 17 | NULL | +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ 1 row in set (0.00 sec) mysql>
Copier après la connexion

4, opération de lecture non verrouillable cohérente
4.1, CNRAnalyse des principes
Lecture non bloquante cohérente (lecture non bloquante cohérente, appelée CNR) fait référence au moteur de stockage InnoDB lisant l'exécution en cours via le multi-versioning de lignes (multi versioning) Données exécutées dans la base de données temporelle . Si la ligne de lecture exécute les opérations delete, update, alors l'opération de lecture n'attendra pas que la ligne soit verrouillée. , au contraire, le moteur de stockage InnoDB lira un instantané des données de la ligne, comme le montre la figure suivante :

Lecture non bloquante, car il n'y a pas besoin d'attendre pour les lignes consultées< La publication de 🎜> Données glissantes, de sorte que l'instantané lui-même n'a pas de surcharge supplémentaire. De plus, la lecture de l'instantané ne nécessite pas de verrouillage car il n'est pas nécessaire de modifier les données historiques. La lecture non verrouillable améliore considérablement la simultanéité de la lecture des données. Dans les paramètres par défaut du moteur de stockage
InnoDB
, il s'agit de la lecture par défaut. De cette façon, la lecture ne sera pas occupée et n'attendra pas le verrou sur la table. Cependant, les méthodes de lecture sont différentes selon les différents niveaux d'isolement des transactions. Tous les niveaux d'isolement des transactions ne permettent pas une lecture cohérente. De même, même s’ils utilisent tous deux des lectures cohérentes, la définition des données instantanées est différente.

快照数据其实就是当前数据之前的历史版本，可能有多个版本。如上图所示，一个行可能不止有一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

在Read Committed和Repeatable Read模式下，innodb存储引擎使用默认的非锁定一致读。在Read Committed隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；而在Repeatable Read隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

4.2，CNR实例
开启2个Session A和B。
Session A： mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from t1 where a=1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c2 | c2 | +---+----+----+ 1 row in set (0.00 sec) mysql>
Copier après la connexion

Session A中事务已经开始，读取了a=1的数据，但是还没有结束事务，这时我们再开启一个Session B，以此模拟并发的情况，然后对Session B做如下操作：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set a=111 where a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
Copier après la connexion

Session 中将a=1的行修改为a=111，但是事务同样没有提交，这样a=1的行其实加了一个X锁。这时如果再在Session A中读取a=1的数据，根据innodb存储引擎的特性，在Read Committed和Repeatable Read事务隔离级别下，会使用非锁定的一致性读。回到Session A，节着上次未提交的事务，执行select * from t1 where a=1;的操作，显示的数据应该都是原来的数据：
mysql> select * from t1 where a=1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c2 | c2 | +---+----+----+ 1 row in set (0.00 sec) mysql>
Copier après la connexion

因为当前a=1的数据被修改了1次，所以只有一个版本的数据，接着我们在Session B中commit上次的事务。如：
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql>
Copier après la connexion

Session B提交事务后，这时再在Session A中运行select * from t1 where a=1;的sql语句，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样，对于READ-COMMITTED模事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行的最新一个快照(fresh snapshot)。因此在这个例子中，因为Session B已经commit了事务，所以在READ-COMMITTED事务隔离级别下会得到如下结果，查询a=1就是为null记录，因为a=1的已经被commit成了a=111，但是如果查询a=111的记录则会被查到，如下所示：
mysql> show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) mysql> select * from t1 where a=1; Empty set (0.00 sec) mysql> select * from t1 where a=111; +-----+----+----+ | a | b | c | +-----+----+----+ | 111 | c2 | c2 | +-----+----+----+ 1 row in set (0.01 sec) mysql>
Copier après la connexion

但是如果在REPEATABLE-READ事务隔离级别下，总是读取事务开始时的数据，所以得到的结果截然不同，如下所示：
mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> select * from t1 where a=1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c0 | c2 | +---+----+----+ 1 row in set (0.00 sec) mysql> select * from t1 where a=111; Empty set (0.00 sec) mysql>
Copier après la connexion

对于READ-COMMITTED的事务隔离级别而言，从数据库理论的角度来看，其实违反了事务ACID的I的特性，既是隔离性，整理成时序表，如下图所示。
Time
Session A
Session B
| time 1
Begin;
Select * from t1 where a=1;有记录

| time 2

Début ;
Mise à jour t1 défini a=111 où a=1;
| heure 3

Sélectionnez * à partir de t1 où a=1;Avec enregistrement

heure 4

S'engager;
| 🎜>Aucun enregistrement

V temps 6
S'engager ;

Si le principe du I est suivi tout au long de la séance Session A, Sélectionnez * à partir de t1 où a=1; devrait conserver les données interrogées, mais au temps 5 Avant que la session A ne soit terminée, le résultat de la requête a changé et est incohérent avec le temps 1 et le temps 3, et ne répond pas à l'isolement de ACID .

5
, SÉLECTIONNER... POUR LA MISE À JOUR && SÉLECTIONNER... VERROUILLER EN MODE PARTAGEPar défaut, l'opération
select
du moteur de stockage innodb utilise la cohérence Non- verrouillage de la lecture, mais dans certains cas, l'opération de lecture doit être verrouillée. InnodbLe moteur de stockage prend en charge 2 types d'opérations d'ajout de verrou; ? SÉLECTIONNER... POUR LA MISE À JOUR Ajouter un
à l'enregistrement de la ligne de lecture X
verrouillage exclusif, d'autres transactions seront bloqués s'ils souhaitent effectuer des opérations dml ou select sur ces lignes.
? SELECT ... LOCK IN SHARE MODE 对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。
对于一致性非锁定读，即使读取的行数已经被SELECT ... FOR UPDATE了，但是也是可以进行读取的。
PS：… FOR UPDATE以及LOCK IN SHARE MODE必须在一个事务中，如果事务commit了，锁也就释放了，所以在使用的时候务必加上begin;start transaction或者set autocommit=0;

例子如下：
会话A：开启事务，执行LOCK IN SHARE MODE;锁定
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c0 | c2 | +---+----+----+ 1 row in set (0.00 sec) mysql>
Copier après la connexion

同时在另外一个窗口开启会话B，执行dml操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set a=111 where a=1;
Copier après la connexion

…这里会卡住，没有信息。

再开启一个会话C，查询INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就会看到锁的详细信息：
mysql> select * from INNODB_LOCKS; +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ | 3015708:797:3:2 | 3015708 | X | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 | | 3015706:797:3:2 | 3015706 | S | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 | +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.00 sec) mysql> mysql> select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 3015708 | 3015708:797:3:2 | 3015706 | 3015706:797:3:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_state` AS waiting_trx_status,it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query, -> it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query -> FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 -> WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`; +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ | waiting_trx_id | waiting_trx_status | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ | 3015708 | LOCK WAIT | 18 | update t1 set a=111 where a=1 | 3015706 | 21 | NULL | +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ 1 row in set (0.00 sec) mysql>
Copier après la connexion

会话A开启的事务1（事务id：3015706）执行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
La déclaration
a ajouté le verrou S à la ligne a=1, donc la session BTransaction ouverte2 (transactionid:23015708) exécute l'instruction update t1 set a=111where a=1;sql sur la ligne de a=1 Ajout de , donc la valeur d'état de la transaction 2 est LOCK WAIT, qui était en attente. Jusqu'à ce que la transaction attende l'expiration du délai, l'erreur est signalée comme suit : mysql> 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< La transaction en 🎜>
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>
Copier après la connexion

而在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)
Copier après la connexion

mysql>

7，MySQL外键和锁

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

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