MySQL Explication détaillée du mécanisme d'isolation des transactions et des principes de mise en œuvre

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement l'utilisation des transactions MySQL et les risques des transactions longues, les transactions MySQL et leurs caractéristiques, les problèmes causés par les transactions simultanées et l'isolation des transactions. Niveaux et démonstrations, verrous de contrôle de version unique et le contrôle de concurrence multi-versions MVCC, etc. Examinons-les ensemble, j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

1. Utilisation des transactions MySQL

1. Qu'est-ce qu'une transaction

Une transaction est une série d'opérations effectuées comme une seule unité logique de travail. Soit toutes, soit aucune de ces opérations constituent une unité de travail intégrale.

Par exemple, lors du paiement de quelque chose, il y aura une série d'opérations, telles que vérifier le solde, ajouter ou soustraire et mettre à jour le solde. Ces opérations doivent être simultanées. Sinon, cela montrera que votre paiement a réussi, mais que le système n'a pas reçu l'argent. Dans MySQL, la prise en charge des transactions est implémentée au niveau du moteur. Le moteur MyISAM ne prend pas en charge les transactions. 2. Les quatre caractéristiques des transactions. une transaction, Une unité logique de travail doit satisfaire quatre caractéristiques dans un système de gestion de base de données relationnelle.

Le soi-disant ACIDE : atomicité, consistance, isolation et durabilité.

Atomicité : Toutes les opérations d'une transaction sont soit terminées, soit incomplètes et ne se termineront pas dans les étapes intermédiaires.

Cohérence

 : Les limites d'intégrité de la base de données ne sont pas dépassées avant et après une transaction.

Isolation : les relations apparaîtront lorsque plusieurs transactions accèdent aux mêmes données dans la base de données en même temps.

Persistance : Une fois la transaction terminée, les modifications apportées par la transaction seront conservées et ne seront pas perdues.

ACID doit être garanti via les journaux Redo et Undo. Explication détaillée du système de journalisation MySQL : (à compléter ultérieurement)

3. Utilisation des transactions MySQL

MySQL a les deux méthodes de démarrage de transaction suivantes :

Démarrer explicitement les instructions de transaction

commencer ou démarrer la transaction. L'instruction de validation correspondante est commit et l'instruction rollback est rollback.

BEGIN -- 开启事务
START TRANSACTION -- 开启事务

INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );

COMMIT -- 提交事务

ROLLBACK -- 回滚事务

3.2. Désactivez la soumission automatique

set autocommit=0, cette commande désactivera la soumission automatique de ce fil. Cela signifie que si vous exécutez uniquement une instruction select, la transaction sera démarrée et ne sera pas automatiquement validée. Cette transaction persiste jusqu'à ce que vous exécutiez activement une instruction de validation ou d'annulation, ou jusqu'à ce que vous vous déconnectiez. (Non recommandé !)

set autocommit=0 -- 关闭自动提交

INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );

COMMIT -- 提交事务

ROLLBACK -- 回滚事务

2. Isolation et mécanisme d'isolation des transactions MySQL

Lorsque plusieurs transactions sont exécutées sur la base de données en même temps (peut être compris comme des scénarios multitâches et simultanés), des lectures sales et des transactions indisponibles Des lectures répétées et des lectures fantômes peuvent survenir.

1. Quatre niveaux d'isolement

Afin de résoudre ces problèmes, MySQL introduit le concept de « niveau d'isolement ».

Plus le niveau d'isolement est élevé, plus l'efficacité est faible. Plusieurs fois, nous devons trouver un équilibre entre les deux. Les niveaux d'isolement des transactions standard SQL incluent : lecture (lecture) non validée (lecture non validée), lecture (lecture) validée (lecture validée), lecture répétable (lecture répétable) et sérialisable (sérialisable).

Read Uncommit (

RU

, Read Uncommit), qui peut lire le processus intermédiaire de la transaction. Il viole les propriétés ACID et présente des problèmes de lecture incorrects. Par conséquent, il n’est fondamentalement pas utilisé et peut être ignoré.

Read Comended

