L'apprentissage MySQL parle de la situation de verrouillage dans InnoDB

Cet article parlera de MySQL et présentera la situation de verrouillage dans InnoDB. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer. J'espère qu'il sera utile à tout le monde.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

Tout d'abord, une introduction de base aux verrous

Comparé à d'autres bases de données, le mécanisme de verrouillage de MySQL est relativement simple. Sa caractéristique la plus importante est que différents moteurs de stockage prennent en charge différents mécanismes de verrouillage. Par exemple, les moteurs de stockage MyISAM et MEMORY utilisent le verrouillage au niveau de la table ; le moteur de stockage InnoDB prend en charge à la fois le verrouillage au niveau de la ligne (verrouillage au niveau de la ligne) et le verrouillage au niveau de la table, mais le verrouillage au niveau de la ligne est utilisé par défaut.

Verrouillage au niveau de la table : faible surcharge, verrouillage rapide ; pas de blocage ; grande granularité de verrouillage, probabilité la plus élevée de conflits de verrouillage et concurrence la plus faible.

Verrouillage au niveau de la ligne : une surcharge élevée et un verrouillage lent peuvent survenir ; la granularité du verrouillage est la plus petite, la probabilité de conflits de verrouillage est la plus faible et la concurrence est la plus élevée.

Type de verrouillage au niveau de la ligne :

Verrouillage d'enregistrement (verrouillage d'enregistrement) : comme verrouillage d'enregistrement (verrouillage d'un seul enregistrement)

Le verrouillage d'enregistrement ne verrouillera l'enregistrement d'index que si la table de stockage InnoDB est créée lorsque il n'y a pas d'index, alors ce verrou utilisera la clé primaire implicite pour verrouiller, comme indiqué ci-dessous

Gap Lock : verrouille une plage, à l'exclusion de l'enregistrement lui-même (verrouillez uniquement le GAP devant les données)

Le verrou montré dans l'image ci-dessous est un verrou GAP, qui ne permet pas à d'autres transactions d'insérer de nouveaux enregistrements dans l'espace avant la colonne d'index 8, qui est l'intervalle (3, 8). Le rôle du verrou d'espacement est uniquement d'empêcher l'insertion d'enregistrements fantômes

Next-Key Lock : verrouillez l'enregistrement et le GAP devant l'enregistrement en même temps, c'est-à-dire Next-Key Verrouillage = Verrouillage d'enregistrement + Verrouillage d'espacement.

Deuxièmement, la classification des verrous

Verrous partagés Verrous partagés (appelés verrous S, appartenant aux verrous de rangée)

Verrous exclusifs (appelés verrous X, appartenant aux verrous de rangée)

Verrous partagés d'intention Verrous de partage (les verrous IS en abrégé appartiennent aux verrous de table)

Verrous exclusifs d'intention (les verrous IX en abrégé appartiennent aux verrous de table)

Verrous AUTO-INC (appartiennent aux verrous de table)

Ce qui suit est un introduction détaillée de chacun Pour différents types de verrous, nous construisons d'abord une table innodb comme suit

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

Verrou partagé

Le verrouillage partagé signifie que plusieurs transactions peuvent partager un verrou pour les mêmes données, et elles peuvent toutes le faire. accéder à la base de données, mais ils ne peuvent que lire Ne peut pas être modifié ;

Transaction A :

select * from tab_with_index lock in share mode ;

Transaction B :

select * from tab_with_index which id =1 ; Les données peuvent être interrogées

update tab_with_index set name = 'aa' Where id = 1 ;

Remarque : l'instruction de modification ici sera bloquée et l'opération ne pourra réussir que lorsque la transaction A sera soumise.

Verrou exclusif

Les verrous exclusifs ne peuvent pas coexister avec d'autres verrous. Si une transaction acquiert un verrou exclusif sur une ligne de données, les autres transactions ne peuvent pas acquérir le verrou sur la ligne. Seule la transaction acquérant actuellement le verrou exclusif. Les données peuvent être modifiées. . (supprimer, mettre à jour, créer sont des verrous exclusifs par défaut)

