En savoir plus sur les transactions et les verrous dans MySQL

La base de données MySQL est un système d'accès multi-utilisateurs, donc lorsque plusieurs utilisateurs lisent et mettent à jour des données en même temps, les données ne seront pas détruites, les verrous sont donc nés. L'utilisateur tente de modifier des enregistrements dans la base de données, il doit d'abord acquérir le verrou. Ensuite, pendant que l'utilisateur détenant le verrou est toujours en train de modifier, les autres utilisateurs ne peuvent pas modifier ces enregistrements. [Recommandations associées : Tutoriel vidéo MySQL]

Verrous dans MySQL

Mais comparé à d'autres bases de données, le mécanisme de verrouillage de MySQL est relativement simple. Différents moteurs de stockage MySQL ont des mécanismes de verrouillage différents, y compris les moteurs de stockage MylSAM et MEMORY Table-. des verrous de niveau sont utilisés et le moteur de stockage BDB utilise des verrous de page. Le moteur de stockage InnoDB couramment utilisé prend en charge les verrous au niveau de la ligne et les verrous au niveau de la table, et les verrous au niveau de la ligne sont utilisés par défaut.

Les caractéristiques de ces trois types de verrous sont les suivantes :

• Verrous 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 : surcharge élevée, verrouillage lent, un blocage se produira, la plus petite granularité de verrouillage, la plus faible probabilité de conflit de verrouillage et le plus haut degré de concurrence.

• Verrouillage de page : le coût et le temps de verrouillage se situent entre les verrous de table et les verrous de ligne, des blocages se produiront, la granularité du verrouillage se situe entre les verrous de table et les verrous de ligne, et la concurrence est moyenne.

MyISAM

MyISAM Table Lock

MySQL fournit deux types de verrous pour les tables, à savoir :

• READ LOCK : permet aux utilisateurs de lire uniquement les données de la table.

• WRITE LOCK : Permet aux utilisateurs de lire et d'écrire sur la table.

L'opération de lecture de MyISAM sur la table ne bloquera pas les demandes de lecture des autres utilisateurs pour la même table, mais elle bloquera les demandes d'écriture sur la même table. Les opérations d'écriture de MyISAM sur la table bloqueront les demandes de lecture et d'écriture des autres utilisateurs. la même table. Les opérations d'écriture, les opérations de lecture et les opérations d'écriture de la table MyISAM, ainsi que les opérations d'écriture sont en série.

MyISAM ajoutera automatiquement des verrous en lecture à toutes les tables utilisées avant d'exécuter l'instruction de requête (SELECT). Avant d'exécuter les opérations de mise à jour (UPDATE, DELETE, INSERT, etc.), il ajoutera automatiquement des verrous en écriture aux tables impliquées. ne nécessite pas notre intervention manuelle, nous n'avons donc généralement pas besoin d'utiliser la commande LOCK TABLE pour verrouiller explicitement la table MyISAM, mais il n'y a aucun problème avec l'affichage du verrou.

De plus, lorsque vous utilisez LOCK TABLES pour ajouter explicitement des verrous de table à une table, vous devez obtenir tous les verrous sur les tables impliquées en même temps, car après avoir exécuté LOCK TABLES, vous ne pouvez accéder qu'aux tables explicitement verrouillées, pas à celles déverrouillées. table, sinon une erreur se produira en même temps, si un verrou de lecture est ajouté, seules les opérations de requête peuvent être effectuées et les opérations de mise à jour ne peuvent pas être effectuées. Sinon, une erreur sera signalée. verrouillage automatique. C'est pourquoi la table MyISAM ne sera pas la raison du blocage.

Regardez un exemple ci-dessous.

1. Créer une table

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2. La session 1 acquiert le verrou en écriture

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3.

Nous savons que lorsqu'une session détient un verrou WRITE, toutes les autres sessions ne peuvent pas accéder aux données de la table, donc lorsque la deuxième session exécute l'instruction suivante, elle sera toujours en état d'attente.

mysql> select * from test_table;

4. Session 1 déverrouillée

unlock table;

Inserts simultanés

Les opérations de lecture et d'écriture dans MyISAM sont en série, mais selon le paramètre concurrent_insert, MyISAM peut prendre en charge les requêtes et les insertions parallèles.

concurrent_insert a les valeurs suivantes :

• 0 : la fonction d'insertion simultanée n'est pas autorisée.

• 1 : Autoriser les insertions simultanées pour les tables sans trous, avec de nouvelles données à la fin du fichier de données (par défaut).

• 2 : Que le tableau comporte ou non des trous, l'insertion simultanée à la fin du fichier de données est autorisée.

Les trous font référence aux lignes du milieu du tableau qui n'ont pas été supprimées.

InnoDB

InnoDB est différent de MyISAM. Il a deux caractéristiques : l'une est qu'il prend en charge les transactions et l'autre est qu'il utilise des verrous au niveau de la ligne. serrures.

Caractéristiques de la transaction

• Atomicité

  Une transaction est une unité d'opération atomique. Toutes les modifications apportées aux données sont soit exécutées, soit pas exécutées du tout.

• Cohérence

  Les données doivent rester cohérentes lorsqu'une transaction est démarrée et terminée. Cela signifie que toutes les règles de données pertinentes doivent être appliquées aux modifications des transactions afin de maintenir l'intégrité des données.

• Isolement

  Le système de base de données garantit que les transactions ne sont pas affectées par des opérations simultanées externes et peuvent être exécutées dans un environnement « indépendant », ce qui signifie que l'état intermédiaire pendant le traitement des transactions est invisible de l'extérieur.

• Persistance

  Une fois la transaction terminée, sa modification des données est permanente et peut être maintenue même en cas de panne du système.

Problèmes causés par le traitement des transactions simultanées

Par rapport au traitement en série, bien que l'utilisation des ressources soit améliorée et qu'un plus grand nombre d'utilisateurs puissent être pris en charge, le traitement des transactions simultanées entraînera également certains problèmes, notamment les situations suivantes.

Mise à jour perdue

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

隔离级别	脏读	不可重复性	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读取	×	×	√
可序列化（serializable）	×	×	×

查询/更改隔离级别

显示隔离级别
show global variables like '%isolation%';
select @@transaction_isolation;

设置隔离级别
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额80
T5	commit	commit

可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。

READ COMMITTED （读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额100
T5	commit
T5		select * from user where user_name="张三"; 此时张三余额80

可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。

REPEATABLE READ （可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;

时间	事务1	事务2
T1	begin;	begin;
T2	update user set balance =80 where user_name ="张三";
T3	commit;
T4		select * from user where user_name="张三"; 张三余额为100

可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="张三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子

设置隔离级别为序列化
set session transaction isolation level serializable;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三";
T3		update user set balance =80 where user_name ="张三";

这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

• 共享锁允许持有锁的事务读取行。

• 独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

• 意向共享锁 事务想要获得一张表中某几行的共享锁。
• 意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

• 共享锁：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE
• 排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。

select.* from emp where user_id > 100 for update;

这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

更多编程相关知识，请访问：编程视频！！

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!