(RC, Read Comended), ce qui signifie que si d'autres transactions ont été validées, on voit que c'est aussi le niveau le plus couramment utilisé. Cependant, pour des raisons historiques, RC peut ne pas être beaucoup utilisé dans les environnements de production.

Repeatable Read

(RR, Repeatable Read) est actuellement le niveau le plus utilisé. Il propose un verrouillage des espaces, qui est toujours le niveau par défaut. À ce niveau, des blocages, une faible concurrence et d’autres problèmes surviennent souvent.

Seriallessly

(sérialisable) n'est pas une implémentation multi-version, mais une implémentation à version unique, car toutes ses implémentations sont implémentées via des verrous. Fondamentalement non utilisé et peut être ignoré. 2. Problèmes causés par des transactions simultanées

Lecture sale : La transaction A lit les données mises à jour par la transaction B, puis B annule, donc les données lues par A sont sales (données qui ne devraient pas réellement exister)

Lecture non répétable : la transaction A lit les mêmes données plusieurs fois. La transaction B met à jour et valide les données lors de plusieurs lectures de la transaction A, ce qui entraîne des résultats incohérents lorsque la transaction A capture les mêmes données plusieurs fois.

Une transaction lit les données soumises par une autre transaction, ce qui entraîne la lecture de données différentes deux fois

幻读：A查出来数据，此时B提交，A再次查同一数据时结果不一致。一个事务前后两次读取的数据不一致，是因为其他事务插入数据导致的事务并发情况

不可重复读和幻读很容易混淆，不可重复读侧重于修改，而幻读侧重于添加或删除。要解决不可重复读取的问题，只需要锁，符合条件的行，而要解决幻读问题需要锁表。

3、隔离级别问题剖析与演示

3.1 查看mysql事务隔离级别

SELECT @@transaction_isolation; -- 查看mysql事务隔离级别
SELECT @@tx_isolation;          -- 查看mysql事务隔离级别

3.2、脏读问题

将事务隔离级别修改为读未提交，可以看到，事务还没有提交，这时候去查询这条数据，发现数据已经可见了。

set session transaction isolation level read uncommitted; -- 设置成读未提交
SELECT @@tx_isolation;          -- 查看mysql事务隔离级别

START TRANSACTION -- 事务A
INSERT INTO fork_business_detail VALUES ( 4, '123', '123', '123004', '2022-11-12 17:17:29', '1', '2022-11-12 17:17:37', '1' );
ROLLBACK

select * from fork_business_detail where id= 4 -- 事务B

3.3、不可重复读

一个事务读取到其他事务已提交的数据导致前后两次读取数据不一样的情况。

select * from fork_business_detail where id= 4;

BEGIN; -- 开启事务
select * from fork_business_detail where id= 4;
UPDATE fork_business_detail set SUB_ODR_ID=123004 where id= 4;
COMMIT;

select * from fork_business_detail where id= 5;

三、MySQL事务实现原理

1、单版本控制——锁

serializable ，使用锁独占方式来确保只有一个版本时事务被隔离，因此锁可以理解为单版本控制。

在MySQL事务中，锁的实现与隔离级别有关。在RR（Repeatable Read）隔离级别下，MySQL使用间隙锁来防止以并行性为代价写入数据，以解决虚拟读取的问题。

这种类型的锁通常会导致死锁，因为它没有足够的并行性和许多冲突。现在流行的Row模式可以避免许多冲突甚至死锁，因此建议默认使用Row+RC（Read Committed）模式隔离级别，这可以大大提高数据库的读写并行性。

2、多版本控制MVCC

多版本控制，也称为MVCC，是指数据的多版本处理，以实现数据库中的高度并发数据访问，以及事务的可见性，以确保事务可以看到其应该看到的数据版本。

如何生成多个版本？

每次修改数据库时，撤消（ Undo log）日志都会记录当前修改记录的事务号和修改前数据状态的存储地址（即ROLL_PTR），以便在必要时回滚旧数据版本。

例如，读取事务查询当前记录，但最近的事务尚未提交。根据原子性，读取事务无法看到最新的数据，但您可以在回滚段中找到旧版本数据，从而生成多个版本。

多版本控制巧妙地将独占和独占的稀有资源转换为并发，大大提高了数据库吞吐量和读/写性能。

推荐学习：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!