Quels sont les quatre niveaux de transaction de MySQL InnoDB et les lectures sales, les lectures non répétées et les lectures fantômes ?

1. Niveau d'isolement des transactions MySQL InnoDB, lecture sale, lecture répétable, lecture fantôme

Le niveau d'isolement des transactions MySQL InnoDB a quatre niveaux, la valeur par défaut est "REPEATABLE" READ).

· 1). Lecture non validée (READUNCOMMITTED). Une autre transaction a modifié les données mais ne les a pas encore soumises, et le SELECT de cette transaction lira les données non validées (lecture sale) ( Le niveau d'isolement le plus bas et les performances de concurrence élevées ) .

· 2). Soumettre la lecture (READCOMMITTED). Ce que lit cette transaction, ce sont les dernières données (après la validation des autres transactions). Le problème est que dans la même transaction, le même SELECT lira deux fois des résultats différents (sans lecture répétée). Il y aura des problèmes de lecture non répétable et de lecture fantôme (verrouillage de la ligne en cours de lecture)

· 3). Dans la même transaction, le résultat de SELECT est l'état au moment où la transaction démarre. Par conséquent, les résultats lus par la même opération SELECT seront cohérents. Cependant, il y aura une lecture fantôme (expliquée plus tard). Des lectures fantômes se produisent (toutes les lignes lues sont verrouillées).

· 4). Sérialisation (SÉRIALISABLE). Les opérations de lecture acquièrent implicitement des verrous partagés, ce qui garantit une exclusion mutuelle (table de verrouillage) entre différentes transactions.

'

Quatre niveaux d'intensité croissante, chacun résolvant un problème.

· 1) Lecture sale. Une autre transaction a modifié les données mais ne les a pas encore validées, et le SELECT de cette transaction lira les données non validées.

· 2). Pas de lecture répétée. Après avoir résolu la lecture sale, vous constaterez que lors de l'exécution de la même transaction, une autre transaction a soumis de nouvelles données, de sorte que les résultats des données lues deux fois par cette transaction seront incohérents.

· 3). Lecture fantôme. Il résout le problème de lecture non répétée et garantit que dans une même transaction, les résultats de la requête sont dans l'état (cohérence) au début de la transaction. Cependant, si une autre transaction soumet de nouvelles données en même temps, et que cette transaction mise à jour, vous serez "surpris" de découvrir ces nouvelles données. Il semble que les données que vous avez lues auparavant soient une illusion "fantôme". .

Plus précisément :

Lecture sale

Tout d'abord, faites la distinction entre les pages sales et les données sales

milieu. La lecture et la modification des pages dans le pool de tampons sont normales et peuvent améliorer l'efficacité. Flush peut être synchronisé. Des données sales signifient que la transaction a modifié l'enregistrement de ligne dans le pool de mémoire tampon, mais ne l'a pas encore soumis ! ! ! , si des données de ligne non validées dans le pool de mémoire tampon sont lues à ce moment-là, cela s'appelle une lecture sale, qui viole l'isolement des transactions. Une lecture sale signifie que lorsqu'une transaction accède aux données et les modifie, mais que la modification n'a pas encore été soumise à la base de données, une autre transaction accède également aux données et utilise ensuite les données.

2). Lecture non répétable

fait référence à la lecture des mêmes données plusieurs fois au cours d'une transaction. Avant la fin de cette transaction, une autre transaction accède également aux mêmes données. Ensuite, entre les deux lectures de données de la première transaction, la deuxième transaction a été validée du fait des modifications de la deuxième transaction. Ensuite, les données lues deux fois par la première transaction peuvent être différentes. De cette façon, les données lues deux fois au sein d’une transaction sont différentes, on parle donc de lecture non répétable. Par exemple, un éditeur lit deux fois le même document, mais entre les lectures, l'auteur réécrit le document. Lorsque l'éditeur lit le document une seconde fois, le document a changé. Les lectures brutes ne sont pas reproductibles. Ce problème peut être évité si les éditeurs ne peuvent lire le document qu'une fois que l'auteur a fini de l'écrire

3). Lecture fantôme :

Références à un phénomène qui se produit lorsque les transactions ne sont pas exécutées indépendamment. Par exemple, la première transaction modifie les données d'une table, et cette modification concerne toutes les lignes de données de la table. Parallèlement, la deuxième transaction modifie également les données de cette table. Cette modification insère une ligne de nouvelles données dans la table. Ensuite, à l’avenir, l’utilisateur qui effectue la première transaction constatera qu’il reste des lignes de données non modifiées dans la table, comme si une hallucination s’était produite. Par exemple, un éditeur modifie un document soumis par un auteur, mais lorsque la production fusionne ses modifications dans la copie principale du document, on découvre que l'auteur a ajouté du nouveau matériel non édité au document. Ce problème peut être évité si personne ne peut ajouter de nouveaux éléments au document tant que les éditeurs et le service de production n'ont pas fini de travailler sur le document original.