Transaction A :

select * from tab_with_index où id =1 pour la mise à jour

Transaction B :

select * from tab_with_index où id =1 / /Peut obtenir des résultats

select * from tab_with_index où id =1 pour la mise à jour ; // Bloqué

select * from tab_with_index où id = 1 verrou pour le mode partage // Bloqué

Remarque : Transaction B deux ; Chaque instruction SQL sera bloquée, c'est-à-dire que ni le verrou partagé ni le verrou exclusif ne pourront être obtenus. L'opération ne pourra réussir que lorsque la transaction A sera soumise.

Verrou partagé d'intention et verrouillage exclusif d'intention

Verrou partagé d'intention : Indique que la transaction se prépare à ajouter un verrou partagé à la ligne de données, ce qui signifie qu'une ligne de données doit d'abord obtenir le verrou IS de la table avant d'ajouter une serrure partagée.

Verrou exclusif intentionnel : indique que la transaction se prépare à ajouter un verrou exclusif à la ligne de données, c'est-à-dire qu'avant d'ajouter un verrou exclusif à une ligne de données, le verrou IX de la table doit être obtenu en premier.

Le verrou IS et le verrou IX sont des verrous au niveau de la table. Ils sont proposés uniquement pour déterminer rapidement si les enregistrements de la table sont verrouillés lors de l'ajout ultérieur de verrous S et de verrous X au niveau de la table, afin d'éviter d'utiliser le parcours pour les visualiser. . Y a-t-il des enregistrements verrouillés dans la table ? En d'autres termes, les verrous IS sont compatibles avec les verrous IX et les verrous IX sont compatibles avec les verrous IX. "Comment MySQL fonctionne"

Verrouillage à incrémentation automatique

Un verrou spécial au niveau de la table pour les colonnes à incrémentation automatique.

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失

三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。  排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

Résumé

Pour les tables InnoDB, cet article traite principalement du contenu suivant : (1) Les verrous de ligne d'InnoDB sont basés sur des index. Si les données ne sont pas accessibles via les index, InnoDB utilisera des verrous de table. (2) Sous différents niveaux d'isolement, le mécanisme de verrouillage d'InnoDB et la stratégie de lecture cohérente sont différents.

Après avoir compris les caractéristiques de verrouillage d'InnoDB, les utilisateurs peuvent réduire les conflits de verrouillage et les blocages grâce à des ajustements de conception et SQL, notamment :

• Essayez d'utiliser un niveau d'isolement inférieur pour concevoir soigneusement les index et essayez d'utiliser les index pour accéder aux données de manière à ce qu'ils puissent être utilisés. Le verrouillage est plus précis, réduisant ainsi le risque de conflits de verrouillage ;
• Choisissez une taille de transaction raisonnable, et la probabilité de conflits de verrouillage pour les petites transactions est plus petite
• Lors du verrouillage explicite de l'ensemble d'enregistrements, il est préférable de demander un niveau suffisant ; à la fois Verrouiller. Par exemple, si vous souhaitez modifier des données, il est préférable de demander directement un verrou exclusif au lieu de demander d'abord un verrou partagé, puis de demander un verrou exclusif lors de la modification. Cela peut facilement provoquer un blocage 
• Lorsque différents programmes accèdent à un. groupe de tableaux, ils doivent essayer de se mettre d'accord sur le même. Accédez à chaque tableau de manière séquentielle. Pour un tableau, accédez aux lignes du tableau dans un ordre fixe autant que possible. Cela peut réduire considérablement le risque de blocage ;
• Essayez d'utiliser des conditions égales pour accéder aux données, afin d'éviter l'impact des verrouillages d'espacement sur les insertions simultanées ; Ne demandez pas un niveau de verrouillage qui dépasse le besoin réel, sauf si cela est nécessaire ; ne pas afficher les verrous lors de l'interrogation ;
• Pour certaines transactions spécifiques, les verrous de table peuvent être utilisés pour augmenter la vitesse de traitement ou réduire le risque de blocage.

【Recommandations associées : tutoriel vidéo 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!