2. Expérience de niveau d'isolement

L'expérience suivante est basée sur le blogueur MySQL Server 5.6

Créez d'abord un tableau, comme suit :

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1 : Expliquer les problèmes de lecture sale et de lecture répétable

.

sélectionnez * parmi t :1,2,3, 4

	Transaction A LECTURE-UNCOMMITTED	Transaction READB - ENGAGÉ,	Transaction C-1 RÉPÉTABLE-LECTURE	Transaction C-2 RÉPÉTABLE-LECTURE	Transaction D SERIALIZABLE
set autocommit =0;
démarrer la transaction ;				démarrer la transaction ;
insérer dans les valeurs t(a) (4);
	sélectionnez * parmi t; 1,2,3,4 (lecture sale : lire les données dans les transactions non validées)	sélectionnez * from t; 1,2,3 (résoudre les lectures sales)	sélectionnez * from t; 1, 2,3	sélectionnez * dans t; 1,2,3	sélectionnez * dans t; 1, 2,3
commit;	🎜>					sélectionnez * parmi t : 1,2,3,4	sélectionnez * parmi t : 1,2,3,4 (pas dans la même transaction que ci-dessus, donc la lecture est la dernière après la soumission de la transaction, donc 4 peut être lu)	sélectionnez * parmi t: 1,2,3 (Lecture répétée : Puisqu'il s'agit de la même transaction que ci-dessus, seules les données du début de la transaction sont lues, c'est-à-dire une lecture répétée)	sélectionnez * parmi t: 1,2,3,4
				commit (soumettez la transaction, ce qui suit est une nouvelle transaction, vous pouvez donc lire les dernières données après la soumission de la transaction)
				sélectionnez * parmi t : 1,2,3,4
READ-UNCOMMITTED générera des lectures sales et est rarement applicable aux scénarios réels, donc il n'est fondamentalement pas utilisé.

2.2、实验二：测试READ-COMMITTED与REPEATABLE-READ

事务A	事务B READ-COMMITTED	事务C REPEATABLE-READ
set autocommit =0;
start transaction ;	start transaction;	start transaction;
insert into t(a)values(4);
	select * from t; 1,2,3	select * from t; 1,2,3
commit;
	select * from t: 1,2,3,4	select * from t: 1,2,3（重复读：由于与上面的在一个事务中，所以只读到事务开始事务的数据，也就是重复读）
		commit（提交事务，下面的就是一个新的事务，所以可以读到事务提交以后的最新数据）
		select * from t: 1,2,3,4
REPEATABLE-READ可以确保一个事务中读取的数据是可重复的，也就是相同的读取（第一次读取以后，即使其他事务已经提交新的数据，同一个事务中再次select也并不会被读取）。 READ-COMMITTED只是确保读取最新事务已经提交的数据。

事务B LIRE -COMMITTED

事务C REPEATABLE- LIRE

set autocommit =0;

   

démarrer la transaction ;

démarrer la transaction;

démarrer la transaction;

insérer dans t(a)values(4);

     

select * from t;

1,2,3

select * from t;

1,2,3

    td>        

commit;

     

select * from t:

1,2,3,4

select * from t:

1,2,3（重复读：由于与上面的在一个事务中，所以只读到事务开始事务的数据，也就是重复读）

   

commit（提交事务，下面的就是一个新的事务，所以可以读到事务提交以后的最新数据)

  

select * from t:

1,2,3,4

REPEATABLE-READ后，即使其他事务已经提交新的数据，同一个事务中再次select也并不会被读取）。

READ-COMMITTED只是确保读取最新事务已经提交的数据。

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。如下，

1. start transaction;  
   insert into t(a)values(4);  
   select *from t;    
   1,2,3,4;  
   insert into t(a)values(5);  
   select *from t;  
   1,2,3,4,5;

2.3、实验三：测试SERIALIZABLE事务对其他的影响

事务A SERIALIZABLE	事务B READ-UNCOMMITTED	事务C READ-COMMITTED,	事务D REPEATABLE-READ	事务E SERIALIZABLE
set autocommit =0;
start transaction ;			start transaction;
select a from t union all select sleep(1000) from dual;
	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。
SERIALIZABLE est un mode d'exécution de sérialisation très strict qu'il s'agisse de lecture ou d'écriture, cela affectera les autres transactions qui lisent la même table. Il s’agit d’un verrou exclusif strict en lecture-écriture au niveau de la table. Il perd également les avantages du moteur innodb. Les applications pratiques sont peu nombreuses.

2.4. Expérience 4 : Lecture fantôme

Certains articles écrivent que la lecture répétable d'InnoDB évite la « lecture fantôme » (lecture fantôme). Faites une expérience : (Toutes les expériences suivantes doivent prêter attention au moteur de stockage et au niveau d'isolation)

1. CREATE TABLE `t_bitfly` (

2. `id` bigint( 20) NON NULL par défaut '0' ,

3. `value` varchar(32) par défaut NULL,

4. PRIMAIRE CLÉ (`id`)

5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

7. sélectionnez @@global.tx_isolation, @@tx_isolation;  

8. +-----------------------+- ----------------+  

9. | @@global.tx_isolation | @@tx_isolation  |  

10. +-----------------------+- ----------------+  

11. | RÉPÉTABLE-LIRE       | RÉPÉTABLE-LIRE |  

12. +-----------------------+- ----------------+  

实验4-1：

SéanceA	Séance B
démarrer la transaction ;	démarrer la transaction ;
SELECT * FROM t_bitfly; ensemble vide
	INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;
SELECT * FROM t_bitfly; | ensemble vide
INSERT INTO t_bitfly VALUES (1, 'a'); |ERREUR 1062 (23000): |Entrée en double '1' pour la clé 1 ( Vous venez de me dire clairement qu'un tel enregistrement n'existe pas)	Je

De cette façon, une lecture fantôme se produit, pensant qu'il n'y a pas données dans le tableau, mais en fait les données existaient déjà. Après la soumission, j'ai constaté que les données étaient en conflit.

Expérience 4-2 :

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b');
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
UPDATE t_bitfly SET value='z'; | Rows matched: 2  Changed:2  Warnings: 0 (怎么多出来一行)
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |z     | | |    2 |z     | | +------+-------+

Session A

Session B

démarrer la transaction ;

démarrer la transaction ;

SELECT * FROM t_bitfly;| +------+-------+| | id | valeur || + ------+-------+| |1 || +------+-------+

INSÉRER DANS LES VALEURS t_bitfly (2, 'b');

SELECT * FROM t_bitfly;| +------+-------+| | identifiant | valeur || +------+-------+| --+

COMMIT;

SELECT * FROM t_bitfly;| +------+-------+| | id | valeur || +--- ---+-------+| |1 || +------+-------+

MISE À JOUR t_bitfly SET value='z';| Lignes correspondantes : 2 Modifiées : 2 Avertissements : 0

(Comment obtenir une ligne supplémentaire)

SELECT * FROM t_bitfly;| +------+-------+| | id | valeur || +------+-------+| 1 |z ||2 || --- ---+

Une ligne est lue pour la première fois dans cette transaction. Après une mise à jour, les données soumises dans une autre transaction apparaissent. On peut aussi y voir une sorte de lecture fantôme.

Avec explication

Alors, quelle est la raison pour laquelle InnoDB a souligné que les lectures fantômes peuvent être évitées ?

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

Par défaut, InnoDB fonctionne au niveau d'isolement des transactions REPEATABLE READ et avec la variable système innodb_locks_unsafe_for_binlog désactivée. Dans ce cas, InnoDB utilise les verrous de clé suivante pour les recherches et les analyses d'index, ce qui empêche les lignes fantômes (voir Section 13.6.8.5, "Éviter le problème fantôme en utilisant le verrouillage à clé suivante").

La compréhension préparée est que lorsque le niveau d'isolement est répétable, en lecture et que innodb_locks_unsafe_for_binlog est désactivé, recherchez et analysez l'index dans Next-keylocks. peut être utilisé pour éviter les lectures fantômes.

Le point clé est le suivant : InnoDB ajoute-t-il également des verrous de clé suivante à une requête normale par défaut, ou l'application doit-elle ajouter les verrous elle-même ? Si vous venez de lire cette phrase, vous penserez peut-être qu'InnoDB ajoute également des verrous aux requêtes ordinaires. Si oui, quelle est la différence entre cela et la sérialisation (SERIALIZABLE) ?

Il y a un autre paragraphe dans le manuel MySQL :

13.2.8.5 Éviter le problème fantôme en utilisant le verrouillage par clé suivante (http://dev.mysql.com/doc/refman/5.0/. fr/ innodb-next-key-locking.html)

Pour prévenir les fantômes, InnoDB utilise un algorithme appelé next-key verrouillage qui combine le verrouillage des lignes d'index avec le verrouillage des espaces.

Vous pouvez utiliser le verrouillage par clé suivante pour implémenter une vérification d'unicité dans votre application : Si vous lisez vos données en mode partage et ne voyez pas de doublon pour une ligne que vous allez insérer, vous pouvez alors insérer votre ligne en toute sécurité et savoir que le verrou à clé suivante est défini sur le Le succès de votre ligne lors de la lecture empêche quiconque d'insérer un doublon pour votre ligne. Ainsi, le verrouillage de la touche suivante vous permet de « verrouiller » la non-existence de quelque chose dans votre table.

Ma compréhension est Say. , InnoDB fournit des verrous sur la clé suivante, mais l'application doit le verrouiller elle-même. Un exemple est fourni dans le manuel :

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

可以使用show engine innodb status来查看是否给表加上了锁。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验4-3：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly  WHERE id<=1  FOR UPDATE; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly   VALUES (2, 'b'); | Query OK, 1 row affected
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (0, '0'); |  (waiting for lock ... |   then timeout) ERROR 1205 (HY000):Lock wait timeout exceeded; |try restarting transaction
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | identifiant   | valeur | | +------+-------+ | |    1 |a     | | +------+-------+

Vous pouvez voir que le verrou ajouté avec id<=1 verrouille uniquement la plage d'id<=1. L'enregistrement avec l'identifiant 2 peut être ajouté avec succès Lors de l'ajout de l'enregistrement avec l'identifiant 0, il attendra que le verrou soit ajouté. être libéré.

Note ci-jointe :

Explication détaillée des verrous dans les lectures répétables dans le manuel MySQL :

http://dev.mysql.com/doc/refman/5.0 /en /set-transaction.html#isolevel_repeatable-read

Pour verrouiller les lectures (SELECT avec FOR UPDATE ou LOCK IN SHARE MODE),UPDATE, et DELETE, le verrouillage dépend du fait que l'instruction utilise ou non un index unique avec une condition de recherche unique, ou une condition de recherche de type plage Pour un index unique avec une condition de recherche unique, InnoDB verrouille. seul l'enregistrement d'index trouvé, pas l'espace qui le précède. Pour d'autres conditions de recherche, InnoDB se verrouille. la plage d'index analysée, à l'aide de verrous d'espacement ou de verrous de clé suivante (espacement plus enregistrement d'index) pour bloquer les insertions par d'autres sessions dans les espaces couverts par la plage.

Lecture de cohérence et lecture de validation, voir d'abord l'expérience ,

Expérience 4-4 :

Session A	Session B
démarrer la transaction ;	démarrer la transaction ;
SELECT * FROM t_bitfly; | +------+-------+ | | identifiant   | valeur | | +------+-------+ | |    1 |a     | | +------+-------+
	INSÉRER DANS LES VALEURS t_bitfly   (2, 'b');
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | identifiant   | valeur | | +------+-------+ | |    1 |a     | | +------+-------+
SELECT * FROM t_bitfly VERROUILLER EN MODE PARTAGE ; | +---------+-------+ | | identifiant | valeur | | +---------+-------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SÉLECTIONNER * FROM t_bitfly POUR LA MISE À JOUR ; | +---------+-------+ | | identifiant | valeur | | +---------+-------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SELECT * FROM t_bitfly; | +---------+-------+ | | identifiant | valeur | | +---------+-------+ | |  1 |a     | | +---------+-------+

Pièce jointe : si vous utilisez une lecture ordinaire, vous obtiendrez des résultats cohérents. Si vous utilisez une lecture verrouillée, vous lirez le résultat de lecture "dernier" "validé".

lui-même, lecture répétable et lecture engagée sont contradictoires. Dans la même transaction, si la lecture répétable est garantie, les validations des autres transactions ne seront pas vues, ce qui viole la lecture validée ; si la lecture validée est garantie, les résultats des deux lectures précédentes seront incohérents, ce qui viole la lecture répétable.

On peut dire qu'InnoDB fournit un tel mécanisme dans le niveau d'isolement de lecture répétable par défaut, vous pouvez utiliser la lecture verrouillée pour interroger les dernières données.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Si vous souhaitez voir l'état « le plus récent » de la base de données , vous devez utiliser soit le niveau d'isolement READ COMMITTED, soit une lecture verrouillable :
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

3. Résumé

Conclusion : le niveau d'isolement par défaut des transactions MySQL InnoDB est une lecture répétable, ce qui ne garantit pas l'évitement des lectures fantômes. L'application doit utiliser des lectures verrouillées pour garantir cela. Le mécanisme utilisé pour ce degré de verrouillage est celui des serrures à clé suivante.

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!